Duplicate records in query

[NHCPA]

I have a table with payroll transactions and a table with dept activity (dates employees were transferred to another dept). My query is supposed to list the payroll transactions along with the dept the employee was in at the time.

DeptAct
Emp Dept TDate
101 201 01/01/05
101 202 02/01/05

Payroll
Emp Amt PDate
101 500 01/01/05
101 200 01/02/05
101 250 02/05/05

SELECT DeptAct.Dept, DeptAct.Emp, Max(DeptAct.TDate) AS MaxOfTDate, Payroll.Amt
FROM DeptAct INNER JOIN Payroll ON DeptAct.Emp = Payroll.Emp
WHERE (((DeptAct.TDate)<=[PDate]))
GROUP BY DeptAct.Dept, DeptAct.Emp, Payroll.Amt, Payroll.PDate;

Query1
Dept Emp MaxOfTDate Amt
201 101 01/01/05 200
201 101 01/01/05 250
201 101 01/01/05 500
202 101 02/01/05 250

As you can see it is duplicating the 250 transaction for both dept’s.

Any advice would be appreciated

Thank you

 

[KenReay]

Hi

One way

Make a Group By query based on DeptAct

SELECT DeptAct.Dept, DeptAct.Emp, Max(DeptAct.TDate) AS MaxOfTDate FROM DeptAct GROUP BY DeptAct.Dept..etc, save as say QueryA

this will return one row per Employee

Now join this query to your Payroll Table

SELECT QueryA.Dept, QueryA.Emp, QueryA.MaxOfTDate, Payroll.Amt
FROM QueryA INNER JOIN Payroll ON QueryA.Emp = Payroll.Emp
;


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK