Results 1 to 6 of 6
  1. #1
    blue_echo's Avatar
    blue_echo is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    East Coast
    Posts
    11

    Question greater amount criteria

    I'm working on a query that calls up debtors who have more than one employer. I want the query to pull up the debtor based on the amount of wages but I want it to pull their larger wages. What would be the criteria for this if I don't have the $ amounts since they all vary?

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Not quite sure I follow.
    Can you provide a sample of your data, and what you want to return?

  3. #3
    blue_echo's Avatar
    blue_echo is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    East Coast
    Posts
    11
    Sure.

    So John Doe works at Best Buy and makes $5341.98 per quarter and he also works at Giant Food and makes $3415.23 per quarter. I want the query to call up the employer/wages for which he makes the most money. But every person makes a different amount so I don't have exact figures to put in the criteria. Does that make more sense? thanks for any help

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You can do this in two queries (or a single nested-query). I will explain the two query approach:

    Query 1
    Add your table
    ONLY add the employee and wages field to your query.
    Click on the Totals icon (looks like a Sigma). This will add a Totals row under each field in the Query Builder.
    Change the "Totals" row value under the wages field from "Group By" to "Max"
    If you look at the results, this will show each employee and their maximum wage

    Query 1
    Add both the original table and the query you built above to this new query
    Join the original table and built query on both the employee and wage fields
    Return the employee, employer, and wage fields from the original query
    If you look at the results, this will show you what you want

    So, in a nutshell, in the first query, we are just finding out the maximum wage for each person.
    In the second query, we are joining that query with the original table to bring back all the other fields that you want to see.

  5. #5
    blue_echo's Avatar
    blue_echo is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    East Coast
    Posts
    11
    Perfect! Thank you so much. This is such a big help

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!

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

Similar Threads

  1. Less/greater than or equal to criteria question.
    By McArthurGDM in forum Queries
    Replies: 11
    Last Post: 04-17-2015, 02:20 PM
  2. Replies: 7
    Last Post: 02-09-2015, 12:35 PM
  3. Criteria to suppress zero amount from expression
    By George Pollow in forum Queries
    Replies: 6
    Last Post: 05-05-2014, 04:00 PM
  4. Replies: 5
    Last Post: 01-09-2014, 03:06 PM
  5. Replies: 1
    Last Post: 07-26-2013, 11:30 AM

Tags for this Thread

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