Let's see if this makes sense.
I have a table that lists truckloads of product (gallons of natural gas liquids), and has a variety of characteristics for each record, including its chemical composition, and various criteria which would show that the product meets or fails specifications. This last list includes color, corrosion, CO2 content, and Methane content. Please note that most truckloads will fail at least one specification, and quite a few will fail more than one.
Got that? Good.
What I am wanting to do is run a query that sums up the total gallons in a month that failed each specification. The larger purpose would be to compare that to the total number of gallons period for percentage. So, I could see that a given month might show that 75% failed color testing, 47% failed corrosion tests, 5% failed CO2, etc. I would then be able to trend that out to see if various specification failure rates were increasing or decreasing over time.
This is really easy to do if I only check for one of these specifications at a time. It would be three fields, Date (with a criteria of the date range), Total Gallons, and the Specification I am checking. But that means that I'm now running four separate queries, five if you count needing to do a check for unfiltered total gallons in a month to calculate percentage.
What I am wanting to do:
Have all of this information in a single query by using functions and criteria in individual expressions:
Date| Color Treated Gallons |Corrosion Treated Gallons|CO2 Treated Gallons|Methane Treated Gallons
I have this working in my various monthly spreadsheets using the SumIf function in Excel (Sum Total Gallons IF Criteria X ="") for each column.
Is there something similar I can do in Access? Most things I'm thinking of would simply add multiple criteria to the same query, making it so the query would have to pass ALL of four of the criteria to calculate one total, which is not what I'm after, which is to have FOUR separate totals in a query that each check for different criteria.