Query Amount Fields

[needmoremoney]

I have one amount field which I would like to filter out based on the code from another field within the same table.

Example:

TableA

Job ID Amount
2 12 100.23
2 12 222.22
1 12 34.00
3 11 34.09
3 11 333.33
2 11 332.34
4 31 343.01

In my query, how do I add the amounts together based on the ID and Job.

Results:
ID 1Amount 2Amount 3Amount 4Amount
12 34.00 0.00 0.00 0.00
12 0.00 322.45 0.00 0.00
11 0.00 332.34 0.00 0.00
11 0.00 0.00 367.42 0.00
31 0.00 0.00 0.00 343.01

Any help Thanks..

 

[PHV]

A starting point:
SELECT ID, Sum(IIf(Job=1,Amount,0)) AS [1Amount], Sum(IIf(Job=2,Amount,0)) AS [2Amount]
, Sum(IIf(Job=3,Amount,0)) AS [3Amount], Sum(IIf(Job=4,Amount,0)) AS [4Amount]
FROM TableA
GROUP BY ID

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

 

[needmoremoney]

Thanks PHV.. but I'm lost in all of this.. I'll have to rethink my way to get this to work. Thanks again for your input.

 

[dhookom]

Have you tried a crosstab query where ID and Job are Row Headings, Job & "Amount" are the Column Headings, and Sum of Amount is the Value.

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]