Missing records in cross tab query

[Rolf Rosenquist]

I have earlier got help here with a cross tab query. At that time it looked
like it worked. But now I have found that some or many lines (records) in the
answer are missing.

It is a schedule with times from 07:00 mornings to 21:30 evenings. Every
half hour should be specified. At best all but one shows up for 2006-10-19,
and 7 are missing at 2006-10-27. 11 are missing on all other days when no
booking is made. I have put a zip file on www dot net-help dot
info/crosstab/ and in this file there are only the query and the tables.

I cannot understand why all the time rows are not shown. Are the cross tab
queries really so unstable, or what have I been missing?
/ Rolf

 

[David F Cox]

If there is nothing to show, Access will not show it.
Set up a lookup table with a key of HH:MM for every time that you want
displayed, and do a Left join on the table of data.
A left join is all of the records from one table, and only the records from
the other where there is a match.
You can draw a line to link the join fields in the tables, then double click
on the line to get the options.
The Crosstab will then show those times even though there is no data for
them.

 

[Rolf Rosenquist]

That is exactly what I have done in the file I put on the internet address I
mentioned. Without that, I only got the records with the specified date, as
normal. Now even empty records come for a desired date, but the rows are
missing, when there is a record on another date.

That said, the come out when either empty or with a date match. But they are
suppressed when empty for the desired date and a record for any other date.
It now seems to me, that my date condition may be wrong. I wrote: Is Null OR
#10/19/2006# in the query.

How can I write so it ignores other dates?
/ Rolf

 

[Rolf Rosenquist]

Sorry, I meant my last question to be read as "How can I write so it ignores
if there also are other dates?"
/ Rolf

 

[David F Cox]

I found the sample file this time - I misread the address before.

I see only five records for the 27/10 and I see those exactly as I would
expect to in the crosstab, after I had included that date in the criteria.

I see thirteen records for the 19th and I find all of those in the crosstab
exactly where I would expect to find them.

Is the fact that you can get more than one booking record displayed on a
line in the crosstab confusing you?

 

[Rolf Rosenquist]

No, that is not the problem. If you run the query with the date 19th, there
come only 29 rows out, even if the time has 30 records.
And for the 27th there come only 23 out, not 30 here either.

The problem seems to me that the left join from tid table does not give all
the times.

I tried with a normal query:
SELECT Tid.Tid, Bokning.BoknDag, First(Bokning.Tränare) AS FörstaförTränare
FROM Tid LEFT JOIN Bokning ON Tid.TidID = Bokning.TidID
GROUP BY Tid.Tid, Bokning.BoknDag
HAVING (((Bokning.BoknDag) Is Null Or (Bokning.BoknDag)=#10/19/2006#))
ORDER BY Tid.Tid;

but here is also one missing. And that is for the time #4 and that is 08:30.
That is a time where there is a booking on the 27th.

If I say:
FROM Tid LEFT JOIN Bokning ON Tid.TidID = Bokning.TidID

in this normal query, I don't get all the 30 time records, only those with a
date. So I cannot get the left join to show every record in the tid table.

Can you see what I have done wrong?
/ Rolf

 

[David F Cox]

I see the problem now. I do not know if that is progress, where we had one
confused before,now we have two.

So far it looks to me that the times that are missing when the query is run
for the 27th are those that have matches on the 19th.

I have tried changing all of the things that I thought might be confusing
Access, but nothing changes this behaviour.

The quest continues. I will try re-building the crosstab step by step using
other queries. I want to produce a query of this type for an application I
have in mind myself.

 

[David F Cox]

I have it at last.

There is a crosstab field for 8 a.m. for the 19th. If we select on date =
27/10/2006 that field gets eliminated from the results. The *** thing is
doing what we tell it to.

I believe that part, or all of, the answer is to replace the table bokning
with a query that pre-selects the desired records. I have a working crosstab
query , but it has test mods that may or may not make a difference.

more to check out later.

 

[David F Cox]

This works for 27/10/2006

TRANSFORM First(([Förnamn] & " " & [efternamn])) AS nam
SELECT Tid.Tid, q_selectedbookings.[BoknDag]
FROM ((Tid LEFT JOIN q_selectedbookings ON Tid.TidID =
q_selectedbookings.TidID) LEFT JOIN Kunder ON q_selectedbookings.Kund =
Kunder.KundID) LEFT JOIN Tränare ON q_selectedbookings.id_Tränare =
Tränare.TränID
GROUP BY Tid.Tid, q_selectedbookings.[BoknDag]
PIVOT Tränare.TränareT;

 

[Rolf Rosenquist]

WOW!! It's fantastic! Exactly on the spot!

In the meantime I also made an extra query to select the date and got it
work, but only with numbers for Tränare och Kunder. Then I got the names for
Tränare as headings. But I got all the Kunder inside the view, even from the
other dates.

But your solution was the beautiful one and feel that I must salute you for
this. If we were close geographical, I also would buy you a beer to
celebrate. A heavy stone fell from my heart. Thanks!
/ Rolf

This works for 27/10/2006

TRANSFORM First(([Förnamn] & " " & [efternamn])) AS nam
SELECT Tid.Tid, q_selectedbookings.[BoknDag]
FROM ((Tid LEFT JOIN q_selectedbookings ON Tid.TidID =
q_selectedbookings.TidID) LEFT JOIN Kunder ON q_selectedbookings.Kund =
Kunder.KundID) LEFT JOIN Tränare ON q_selectedbookings.id_Tränare =
Tränare.TränID
GROUP BY Tid.Tid, q_selectedbookings.[BoknDag]
PIVOT Tränare.TränareT;

I see the problem now. I do not know if that is progress, where we had one
confused before,now we have two.

So far it looks to me that the times that are missing when the query is
run for the 27th are those that have matches on the 19th.

I have tried changing all of the things that I thought might be confusing
Access, but nothing changes this behaviour.

The quest continues. I will try re-building the crosstab step by step
using other queries. I want to produce a query of this type for an
application I have in mind myself.