Results 1 to 6 of 6
  1. #1
    mMike01 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    5

    Return Value in Select Query based on criteria in another column of Query

    Sorry about the title, I wasn't actually sure how to word it and I'm sort of new doing queries in access. Here's a small example of the table I will be writing the query for, I basically want to just make a report table from it: Call it Tble z_Results

    Location Analyte Results Qualifier
    1 Aluminum 1 J
    1 Aluminum 2
    1 Aluminum 3
    1 Barium 6 J
    1 Barium 7 J
    1 Barium 8 B
    2 Aluminum 4 J
    2 Aluminum 5
    2 Aluminum 6
    2 Barium 2 J
    2 Barium 3 J
    2 Barium 4



    The Select Query I want will return the Analyte, Min of Result, Qualifier of the Min Result, Max Result, Qualifier of Max Result, Location of the Max Result

    I'm having an problem associating the qualifier that is associated with the min result and max result. I haven't even tried getting the location of the Max yet, which I'm sure I will have a problem with.

    I would like the end table to look like

    Analyte Min Result Min Qualifier Max Result Max Qualifier Location of Max
    Aluminum 1 J 6 2
    Barium 2 J 8 B 1

    I've been looking online for answers such as writing join queries, self joins, but I don't have a good handle on SQL and can't get them to work. Just curious if this is possible and how it could be done.

    Thanks in advance

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Need a unique ID field - autonumber will serve - then try:

    MinT1
    SELECT *
    FROM Table1
    WHERE ID IN (SELECT TOP 1 ID FROM Table1 AS Dupe
    WHERE Dupe.Analyte=Table1.Analyte ORDER BY Dupe.Results);

    MaxT1
    SELECT *
    FROM Table1
    WHERE ID IN (SELECT TOP 1 ID FROM Table1 AS Dupe
    WHERE Dupe.Analyte=Table1.Analyte ORDER BY Dupe.Results DESC);

    MinMax
    SELECT MaxT1.Location, MaxT1.Analyte, MinT1.Results, MinT1.Qualifier, MaxT1.Results, MaxT1.Qualifier
    FROM MinT1 INNER JOIN MaxT1 ON MinT1.Analyte = MaxT1.Analyte;

    This is the source that helped me http://allenbrowne.com/subquery-01.html
    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
    mMike01 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    5
    Thank you for the quick response. Most likely I'm doing something wrong, but when I paste this query into access, I get this error: Invalid SQL statement; expected 'DELETE','INSERT',PROCEDURE','SELECT', or "UPDATE'

    I opened the table twice and named it Dupe and also added an auto number column.

    What did I do incorrectly.

    Thanks in advance.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I built a table of the example data, added an autonumber field named ID, and the queries work.

    What do you mean you opened the table twice and named it Dupe? There is no table actually named Dupe in my example. The query involves a self-join and the Dupe name is an alias and exists only within the query.

    Instead of Table1 in my example, use your real table name.

    To create the first two queries, open the query designer in SQL view and type or copy/paste the sql, change the table name.
    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
    mMike01 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    5
    Thanks again, sorry, just bear with me. I had the same same Table1 originally when I tried and added the auto number, but i was simply copy/pasting everything you put in the SQL window.

    I didn't realize I had to run the 3 queries separate as well as removing your description header.

    This time I tried them separate, and the 3rd minmax query returned what I was after.

    Now, I just have to try and figure out how your queries worked so I can apply in the future, which I may need to further complete the table I'm trying to make as there are more columns I want to fill.

    Thanks again

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry, guess I could have been more explicit.

    Carefully study the Allen Browne link. I visit it often - like twice yesterday to assist with two threads needing the TOP parameter - yours was the second one. Good Luck.
    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: 1
    Last Post: 08-31-2012, 01:56 PM
  2. Replies: 5
    Last Post: 08-02-2012, 09:44 AM
  3. Replies: 1
    Last Post: 03-14-2011, 10:04 AM
  4. Replies: 5
    Last Post: 02-09-2011, 11:22 AM
  5. Replies: 1
    Last Post: 04-15-2010, 02:07 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