How to do a SUMIF in an access query

[accessaspire219]

Hi, I have a query which contains item numbers, branch, purchase order number, qty and due dates. This query looks for the closest due dates to the current date and displays those records. In some cases there are two purchase orders having the same due date and both qualify as the earliest due date therefore it displays both the purchase orders for that item.

Now, what I need it to do is that, if the earliest due dates are same for a particular item branch combination, it should add up the quantity and display it only once per item-branch combination.

Basically in simpler terms, it should look do a sumif on the qty and the criteria should be the item branch combination. I want one line per item-branch combination.

Does anybody have any idea how this could be done??
Thanks.

 

[Brianwarnock]

Sounds like a Totals query Group by on all fields except quantity which is Sum.

Brian

 

[accessaspire219]

Hi Brian,
Thanks for your reply. I may be wrong, but my only concern is that, if I do a group by on everything including the PO numbers it would only total if the two PO numbers are same, however, I want it to total only if the two PO numbers are different but the item, branch and due dates are same.

Thanks.

 

[Brianwarnock]

Yes that's true, missed that point, but as any query involving sum is a totals query and an aggregate function has to apply to all fields I'm struggling to get my head round this at the moment. Wull think about it.


Brian

 

[accessaspire219]

Thanks!! Keep me posted in case you think of something.

 

[Brianwarnock]

I'm not getting this, if the PO are different when you Sum to show one line, what PO would you show, and how does this tie in with

In some cases there are two purchase orders having the same due date and both qualify as the earliest due date therefore it displays both the purchase orders for that item.

I'm just not with this , sorry.

Brian

 

[accessaspire219]

No problem
It could show any of the two PO's, it doesn't matter - whichever is easier to show from a programming perspective