December 4th, 2013, 01:33 PM
Query to Compare 2 Number fields and select greatest
I'm using Access 2010.
I have a large database.
The table called Product contains information specific to individual products. There are 3 fields that I'm concerned about right now: NFPAHealth, NFPAFlammability, and NFPAReactivity.
Each of these fields contain numbers, 0 through 4.
For a particular record, I need to compare the entry in the 3 fields and select the highest, and group by that.
In other words, say Record 1 has 1 3 2 in those 3 fields, respectively.
I need Record 1 to be put into the group Flammability 3.
Let's say Record 2 has 2 0 0 in those fields. I need Record 2 to be put into the group Health 2.
How exactly would I go about doing this?
This is just the first step in a pretty complicated logic process for fire marshal reporting; but once I get this logic process working correctly, the rest should be a snap.
Thanks in advance for your help!
December 4th, 2013, 07:28 PM
what does "put into a group" mean?
December 5th, 2013, 10:08 AM
What "Put into a group" means
The report requires that the data be grouped...by those products where the Flammability number is the highest number, and sub-total the QOH (quantity on hand) of each, then by those products where the Health number is the highest number (subtotaled), then by those products where the Reactivity number is the highest number (subtotaled).
The report will be such:
Product 1 QOH
Product 2 QOH
Product 3 QOH
Subtotal Sum of QOH
Product 4 QOH
December 5th, 2013, 11:18 AM
aha, i see
by "grouped" you mean sorted or ordered
you will not be able to obtain the nicely formatted data from the sql query, with group header and detail lines, and especially not subtotals, but you can easily do those things while processing the query results
in an application language like php in an access report module
with me so far?
Last edited by r937; December 5th, 2013 at 11:21 AM.
December 5th, 2013, 11:26 AM
Yes, I'm with you.
I've created a query and used the Expression Builder to create a new field called GRP, as such:
I'm 80% to my goal right now...but my issue is, the query is not returning any records where the NFPAFlammability is 0 but either of the other 2 fields is 1, 2, 3, or 4
Grp: IIf(([NFPAFlammability]>=[NFPAHealth]) And ([NFPAFlammability]>=[NFPAReactivity]),"F",IIf([NFPAHealth]>=[NFPAReactivity],"H","R"))
I need to get those records, too, but I cannot figure out why those records are being excluded.
The only other criteria in the query is that NFPAFlammability <>IsEmpty