Results 1 to 9 of 9
  1. #1
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20

    Question Union query filtering


    Hey guys! I am relatively new to query building and have stumbled into a bit of an issue. I have taken two sets of data and united them where there are similarities with a union. My goal is to filter this data to only look at where there are duplicate ID's then further down to the ID with the Higher value hour meter. I can't seem to figure it out. Here is what I have successfully done so far. Thanks for any help in advance
    Click image for larger version. 

Name:	query.PNG 
Views:	12 
Size:	5.2 KB 
ID:	35488
    Click image for larger version. 

Name:	table.PNG 
Views:	11 
Size:	45.1 KB 
ID:	35489

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this query using that query as a base:

    SELECT AssetID, Max([Hour Meter]) As MaxHour
    FROM QueryName
    GROUP BY AssetID
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    Save that query and make a new query with that one as input. Group by Asset ID and display Max([Hour Meter]). If you only want the duplicates from the source to be in the output (one row per ID), add a criteria count(*)>1.

    edited to add: same as post above mine, which appeared after I posted this one

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by JSR View Post
    edited to add: same as post above mine, which appeared after I posted this one
    Fast fingers JSR. Hope you're enjoying the air races.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20
    Wow, you guys are fast! Going to try both of the suggestions and I will let you know what happens.

  6. #6
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20

    Current State

    OK, so I took both of you guys advice and implemented it into another query, I do believe we are on the right track, but I've ran into another problem. whenever I set the count(AssetID) = 1, it displays all of the results, which is wrong, and when I set it = 2 (the maximum possible amount of duplicate ID's is 2), it displays 0 results.
    Click image for larger version. 

Name:	UnionQuery.PNG 
Views:	10 
Size:	4.4 KB 
ID:	35492
    Click image for larger version. 

Name:	ResultSet.PNG 
Views:	10 
Size:	44.0 KB 
ID:	35491
    Click image for larger version. 

Name:	Error.PNG 
Views:	10 
Size:	4.3 KB 
ID:	35490
    Click image for larger version. 

Name:	result.PNG 
Views:	10 
Size:	6.0 KB 
ID:	35493

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're grouping on the date/time field, which neither of us suggested. If you need that value, this may work:

    http://www.baldyweb.com/LastValue.htm

    but using the max on the meter field instead of the date field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20

    Awesome!

    aha, you are right, I completely overlooked the date field. Changed it to max(datefield) (this will be accurate because if the meter value is higher, the date should be later) and now it works perfectly! Thanks alot guys!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. That will work unless the meters turn over.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 4
    Last Post: 08-09-2017, 12:06 PM
  2. Convert Union Query to non-union help
    By Ekhart in forum Queries
    Replies: 2
    Last Post: 01-10-2017, 03:39 AM
  3. Replies: 4
    Last Post: 12-20-2015, 02:35 PM
  4. Union Query
    By jo15765 in forum Queries
    Replies: 1
    Last Post: 02-04-2014, 06:35 PM
  5. Replies: 8
    Last Post: 10-22-2012, 07:43 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