Get rid of duplication

[mithani]

Hi,

I am working on Accounts Statement query.

I have compiled my StatementQuery which is based on two queries, purchaseQuery and invoiceQuery. If I have got one purchase order of amount $1,500.00 and we received three invoices $600 + $500 + $400, my query shows as below

POno, POamount, Inv#, InvAmount
1001, $1,500.00, 273, $600.00
1001, $1,500.00, 274, $500.00
1001, $1,500.00, 275, $400.00

Is there any way I could show purchase amount only once:

POno, POamount, Inv#, InvAmount
1001, $1,500.00, 273, $600.00
1001, 0, 274, $500.00
1001, 0, 275, $400.00

Any clue

Thanks
Mithani

 

[boblarson]

Use a report for the output and there you can supress duplicates by selecting YES where it says HIDE DUPLICATES in the appropriate Section.

 

[mithani]

Thanks Bob,

Please have a look attached statement. When I sum Purchase amount it multiply number invoices, i.e. $3575 x 6 (number of invoices) = $21,450.00

Regards
mithani

 

[CameronM]

When I sum Purchase amount it multiply number invoices

That's what you would expect if you are summing the column. You need to remove the sum of POamount in your group footer to display just the actual amount.

 

[mithani]

Thanks CameronM

That should be OK if i have only one purchase order but what about if I have got multiple purchase order numbers. If I remove sum, than only last PO amount will be shown in Total.

Regards
Mithani

 

[mithani]

don't worry, all done. I used running sum and working perfect.

Regards
Mithani

 

[CameronM]

Hi Mithani,

Sorry, I assumed you had used a grouping with footer to the report (grouping on POAmount). If you had, then each purchase order would have its own totals line.