Missed Games

[chris.mo]

Hello!
I'm trying to show the repective total numbers of days and games a football player has missed due to a certain injury. I have an injuries table showing the date of injury and the date of recovery, so the "Days Missed" column was just recovery date minus injury date and the Days Missed column seems to work ok. My problem is with the "Games Missed" column in my query. I have a "Game Details" table, listing the date of each game, but how do I get my query to show a count of the number of games between the injury dates?? This is doing my head in, so any help would be much appreciated.

Chris,
Liverpool, England.

 

[raskew]

If you had:

(1) tblInjured, with three fields:
Player
DateOut
DateIn

(2) tblGames, with two fields:
Opponent
DtePlayed

This query would show the games the players didn't play in because of injury. Note that both tables appear in the query, but without being related.

SELECT tblInjured.Player, tblGames.Opponent, tblGames.dtePlayed
FROM tblInjured, tblGames
WHERE (((tblGames.dtePlayed)>=[DateOut] And (tblGames.dtePlayed)<=[DateIn]));

To return the number of games each player missed, you'd manipulate the above into a totals query, e.g.:

SELECT tblInjured.Player, Count(tblGames.dtePlayed) AS CountOfdtePlayed
FROM tblInjured, tblGames
WHERE (((tblGames.dtePlayed)>=[DateOut] And (tblGames.dtePlayed)<=[DateIn]))
GROUP BY tblInjured.Player;