Compare Data in 3 Fields in 1 Table & Group By the largest

[MarcieFess]

I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.

The fields I will need are as follows:

PRODUCT table:
ProductName
Chemical
ChemicalAbstract
PhysicalState
NFPAHealth
NFPAFlammability
NFPAReactivity

qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):
QOH

I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.

The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4

I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.

For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.

In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.

If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity

Ultimately the report will be grouped as follows:

Flammability
Rating 4
Product 1
Product 2
Product 3

Rating 3
Product 1
Product 2
Product 3

Rating 2
Product 1
Product 2
Product 3

Health
Rating 4
Product 1
Product 2
Product 3

Rating 3
Product 1
Product 2
Product 3

Rating 2
Product 1
Product 2
Product 3

Reactivity
Rating 4
Product 1
Product 2
Product 3

Rating 3
Product 1
Product 2
Product 3

Rating 2
Product 1
Product 2
Product 3

and each of the groups will be sub-totalled.

I'm stumped at trying to create the query in the first place.

The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store; I just need help with the logic for getting the products grouped correctly.

Marcie

 

[Geotch]

I would do several queries and then join them together.

First query for this:
I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.
I would do an if statement for the values.

Test1: Iif([yourfield] is null, 1,0)
Repeat for all fields, and call test2, test3, test4
Then create a field adding all the tests
Test final: [test1]+[test2]+....

If test final is > 0 one field is null so you have that answer.

 

[Geotch]

Sorry only three tests for the above instead of four.

 

[Geotch]

For this:

For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.

More if statements are needed, four for each of the three items.
flam4: iif([Nfpaflammability]=4,1,0)
Flam3: iif([Nfpaflammability]=3,1,0)
And so on for 2, 1 and 0.

Repeat for the other two items.

You can then do calculations by adding up the 4's, 3's, 2's etc from each group.

This should get you started. If you can upload sample data, might have time to put some of these together for better examples.

 

[MarcieFess]

My solution so far:

SELECT Product.NFPAFlammability, Product.NFPAHealth, Product.NFPAReactivity, Product.ProductName, Product.Size, Product.Units, Product.ReportUnits, (([Product].[Size]*tblStoreProducts.MaxUnits)/[Product].[ConversionRate]) AS QOH, tblStoreProducts.StoreKey, tblStoreProducts.Storage, tblStoreInformation.StoreNum, IIf(([NFPAFlammability]>=[NFPAHealth]) And ([NFPAFlammability]>=[NFPAReactivity]),"F",IIf([NFPAHealth]>=[NFPAReactivity],"H","R")) AS Grp
FROM tblStoreInformation INNER JOIN (tblHazardClass INNER JOIN (Product INNER JOIN tblStoreProducts ON Product.UPC = tblStoreProducts.UPC) ON tblHazardClass.HazardKey = Product.HazardKey) ON tblStoreInformation.StoreKey = tblStoreProducts.StoreKey
WHERE (((Product.NFPAFlammability)<>IsEmpty([NFPAFlammability])))
ORDER BY Product.NFPAFlammability DESC , Product.NFPAHealth DESC , Product.NFPAReactivity DESC;

The only issue with this so far is that if NFPAFlammability = 0, but NFPA Health > 0, it's not showing up in the query results.

Any ideas?