Multiple Expressions using different criteria in a single query similar to SumIf

[Lluewhyn]

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.

[Bulzie]

Can you give example of the fields and values in that table and what you want your final query to look like. I think you could use nested Queries but need more info.

[Lluewhyn]

I gave you all of this information above.

[Lluewhyn]

As far as values, color would be any value under 27, Corrosion would be any value OTHER than 1A or 1B, CO2 would be any value over 1,000, and Methane would be any value over .5%.

[CJ_London]

can't really make sense of your data and suspect it is not normalised, but sounds like you need something like

SELECT sum(iif(color<27,1,0)) as colourfail, sum(iif(corrosion not in ('1A','1B,'CO2).... as corrosionfail,....
FROM myTable
WHERE delDate between....

[Lluewhyn]

That worked. Thank you very much!