Re: Duplicate records in Query/Report
From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 02/03/04
- Next message: Tom Ellison: "Re: Most Characters Matched query"
- Previous message: Tom Ellison: "Re: Querry with Check Box?"
- In reply to: Janet Friesen: "Duplicate records in Query/Report"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 02 Feb 2004 22:03:24 -0600
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
On Mon, 2 Feb 2004 17:31:38 -0800, "Janet Friesen"
<anonymous@discussions.microsoft.com> wrote:
>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
- Next message: Tom Ellison: "Re: Most Characters Matched query"
- Previous message: Tom Ellison: "Re: Querry with Check Box?"
- In reply to: Janet Friesen: "Duplicate records in Query/Report"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
