Duplicate Numbers in Report

[bwalk037]

Hello,

I have a report that is pulling its data from a query. In the query I have two tables. One that pulls in the invoices and the amount of each invoice and the second table that pulls in the total amount of the invoices by account, the account total.

The problem I am having is when I run the query the account total duplicates itself on every line for each invoice and I only want it to show once for each account. So when I try to do a sum at the bottom of the report it sums all the duplicate amounts and not the singular amount for each account. How would I do this?


For Example, It looks like this:

Inv # Inv Amt. Acct # Total Acct. Balance

1 $12 1 $52
2 $14 1 $52
3 $26 1 $52
4 $34 2 $84
5 $40 2 $84
6 $10 2 $84
7 $33 3 $74
8 $25 3 $74
9 $16 3 $74


I would like it to look like this:

Inv # Inv Amt. Acct # Total Acct. Balance

1 $12 1 $52
2 $14 1 $0
3 $26 1 $0
4 $34 2 $84
5 $40 2 $0
6 $10 2 $0
7 $33 3 $74
8 $25 3 $0
9 $16 3 $0


How would I do this?

Thank you!

 

[pbaldy]

Not sure what the purpose of the second query is. If your report was grouping on the account number field, you could add a group footer that either summed up the invoice amounts or grabbed the value from the second query, if you continue to use it for some reason.