Combine tables having a certain field only in one table

[NHCPA]

I am trying to create a query that will give me the total cost per employee/Month/Dept.

Table #1 – Employee #, Month, Payroll Amount, Dept #
Table #2 – Employee #, Month, Benefit Amount
Table #3 – Employee #, Month, Deduction Amount

When an employee switches departments he will have more then one record in table #1, but in the other two tables he would only have one record.

The benefit and deduction amounts can either be applied ratably (by payroll amount) to the departments, or to any one dept for the employee in that month.

Any help would be greatly appreciated.

 

[dhookom]

Since you haven't received a reply yet, how about entering a few sample records and desired output into a message in this thread? Some of us have no idea what you mean by "ratably".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NHCPA]

Thank You, I hope this helps. However it does not have to be ratably either one will do.


Table 1
Emp # Month Payroll Dept
101 105 200 30
102 105 500 250
103 105 750 130
101 105 600 100

Table 2
Emp # Month Benefit
101 105 100
102 105 100
103 105 75


Table 3
Emp # Month Deduction
101 105 -25
102 105 -25
103 105 -40

Output by Any Dept
Emp # Month Combined Amount Dept
101 105 275 30
102 105 575 250
103 105 785 130
101 105 600 100

Output Ratably by Payroll Amount
Emp # Month Combined Amount Dept
101 105 218.75 30
102 105 575 250
103 105 785 130
101 105 656.25 100

 

[lespaul]

Ok, I can see how you calculated 'Combined Amount' in the 'Output by Any Dept', how do you get 218.75 in the other one?

Leslie

 

[NHCPA]

Employee 101 was in two dept for this month, he earned 200 while in dept 30, and 600 in dept 100.

So, dept 30 was 25% (200/(200+600)), and dept 100 was 75% (600/800). now benefits of 100-25=75 * 25% = 18.75. 18.75 + 200 = 218.75.

 

[dhookom]

I would start by normalizing the amounts using a UNION query:

SELECT EmpNum, Month, Sum(PayRoll) as Amt
FROM TableOne
GROUP BY EmpNum, Month
UNION ALL
SELECT EmpNum, Month, Benefit
FROM TableTwo
UNION ALL SELECT EmpNum, Month, Deduction
FROM TableThree;

Then create another query based on TableOne and the union query:

SELECT TableOne.EmpNum, TableOne.Month, TableOne.Dept, Sum([Amt]/(SELECT Count(*) FROM TableOne o WHERE O.EmpNum = TableOne.EmpNum AND O.Month = TableOne.Month)) AS CombinedAmt
FROM TableOne INNER JOIN quniEmpBenDed ON (TableOne.Month = quniEmpBenDed.Month) AND (TableOne.EmpNum = quniEmpBenDed.EmpNum)
GROUP BY TableOne.EmpNum, TableOne.Month, TableOne.Dept;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NHCPA]

Thank You very much dhookom.

I tried the above, however in the above example it gives me two lines for emp# 101, each in the amount of 437.50.

It seems that it is averaging both dept's for this emp. The total between both dept's is correct however the per dept is not.

 

[dhookom]

Union Query "quniEmpBenDed":

SELECT EmpNum, Month, Benefit AS Amt
FROM TableTwo
UNION ALL SELECT EmpNum, Month, Deduction
FROM TableThree;

SELECT TableOne.EmpNum, TableOne.Month, TableOne.Dept, First([Payroll])+(Sum([Amt])*First([Payroll])/(SELECT SUM(Payroll)  FROM tableOne O WHERE TableOne.EmpNum = O.EmpNum AND TableOne.Month = O.Month)) AS TotAmt
FROM TableOne INNER JOIN quniEmpBenDed ON (TableOne.Month = quniEmpBenDed.Month) AND (TableOne.EmpNum = quniEmpBenDed.EmpNum)
GROUP BY TableOne.EmpNum, TableOne.Month, TableOne.Dept;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NHCPA]

Thank You dhookom, I appreciate your time.

The above works OK, however it only gives me employees that have a record in both tables. I need to have all payroll even if there is no benefits for this employee. Sorry I was not clear about this.

Changing it to a left join, only brings in the record without an amount.

 

[dhookom]

Change your query to LEFT JOINs.

SELECT TableOne.EmpNum, TableOne.Month, TableOne.Dept, First([Payroll])+(Sum([Amt])*First([Payroll])/(SELECT SUM(Payroll)  FROM tableOne O WHERE TableOne.EmpNum = O.EmpNum AND TableOne.Month = O.Month)) AS TotAmt
FROM TableOne LEFT JOIN quniEmpBenDed ON (TableOne.Month = quniEmpBenDed.Month) AND (TableOne.EmpNum = quniEmpBenDed.EmpNum)
GROUP BY TableOne.EmpNum, TableOne.Month, TableOne.Dept;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]