How can I create a report that shows datavalues that are not used?

[IceKing]

Hi,

First off, let me appologies for the description of this thread/question. I'm sure it does not describ my question properly.

Here is the background to my question:
I schedule ice for a minor hockey associaition. I decided to use Access this year, instead of Excel for it's reporting capabilities. I have created a simple report that shows, by day, all the ice that I have, and what teams are on it.

Now I'd like to create a report that shows, by day, what teams are not on the ice, or can not use the ice, that day.

The next step would be to merge these 2 reports. But I'll cross that bridge another day.

Any help would be appreciated.

Thanks!

 

[IceKing]

Figured it out!

The null value returns from query below was the culprit.

SELECT ScheduleDate, HomeId as TeamId, "H" as HV
FROM MasterIceSchedule
UNION ALL SELECT ScheduleDate, VisitorId, "V"
FROM MasterIceSchedule;

Can't explain why, but when I modified the query to:

SELECT ScheduleDate, HomeId as TeamId, "H" as HV
FROM [Master Ice Schedule]
WHERE HomeId > 0
UNION ALL SELECT ScheduleDate, VisitorId, "V"
FROM [Master Ice Schedule]
WHERE VisitorId > 0;

The Not IN query returns the proper results.

Thanks for your time and effort Duane.