Results 1 to 3 of 3
  1. #1
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125

    Select based on Max Query

    There are three fields involved in this query...JobNum, BarSize, and BarWgt.



    I'm trying to select the BarSize for every JobNum where the BarWgt is max out of all the entries

    So far for the Query I have...
    Code:
    SELECT dbo_tblBars.JobNum, Max(dbo_tblBars.BarWgt) AS MaxOfBarWgt
    FROM dbo_tblBars
    GROUP BY dbo_tblBars.JobNum
    ORDER BY dbo_tblBars.JobNum;
    ...which gives me the max BarWgt for each JobNum, but I'm more interested in the BarSize label and I can't seem to return that.

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    you say there are 3 fields involved in this query, but there's only 2 in your ex. ???? confused...

    you cannot return a single GROUP BY on JOBNUM with two other fields that have to be returned. GROUP BY requires that all fields not involved in a mathematical function be GROUPED.

    there are other ways, but are more complex.

  3. #3
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    Got the job done using two queries (was just wondering if I could get it all done in one). First one is finds Max BarWgt grouped by JobNum, selects BarSize where BarWgt = MaxBarWgt from the first query.

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

Similar Threads

  1. Replies: 7
    Last Post: 11-24-2014, 02:11 PM
  2. Select Random Records Based on Conditions
    By access123 in forum Queries
    Replies: 1
    Last Post: 10-27-2010, 10:25 AM
  3. Replies: 2
    Last Post: 03-14-2010, 07:27 PM
  4. split form select records based on a criterea
    By ramkitty in forum Access
    Replies: 8
    Last Post: 03-12-2010, 06:19 PM
  5. Select a certain field of a record based on ID
    By cesarone82 in forum Access
    Replies: 3
    Last Post: 06-06-2009, 01:16 PM

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