Results 1 to 3 of 3
  1. #1
    PHILnTEX is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    6

    Max values of a record subset across tables without using domain functions

    Hi all,
    I'm trying get the max value of a subset of records without using domain functions. My database has well over 250K records and using DMAX or any other domain function just kills the db response time. I’m new to access and this has turned into quite a challenge for me and could use a little help.



    I’ve attached a picture showing a very simplified view of what I want to accomplish.

    Basically, I have a table of item whose names contain a lot of junk. Let’s call this table with dirty names tbl_Dirty. This table also holds the dollar value for each item. I have a 2nd table that attempts to clean up the names in tbl_Dirty, I’ll call it tbl_Clean.
    The goal is to get the max value of each clean name.

    I figure out how to get the max value of the tbl_Dirty but I am stumped on how to introduce tbl_Clean in the middle of the process.

    This is the sql syntax so far:
    SELECT tbl_Dirty.Dirty_Item, tbl_Dirty.Item_Value
    FROM tbl_Dirty INNER JOIN
    (SELECT tbl_Dirty.Dirty_Item, Max(tbl_Dirty.Item_Value) AS MaxItemValue FROM tbl_Dirty GROUP BY Dirty_item) AS MAX_tbl_Dirty
    ON (tbl_Dirty.Dirty_Item = MAX_tbl_Dirty.Dirty_Item) AND (tbl_Dirty.Item_Value = MAX_tbl_Dirty.MaxItemValue);

    Any help would be greatly appreciated!!!
    Attached Thumbnails Attached Thumbnails Access .jpg  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you want to do with the clean max values once you have them?

    Why don't you build another query for the clean table same as you did for dirty?

    Instead of the query you posted, review http://allenbrowne.com/subquery-01.html#TopN

    SELECT tbl_Dirty.*
    FROM tbl_Dirty WHERE Dirty_Item IN
    (SELECT TOP 1 tbl_Dirty.Dirty_Item FROM tbl_Dirty As Dupe WHERE Dupe.Dirty_Item = tbl_Dirty.Dirty_Item
    ORDER BY Dupe.Dirty_Item, Dupe.Item_Value DESC)
    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
    PHILnTEX is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    6
    Hi June7.
    Thanks for replying and for your code. It's cleaner but I still end up with the same result. I will definitely check out the link you sent.

    What I want to do with the real database I'm using is much more complicated and I am not the source of the data. I made up the above example so to concentrate on solving this puzzle, at least its a puzzle to me, before I attempt more complex code.

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

Similar Threads

  1. Replies: 7
    Last Post: 06-03-2015, 11:42 AM
  2. Replies: 2
    Last Post: 01-29-2015, 03:07 PM
  3. Sort a subset within a query
    By mitchbvi in forum Access
    Replies: 8
    Last Post: 11-25-2014, 11:48 AM
  4. Query: Add 'Subset' counter to records
    By JangLang in forum Access
    Replies: 1
    Last Post: 09-20-2013, 10:51 AM
  5. Print Subset of Records from From
    By ssthornton in forum Programming
    Replies: 6
    Last Post: 05-21-2012, 02:28 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