#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0

    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!

    Marcie
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    what does "put into a group" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0

    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:

    Flammability 4
    Product 1 QOH
    Product 2 QOH
    Product 3 QOH
    Subtotal Sum of QOH

    Flammability 3
    Product 4 QOH

    etc. etc.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    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.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0

    With you...


    Yes, I'm with you.

    I've created a query and used the Expression Builder to create a new field called GRP, as such:

    Code:
    Grp: IIf(([NFPAFlammability]>=[NFPAHealth]) And ([NFPAFlammability]>=[NFPAReactivity]),"F",IIf([NFPAHealth]>=[NFPAReactivity],"H","R"))
    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

    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

    Thanks.

IMN logo majestic logo threadwatch logo seochat tools logo