Query problem

[riddler83]

I have a query running on an absence db here to show all users with more than 5 absences in a given time period. A very basic query is all that is required, and it seemed to be working fine until we noticed that one particular individual, who we know to have 11 absences in the time period entered, was not on the report. Below is the SQL for the query. Can anyone shed any light as to why it is not picking up this one individual?

SELECT tName.Name, Count(tStaff.Name) AS CountOfName
FROM tName INNER JOIN ((tDept INNER JOIN tAbsence ON tDept.DeptID = tAbsence.Dept) INNER JOIN tStaff ON tDept.DeptID = tStaff.Department) ON (tName.NameID = tAbsence.Name) AND (tName.NameID = tStaff.Name)
WHERE (((tAbsence.[First Day])>=[Start Date:]) AND ((tAbsence.[Last Day])<[End Date:]))
GROUP BY tName.Name
HAVING (((Count(tStaff.Name))>4));


The man in black fled across the desert, and the Gunslinger followed

 

[PHV]

Perhaps this criteria instead ?
WHERE tAbsence.[First Day]<[End Date:] AND tAbsence.[Last Day]>=[Start Date:]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[riddler83]

Unfortunately, no. This is still returning the same data as before, and leaving out the user that we know should be there

The man in black fled across the desert, and the Gunslinger followed

 

[PHV]

You really should have tAbsence.Name = tName.NameID = tStaff.Name ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[riddler83]

I get an error when i try this "Join type not supported"

The man in black fled across the desert, and the Gunslinger followed

 

[PHV]

I get an error when i try this "Join type not supported"
My last post was a question, not a suggestion !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[riddler83]

Sorry, just at my wits end with this now at this stage. Cant see any reason why this works for 40 people, and not for 1.

The man in black fled across the desert, and the Gunslinger followed