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!!!