Need to see scheduled dates and days and unscheduled in same query 1

[netrusher]

I am a College tennis coach. I am in need of a query that will show me all of my scheduled matches for this Spring. I can do that with my "Team Schedule Table". What I really need is to be able to show all of may scheduled matches and then the dates that I do not have a match scheduled between Jan 1 and May 31. I cannot figure out how to do this. I also need to see the day as well as the date. Thanks for any help. I hope if have worded this correctly.

 

[PHV]

I'd use a dates table with a left join to the schedule table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

Below is the SQL code of a query I am trying. Is this what you mean? This only gives me the date of matches scheduled. It is not showing me dates i do not have a match scheduled.


SELECT ScheduleDate.Date, ScheduleDate.Day, TeamScheduleTbl.Date, TeamScheduleTbl.Opponent, TeamScheduleTbl.Time
FROM ScheduleDate RIGHT JOIN TeamScheduleTbl ON ScheduleDate.Date = TeamScheduleTbl.Date
GROUP BY ScheduleDate.Date, ScheduleDate.Day, TeamScheduleTbl.Date, TeamScheduleTbl.Opponent, TeamScheduleTbl.Time
HAVING (((TeamScheduleTbl.Date) Like "*2012"));

 

[netrusher]

When I try it this way I am still getting the same thing.

SELECT ScheduleDate.Date, ScheduleDate.Day, TeamScheduleTbl.Date, TeamScheduleTbl.Opponent, TeamScheduleTbl.Time
FROM ScheduleDate LEFT JOIN TeamScheduleTbl ON ScheduleDate.Date = TeamScheduleTbl.Date
WHERE (((TeamScheduleTbl.Date) Like "*2012"));

 

[JonFer]

If you put a criteria with a Left Join, it should be on a field from the "Left" table (ScheduleDate here). It appears your dates are entered as text so...

SELECT ScheduleDate.Date, ScheduleDate.Day, TeamScheduleTbl.Opponent, TeamScheduleTbl.Time
FROM ScheduleDate LEFT JOIN TeamScheduleTbl ON ScheduleDate.Date = TeamScheduleTbl.Date
WHERE ScheduleDate.Date Like "*2012" ;

Fields from the "Right" table of a Left Join are NULL when there's no matching record so the only criteria that will preserve these rows is "<Field> Is Null