Sum of Different Transaction Types

[alpine82]

I need to generate a report, that sums up different transaction types. i.e., I have an ORDERS FILE, PARTS DETAILS FILE and LABOR DETAILS FILE. Within the labor category, there are 3 types: INTERNAL, EXTERNAL and MAINTENANCE. Within the parts category, there are 3 types: TIRES, FLUIDS and PARTS. The report needs to total each category type, over all the orders, by a specific ID#. I have tried several queries, but all fail when there is no record in one of the details files for that particular category type (ex: if an order has no TIRES, then there is no detail record for it, so the summation query fails to return any data). Is there some trick I am missing? Thanks.

 

[simongallop]

Create a table / query with the list of results that you want. Use this to force the data in a query.

eg. list of available categories (tblAvail) and list of things you are summing (tblInfo) Use SELECT ..... FROM tblAvail LEFT JOIN tblInfo ON {whatever you link}

In the design view of the query you would link using 2 (Include ALL records from tblAvail and only those from tblInfo....)

This will then show the list of what is available and if there is a value it will show it otherwise it will show a null. To convert the null to 0 use the NZ function

HTH

 

[alpine82]

Thanks

Hey Harry, thanks alot, the idea with a little tweaking worked. i really appreciate the help.