Sql connections

[Guest]

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

 

[Guest]

I had a problem with [Day] as a field name. It is a reserved word as is
Time. I used Day11. I did not use the Dummy and used Name for piviot
instead of PersID.

TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Booking.Day11, Time.Time
FROM (Personell INNER JOIN Booking ON Personell.PersID = Booking.PersID)
INNER JOIN [Time] ON Booking.TimeID = Time.TimeID
WHERE (((Booking.Day11) Is Null Or (Booking.Day11)=#10/19/2006#))
GROUP BY Booking.Day11, Time.Time
PIVOT Personell.Name;

 

[Guest]

Now, that seems good! The way you connect them it works fine. Also when I did
the same to the customer's names.

Then I tried to insert the dummy table again, but that did not work any
longer. I had it there before to get one row for every half hour of the
working day, also when there are no customers booked.

In the first version, with the customer and personell numbers instead of
their names, I got all those lines. But not any longer now, when all the
names are right. I have found that the query runs with INNER JOIN and LEFT
JOIN, although the empty rows are missing with these two examples:

FROM ((Personell INNER JOIN Booking ON Personell.PersID = Booking.PersID)
INNER JOIN [Time] ON Booking.TimeID = Time.TimeID) INNER JOIN dummy ON
Time.TimeID = dummy.dummyTime
and this:
FROM ((Personell INNER JOIN Booking ON Personell.PersID = Booking.PersID)
INNER JOIN [Time] ON Booking.TimeID = Time.TimeID) LEFT JOIN dummy ON
Time.TimeID = dummy.dummyTime

But it does not work with RIGHT JOIN
FROM ((Personell INNER JOIN Booking ON Personell.PersID = Booking.PersID)
INNER JOIN [Time] ON Booking.TimeID = Time.TimeID) RIGHT JOIN dummy ON
Time.TimeID = dummy.dummyTime
-then I get an error message, something like this, when I translate from
swedish to english:
"Cannot run the SQL-expression.There are ambigous outer joins. Decide which
join to be run first by running a special query and then insert it into the
SQL-expression."

I even tried to change direct in your example to:
FROM (Personell INNER JOIN Booking ON Personell.PersID = Booking.PersID)
RIGHT JOIN [Time] ON Booking.TimeID = Time.TimeID
- but got the same error.

Can you see how I could get all the hour's rows?
/ Rolf

I had a problem with [Day] as a field name. It is a reserved word as is
Time. I used Day11. I did not use the Dummy and used Name for piviot
instead of PersID.

TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Booking.Day11, Time.Time
FROM (Personell INNER JOIN Booking ON Personell.PersID = Booking.PersID)
INNER JOIN [Time] ON Booking.TimeID = Time.TimeID
WHERE (((Booking.Day11) Is Null Or (Booking.Day11)=#10/19/2006#))
GROUP BY Booking.Day11, Time.Time
PIVOT Personell.Name;

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

 

[Guest]

I was then experimenting a bit more from your example, and started from
scratch with the time table and time field, so that all of them were shown.
Then I added the other tables and fields and got everything to fall in place.

TRANSFORM First(Customer.Efternamn) AS FörstaförEfternamn
SELECT Time.Time11
FROM (([Time] LEFT JOIN Booking ON Time.TimeID = Booking.TimeID) LEFT JOIN
Customer ON Booking.CustomerID = Customer.KundID) LEFT JOIN Personell ON
Booking.PersID = Personell.PersID
WHERE (((Booking.Day11) Is Null Or (Booking.Day11)=#10/19/2006#))
GROUP BY Time.Time11
ORDER BY Personell.Name
PIVOT Personell.Name;

There showed up an empty column with the header "<>" but I could make it so
narrow, that it was unseen. So now I finally got it the way I intended.

Many thanks for your suggestion, that put me into the right direction.
/ Rolf

I had a problem with [Day] as a field name. It is a reserved word as is
Time. I used Day11. I did not use the Dummy and used Name for piviot
instead of PersID.

TRANSFORM First(Booking.Customer) AS FörstaförCustomer
SELECT Booking.Day11, Time.Time
FROM (Personell INNER JOIN Booking ON Personell.PersID = Booking.PersID)
INNER JOIN [Time] ON Booking.TimeID = Time.TimeID
WHERE (((Booking.Day11) Is Null Or (Booking.Day11)=#10/19/2006#))
GROUP BY Booking.Day11, Time.Time
PIVOT Personell.Name;

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