Matching the correct info for a transaction 2

[NHCPA]

I have a table with payroll transactions and a table with dept activity (dates employees were transferred to another dept). I am trying to make a query that will add the correct dept for each payroll transaction

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

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


The query results, should look like this:

Query1
Dept Emp Amt
201 101 500
202 101 250


Any advice would be appreciated

Thank you

 

[Crowley16]

use the query builder in design view,

select the 2 tables,
join on the emp field,
include the dept + emp + amt fields to display...

--------------------
Procrastinate Now!

 

[NHCPA]

Doing that you would get each payroll transaction twice (one for each dept)

As follows:

Dept Emp Amt
202 101 500
201 101 500
202 101 250
201 101 250

 

[lespaul]

well if the dates in the two tables were the same you could also join on that, but in your example above the dates are different. Are there specific dates in the first table that correspond to the dates in the second table?


Leslie

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

 

[NHCPA]

Each employee would have a payroll transaction every week, however he will only have a dept change once in a couple months or years.

 

[lespaul]

Using the information from the example above, the only thing that I can think of that MIGHT work is something like:

SELECT Dept, P.Emp, Amt
FROM DeptAct D
INNER JOIN Payroll P on D.Emp = P.Emp and D.TDate <= P.Pdate



Leslie

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

 

[NHCPA]

Thank you for taking the time to respond.

I would still get one transaction twice, as follows:

Dept Emp Amt
201 101 500
202 101 250
201 101 250

 

[lespaul]

I don't think you're going to be able to do this with a pure SQL solution. You're probably going to have to develop a VBA solution.

 

[PHV]

You may try this pure SQL way:
SELECT D.Dept, P.Emp, P.Amt
FROM Payroll AS P INNER JOIN (
SELECT A.Emp, A.Dept, A.TDate, Min(Nz(B.TDate,#2049-12-31#)) AS EndDate
FROM DeptAct AS A LEFT JOIN DeptAct AS B ON A.Emp = B.Emp AND A.TDate < B.TDate
GROUP BY A.Emp, A.Dept, A.TDate
) AS D ON P.Emp = D.Emp AND P.PDate >= D.TDate AND P.PDate < D.EndDate;

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

 

[NHCPA]

Thank you PHV, I appreciate your time and expertise.