Sum Query

[mithani]

Hello,

I am working on statement of accounts. I have a purchasing table (POno, POdate, POdescription, POAmount, .........). What I am looking for is, If I have difference amount against same POno, my query should show only one line with POno, PoDate, POdescription and POAmount(total Amount as per POno).

Any idea.

Regards
Mithani

 

[John Big Booty]

Have you had a play with the totals Button? That's the Sigma (Σ) button that appears in the tool bar when a query is in design view.

 

[mithani]

Thanks John,

Yes I did try but didn't help. Still showing duplicate POno.

Regards
Mithani

 

[John Big Booty]

Which filed is it that is causing the duplication? Instead of grouping that field you may need to show the Max or use one of the other available functions.

 

[mithani]

Hi John,

I have attached sample DB. I have tried group and max but still POno is showing duplicates number.

My query should show only three records

1001 ~~~ POamount(sum)
1002 ~~~ POamount(sum)
1003 ~~~ POamount(sum)

Thanks
Mithani

 

[John Big Booty]

The problem is being caused by the field POdescription you are getting duplicates because some PO's have multiple PO Descriptions. You will either have to remove it from the query or Count or use one of the other functions such as first or last.

Alternatively you will have to work out a method of concatenating the various descriptions into one filed, and then pull the concatenated field into your Sum query rather than the raw POdescription.

 

[mithani]

Thanks John.

 

[John Big Booty]

Glad to have helped