CODEWITHSUNDEEP
sumcriteria
Alex Doran
Alex Doran

Reputation: 11

Acess Report - Sum the Value of a Field If the value of another Field Matches Criteria

I have an Access Database and i'm trying to create a report that has me a bit stumped. Basically this report is going to display each employee's order processing performance based on a user specified date range, the report gives an itemized detail of each unique product on the order and it's price and Quantity.

Each Product has an 'Assembly Category' either 'DRFLUSH' or 'FRAME'.

In the summary of each Order i want to total the Quantities of each Assembly Category in a separate field.

Initially i rushed this report and have now found that the way i intended on completing this task is incorrect as the value given is only a Count of how many times an instance of each value 'DRFLUSH' or 'FRAME' occurs. I started with as follows: (Please ignore the bad practice with field naming i have taken this database on from a previous employee).

=Count(IIf([ASSEMBLY ITEM CATEGORY]="FRAME",1,Null))

And

=Count(IIf([ASSEMBLY ITEM CATEGORY]="DRFLUSH",1,Null))

However as previously stated this is wrong. I want the fields to sum the Quantity of each line item but only where the criteria is matched.

Any help is greatly appreciated, i'm sure this is a ridiculously simple task however i just cannot seem to wrap my head around it today.

Thanks

Alex

Upvotes: 0

Views: 251

Answers (1)

Alex Doran
Alex Doran

Reputation: 11

Sorted this by changing the statements to as follows:

=Count(IIf([ASSEMBLY ITEM CATEGORY]="FRAME",[QTY],Null))

=Count(IIf([ASSEMBLY ITEM CATEGORY]="DRFLUSH",[QTY],Null))

Upvotes: 1

Related Questions