Results 1 to 12 of 12
  1. #1
    pintohoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6

    Showing records with highs in multiple fields

    Hi - and, thanks in advance for any assistance here. I am certain my question is very very basic. I only used access about 20 to 30 years back and cannot remember this bit.



    I have searched the help, but suspect I am not asking the right questions - sorry if this is very basic.

    I have a table built and can easily build queries --- what I want is to know which record in one field -------- has the highest value in another field, but also has the highest values in a few other fields -

    I only want to see where a record achieves the highest values in multiple fields

    in other words (I think) -------- show me the highest value in field c but, only if it's the highest value in field d and e and f etc etc.

    I am using Access 2019 (there was only a choice for 2016 at the latest)


    that's the simple guts of it


    if I can go further to show the highest ranking 3 records - that would be great - but,I will settle for what I can get. Happy to be directed to previous conversations if it's been discussed elsewhere.

    thanks in advance

    pinto

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Show example of source data and example of desired output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pintohoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6
    Thanks for your reply --

    example of the source data is that I am bringing in an exel spreadsheet - with the columns of interest formatted to number --

    I am happy to show you an example - but, what exactly do you want me to show you? - would this do

    Click image for larger version. 

Name:	Capture.JPG 
Views:	17 
Size:	60.5 KB 
ID:	38867


    an example of desired output would be merely to show if a number in one other field has a max. value, but, only show when it's got max values in the other fields as well

    so eg. if record 1 in the first field has the highest value in field 3, but not the highest in any other field -- I don't get a return -

    but, if it's got the highest values in all fields then I get to see that record.



    What I would like to see is when it isn't the highest across all other fields - I would like to see the highest closest match - if that makes sense - ie. if record 1 had the highest values in say 4 fields but not 6 and no other records scored more - then, I would like to see that record

    hope that's clear enough - please let me know if I can clarify if needed

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So from that example, which record of the 12 shown would you want to pull? If you can describe steps to accomplish manually, then should be programmable.

    Which record has the most maximum values? How would you resolve this? Perhaps perform 7 queries that order descending? How should ties be dealt with? I tried to eyeball this and didn't get very far.
    Last edited by June7; 06-24-2019 at 12:42 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,
    can't this be simply done by
    Where Field1 >= Field2 and Field1 >= Field3 and Field1 >= Field4 …..
    or am I missing something?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Will that return record with most maximum values?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    pintohoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6
    Quote Originally Posted by June7 View Post
    So from that example, which record of the 12 shown would you want to pull? If you can describe steps to accomplish manually, then should be programmable.

    Which record has the most maximum values? How would you resolve this? Perhaps perform 7 queries that order descending? How should ties be dealt with? I tried to eyeball this and didn't get very far.

    hi all, -- I thought it might be simpler to put up an example which I have doctored -- one that would be found if I had the right solution


    Click image for larger version. 

Name:	Capture1.JPG 
Views:	10 
Size:	88.8 KB 
ID:	38873


    ok, in this example - I would like the db to find record 7 only to display. ----------- if I can get that done - that's a great start.



    if I could then also get it to show the next say 2 records that were a best fit (sorry, didn't doctor more examples) - say a couple of more records had max hits in say 3 fields - then no other records had other max hits - then, it would be nice to see the 3 records show up ------------ the obvious one that hits all targets - and the 2 that would be next closest to the fit

    I hope that makes sense.

  8. #8
    pintohoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6
    Quote Originally Posted by NoellaG View Post
    Hi,
    can't this be simply done by
    Where Field1 >= Field2 and Field1 >= Field3 and Field1 >= Field4 …..
    or am I missing something?
    thanks NoellaG -- I have tried this -------- didn't get a return -- but, I will check it and try it again in a few hours - I might have mucked up the formula.

    it would be field 2 for me -- field one is the primary key.

    thanks so far

  9. #9
    pintohoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6
    Apologies - I might have stuffed people around with the last example as I had been fooling around with the numbers to force it to show a result -

    Forget the first record- just imagine it’s disappeared in a puff of pink smoke

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    See if this gets you something workable:

    SELECT Table1.*, IIf([F1]=[MaxOfF1],1,0)+IIf([F2]=[MaxOfF2],1,0)+IIf([F3]=[MaxOfF3],1,0)+IIf([F4]=[MaxOfF4],1,0)+IIf([F5]=[MaxOfF5],1,0)+IIf([F6]=[MaxOfF6],1,0)+IIf([F7]=[MaxOfF7],1,0)+IIf([F8]=[MaxOfF8],1,0)+IIf([F9]=[MaxOfF9],1,0) AS Wt
    FROM Table1, (SELECT Max(Table1.F1) AS MaxOfF1, Max(Table1.F2) AS MaxOfF2, Max(Table1.F3) AS MaxOfF3, Max(Table1.F4) AS MaxOfF4, Max(Table1.F5) AS MaxOfF5, Max(Table1.F6) AS MaxOfF6, Max(Table1.F7) AS MaxOfF7, Max(Table1.F8) AS MaxOfF8, Max(Table1.F9) AS MaxOfF9
    FROM Table1) AS Query1
    ORDER BY IIf([F1]=[MaxOfF1],1,0)+IIf([F2]=[MaxOfF2],1,0)+IIf([F3]=[MaxOfF3],1,0)+IIf([F4]=[MaxOfF4],1,0)+IIf([F5]=[MaxOfF5],1,0)+IIf([F6]=[MaxOfF6],1,0)+IIf([F7]=[MaxOfF7],1,0)+IIf([F8]=[MaxOfF8],1,0)+IIf([F9]=[MaxOfF9],1,0) DESC;

    F1 is the first data field - primary key is in field ID
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    pintohoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6
    Hi, thanks for that -- before I begin - I better clarify something --

    I have quite a few fields that I don't require to be involved - so,

    What I might do is to build a query showing only the fields that I want involved - then, build a query on that query and, when i do that - I put all you have said, using the correct field numbers into the criteria box -- is that correct?? - put it in just as you have written?????


    TIA


    PS - I haven't done the first query yet - but, played with what you said -- sorry, I might need spoon feeding here -- this is what I think it's going to give me - so, I will need to know what to put where exactly - sorry


    Click image for larger version. 

Name:	1.JPG 
Views:	7 
Size:	22.8 KB 
ID:	38876

    Click image for larger version. 

Name:	2.JPG 
Views:	7 
Size:	16.5 KB 
ID:	38877

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can certainly specify fields instead of using * wildcard.

    What I did was create aggregate query object first.

    Then I built another query referencing aggregate query. When that worked, I copy/paste first query SQL into second query to have an all-in-one. You can do as two separate queries if you prefer.

    No, expression does not go under ID field. It goes on Field row to create a calculated field. Then Descending on Sort row under that calculated field. Expression will not have AS x, that is aggregate query syntax. Look closely at example. See nested aggregate query. Then outer query references aggregate fields in expression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-03-2017, 03:20 PM
  2. Replies: 18
    Last Post: 01-27-2012, 12:53 PM
  3. Replies: 1
    Last Post: 01-17-2012, 02:54 PM
  4. Replies: 1
    Last Post: 12-10-2009, 08:41 PM
  5. Replies: 7
    Last Post: 12-07-2009, 07:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums