Query returns too much data 2

[riddler83]

I have a query which is used to set up a report showing absences here for employees. The query is basically set up to show the following data: tstaff.Empname, tdept.dept, and various fields from t.Absence, such as date etc...

The problem I have is this. When the user enters in the name of the person they want to run a query, eg Joe Bloggs, and runs the query. The query returns all absences for Joe Bloggs & all absences for everyone else in his department. So basically, we have a report showing all of the Sales absences under Joe Bloggs, even though they are correctly entered in tAbsence.

Has anyone got any ideas? We really need to get this working asap, as we have a few reports that need running.

Thanks in advance
Dave

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

 

[lespaul]

what's the SQL of the query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[riddler83]

The SQL for the query is below:
SELECT tStaff.Name, tDept.Dept, tAbsence.[First Day], tAbsence.[Last Day], tAbsence.Days, tAbsence.[lnc type], tAbsence.Illness, tAbsence.[LAMP Event], tAbsence.[Special Leave]
FROM (tDept INNER JOIN tAbsence ON tDept.DeptID = tAbsence.Dept) INNER JOIN tStaff ON tDept.DeptID = tStaff.Department
WHERE (((tStaff.Name)=[Forms]![fEmpAbs]![cboemp]) AND ((tAbsence.[First Day])>=[Start Date:]) AND ((tAbsence.[Last Day])<=[End Date:]));

I really could use any help at all on this one. Im not around access all that often so its not something that just jumps out at me

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

 

[Melagan]

First of all, I would definitely encourage you to upgrade your tEmpAbs form to include the start date and end date parameters instead of prompting them strictly through code. You should be getting those values from your form just like you're satisfying your tstaff.name criteria using "=Forms![fEmpAbs]![cboemp]".

Anyway, as for your SQL, I think this is beyond my ability to solve without seeing some more details on your table structures.

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Please note that this is more of a shot in the dark from me, but it's worth a try - all I've done is rearrange your joins.

SELECT s.Name
, d.Dept
, a.[First Day]
, a.[Last Day]
, a.Days
, a.[lnc type]
, a.Illness
, a.[LAMP Event]
, a.[Special Leave]

FROM (tStaff AS s 
INNER JOIN tDept AS d ON s.Department = d.DeptID) 
INNER JOIN tAbsence AS a ON s.Department = a.Dept

WHERE (((s.Name)=[Forms]![fEmpAbs]![cboemp]) 
AND ((a.[First Day])>=[Start Date:]) AND ((a.[Last Day])<=[End Date:]));

~Melagan
______
"It's never too late to become what you might have been.

 

[riddler83]

Thanks for taking a look Melagen. Unfortunately the above code did not make a difference.

As for your comment on seeing table structures, What type of info would you need here? As far as the fields affected in the query, they are setup as follows, if this is not what you meant, let me know.

tStaff.Name Text
tDept.Dept Text
tAbsence.[First Day] Date/Time
tAbsence.[Last Day] Date/Time
tAbsence.Days Number
tAbsence.[lnc type] Lookup Field
tAbsence.Illness Lookup Field
tAbsence.[LAMP Event] Lookup Field
tAbsence.[Special Leave] Lookup Field

The lookups really shouldnt come into play I think, the problem seems to be in the Name & Dept fields, because as I say, all the absences for a dept show up for each member of that dept. Ive tried a lot of variations on code but cant seem to fix it. If you need any more info let me know. Thanks again

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

 

[randy700]

How about something like...

SELECT tStaff.Name, tDept.Dept, tAbsence.[First Day], tAbsence.[Last Day], tAbsence.Days, tAbsence.[Inc type], tAbsence.Illness, tAbsence.[LAMP Event], tAbsence.[Special Leave]

FROM (tStaff INNER JOIN tAbsence ON tStaff.NameID = tAbsence.NameID) INNER JOIN tDept ON tAbsence.Dept = tDept.DeptID

WHERE tStaff.Name = Forms!fEmpAbs!cboemp
AND tAbsence.[First Day] >= [Start Date: ]
AND tAbsence.[Last Day] <= [End Date: }


Randy

 

[lespaul]

Randy you're absolutely right! Riddler, if you don't understand why, check out Understanding SQL Joins

you've joined your absence table into the staff table based on the department ID!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[riddler83]

Thanks a million Randy, That really helped me out. Worked a treat. Thanks again

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