Duplicate records in Query/Report

[Janet Friesen]

Hello,

I'm hoping someone can help me with this problem. I have
to create a report from two tables. My database is for a
trust which has paid out monies to people. One table is
called contractors. The other table is called workers.
Both the contractors and workers work for a larger
company. But the workers work indirectly for larger
company and directly for the contractor. The contractors
work directly for the company and sometimes have workers
working for them, and sometimes don't.

My query has to show all the contractors who work for the
company, the money that has been paid out to them, and
all the workers that have worked for each contractor and
the money that has been paid out to them. I have managed
to create that query by first creating other queries and
adding joins.

The problem is this: each contractor has an amount
listed that has been paid out to them, but it also has a
list of all workers that have worked for them and amounts
that have been paid out to them. So if there is more
than one worker attached to a contractor, the dollar
amount paid out to the contractor is listed as many times
as there are workers attached. Then my totals in my
report multiply the amount for each contractor by the
number of workers attached and sum those totals.
Obviously that is not what I want. I'm hoping this makes
sense. What I need to do is not only hide the duplicate
dollar amounts for the contractors, but not have them
included in my total calculation. Is there any way I can
do this? Thank you!

Janet Friesen

 

[Tom Ellison]

Dear Janet:

First, just try to get the sum for all workers by contract.

Is there a single row in the workers table for each worker /
contractor combination?

It may looke something like:

SELECT Contractor, SUM(AmountPaid) AS AmountPaid
FROM Worker
GROUP BY Contractor

Next, left join this to a query that adds the amount for the
contractor:

SELECT C.Contractor, C.AmountPaid + NZ(W.AmountPaid, 0)
FROM Contractor C
LEFT JOIN (SELECT Contractor, SUM(AmountPaid) AS AmountPaid
FROM Worker
GROUP BY Contractor) W ON W.Contractor = C.Contractor

You haven't given me specifics, so I'm just guessing at your actual
table structure. Hope I've come close enought to be of some use.

The basic theme here is to aggregate the amounts paid over all the
workers for each contractor, then add the amout paid directly to that
contractor.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts