R
Rolf Rosenquist
It seems that I cannot do everything right in the specification for a cross
tab query, where I have a schedule for customers and personell as well as
time of the day. I have come so far, that it can be presented alright, but
then the booking table must have the names of the customers and personell in
plain text. Of course I want it to store the index numbers to the other
tables instead. My sql is this:
TRANSFORM First(Booking.Customer) AS FirstforCustomer
SELECT Time.Time
FROM ([Time] LEFT JOIN Booking ON Time.TimeID = Booking.TimeID) RIGHT JOIN
dummy ON Time.TimeID = dummy.dummyTime
WHERE (((Booking.Day) Is Null Or (Booking.Day)=#10/19/2006#))
GROUP BY Time.Time, dummy.dummyTime
ORDER BY Time.Time
PIVOT Booking.Personell;
The Time table is used with a dummy table to get all the times of the
opening hours presented, even if there are no bookings, so that the result
always is equal long.
My problem here is that the headers for the personell will not display the
names, when I change to the pesonell's numbers in the Booking table. Also the
same for the customers. When I try to add the Personell table in the query,
Access gives me this:
TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Time.Time
FROM Personell INNER JOIN (([Time] LEFT JOIN Booking ON Time.TimeID =
Booking.TimeID) RIGHT JOIN dummy ON Time.TimeID = dummy.dummyTime) ON
Personell.PersID = Booking.PersID
WHERE (((Booking.Day) Is Null Or (Booking.Day)=#10/19/2006#))
GROUP BY Time.Time, dummy.dummyTime
ORDER BY Time.Time
PIVOT Booking.PersID;
But it also gives me an sql error when I want to look at the result. It says
that the connections are not properly defined.
I don't understand how to make the reference to the Personell table to get
the personell names in the headers, and would be very greatful for some help.
/ Rolf
tab query, where I have a schedule for customers and personell as well as
time of the day. I have come so far, that it can be presented alright, but
then the booking table must have the names of the customers and personell in
plain text. Of course I want it to store the index numbers to the other
tables instead. My sql is this:
TRANSFORM First(Booking.Customer) AS FirstforCustomer
SELECT Time.Time
FROM ([Time] LEFT JOIN Booking ON Time.TimeID = Booking.TimeID) RIGHT JOIN
dummy ON Time.TimeID = dummy.dummyTime
WHERE (((Booking.Day) Is Null Or (Booking.Day)=#10/19/2006#))
GROUP BY Time.Time, dummy.dummyTime
ORDER BY Time.Time
PIVOT Booking.Personell;
The Time table is used with a dummy table to get all the times of the
opening hours presented, even if there are no bookings, so that the result
always is equal long.
My problem here is that the headers for the personell will not display the
names, when I change to the pesonell's numbers in the Booking table. Also the
same for the customers. When I try to add the Personell table in the query,
Access gives me this:
TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Time.Time
FROM Personell INNER JOIN (([Time] LEFT JOIN Booking ON Time.TimeID =
Booking.TimeID) RIGHT JOIN dummy ON Time.TimeID = dummy.dummyTime) ON
Personell.PersID = Booking.PersID
WHERE (((Booking.Day) Is Null Or (Booking.Day)=#10/19/2006#))
GROUP BY Time.Time, dummy.dummyTime
ORDER BY Time.Time
PIVOT Booking.PersID;
But it also gives me an sql error when I want to look at the result. It says
that the connections are not properly defined.
I don't understand how to make the reference to the Personell table to get
the personell names in the headers, and would be very greatful for some help.
/ Rolf