Is there a way to return a value from a field in SQL/Access only when the max value is present?

Question

Reworking the question to hopefully avoid any further reduction in reputation -- sorry for being new!

I am working on a relational MS-Access database that I created at work. I am self-taught, so I thoroughly appreciate the help!

The issue I am currently facing is that I need to set each independent transaction (RefID) with 1 customer name. Unfortunately, the systems we use will allow for multiple Customer Names to exist on one RefID. For example (an excerpt from the AR Data Table that I am using):

Ref ID      Customer Name   AR

20164444    Liberty         50
20164444    Liberty         75
20164444    Unicorn         100
20164444    Liberty         2500

As you can see, there are multiple Customer Names on one RefID. I have been spinning my wheels trying to use a sum on the AR Field and retrieving the Max value. Unfortunately, each time I try to pull it into a new table, both the Customer Names come over. An example of the current code being used:

SELECT AR.Refid, max(AR.collectamount) as MCA
FROM AR
GROUP by AR.RefID

This code is currently pulling all of the RefIds and the MCA or Max Collection Amount. However, if I try to bring in Customer Name from my mapping table:

SELECT AR.Refid, Max(AR.collectamount) AS MCA, [Customer Mapping Table].[Parent Name]
FROM AR INNER JOIN [Customer Mapping Table] ON AR.chkrcvdfrom = [Customer Mapping Table].[Child Name - IE]
GROUP BY AR.Refid, [Customer Mapping Table].[Parent Name];

Unfortunately, this code brings my records up to 147,977 and I have established that I should only have 145,395 unique records. This suggests that duplicates exist on RefID's. Please see the following example of a transaction on which I have found a failure:

Ref ID        Customer Name     AR

20161100749   TBC               2517
20161100749   Unicorn           30

At the end of the day, I need all 145,395 RefID's to have only one Customer Name. If there are two customers, I need the Customer Name with the most AR activity. All of the data that is required to make that possible sits within the AR Data File -- a data dump of our AR activity on a per RefID basis. I have an additional table called "Customer Mapping Table", where I have created Parent-Child relationships with some of our customers. For any solution to work I will need to Query the AR Table, pull the max AR Value of each customer, and isolate each RefID to only one Parent Name from the mapping table.

Edit: An attempt at a Minimal, Reproducible Example

The main source of my data is the AR Table -- for the purposes of this exercise, the fields are as follows:

Ref ID        Customer Name     CollectAmount    itemdesc

20161100749   TBC               2517             Ocean_Freight
20161100749   Unicorn           30               Insurance_Premium
20161100749   TBC               30               AMS Fee
20161100749   TBC               100              Pre-Pull
20161100749   TBC               225              Trucker Yard Storage
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          125              Handling Charge
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY

The section above can be copy and pasted as a table within MS-Access and called "AR" -- this will mirror the MS-Access Environment I am working within.

I took Lee's code and implemented it as follows:

SELECT t1.*
FROM AR AS t1 INNER JOIN 
(select t.refid, max(t.collectamount) as MCA
GROUP by AR.RefID)
As t2 ON (t1.collectamount = t2.mca) AND (t1.refid = t2.refid);

When I hit "Run" no error code appears, but the query returns the following:

Ref ID        Customer Name     CollectAmount    itemdesc

20161100749   TBC               2517             Ocean_Freight
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY
50180900002   Westlake          170              LOCAL_CARTAGE_DELIVERY

The end goal for the data would be that each of the two RefID's I provided will be presented in two, unique rows, with only the max AR, Customer Name, and itemdesc fields. I fear this is my fault and Philipxy was correct that I had not provided enough information. I hope this helps clarify the question.

Great question and sorry I didn't specify. All of this data is coming from an AR Data Dump. The largest CollectAmount per RefID is the largest AR activity. — Scott, Jul 08 '19 at 17:25
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, Jul 08 '19 at 18:21
Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. Insert images/links using edit functions. — philipxy, Jul 08 '19 at 18:21
You don't clearly say exactly what you want or exactly what parts you can do or what exactly went wrong. When this is clear it will be a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS Please clarify via edits, not comments. — philipxy, Jul 08 '19 at 18:27
It is extremely difficult to follow your text, and you don't clearly give each example's specification, input, code & desired output. (And we can't cut & paste & run.) Don't use vague words/phrases like "on one record", "set", "retrieving", "pull", "bring in" & "on them". Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. If you give numbers they should be from example data. — philipxy, Jul 08 '19 at 19:45
I literally just gave examples of the tables because I am using tables, not queries. Additionally, I gave all the code I had. Others have had no issue deciphering my text. I can go back and change some of the phrasing to be more in line -- for example, "Record" to "RefID", but otherwise I think your comment is generally unhelpful. — Scott, Jul 08 '19 at 19:54
"Others" guessed. (Indeed the one answer posted begins "If you're looking to obtain [...]".) If you are interested in feedback re your post & my comments I suggest that you show them to someone senior and/or ask at meta.stackoverflow.com. PS [Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) — philipxy, Jul 08 '19 at 20:08

Lee Mac · Accepted Answer · 2019-07-09T19:54:00.310

2

If you're looking to obtain the information associated with the record holding the maximum CollectAmount value across all records with the same RefID, then one possible method is using joins:

select t1.*
from YourTable t1 inner join
(
    select t.refid, max(t.collectamount) as mca
    from YourTable t 
    group by t.refid
) t2
on t1.refid = t2.refid and t1.collectamount = t2.mca

Alternatively, using a correlated subquery:

select t1.* from YourTable t1 where t1.collectamount = 
(select max(t2.collectamount) from YourTable t2 where t1.refid = t2.refid)

Here, change every occurrence of YourTable with the name of your table.

edited Jul 09 '19 at 19:54

answered Jul 08 '19 at 17:19

Lee Mac

15,615
6
32
80

Hi Lee Mac, thank you so much for your reply to my problem! With the following code: `SELECT AR.RefID, Max(AR.CollectAmount) AS MCA, AR.chkrcvdfrom FROM AR GROUP BY AR.RefID, AR.chkrcvdfrom;` I was able to get the unique max for each RefID - but I have been unable to get the other portion of your code to work for me. All of the data comes from the AR table except for the parent name which comes from a Customer Mapping table relationship with the AR file. As an example, here is the design view of what I am trying to articulate. https://imgur.com/a/xKYtSts – Scott Jul 08 '19 at 17:41
As an update, I was able to get unique customer names through the following code: `SELECT AR.RefID, Max(AR.CollectAmount) AS MCA, Last(AR.chkrcvdfrom) AS LastOfchkrcvdfrom FROM AR GROUP BY AR.RefID ; ` This is slightly different than what you were talking about, but it seems to work? – Scott Jul 08 '19 at 17:58
My previous update was incorrect, although some of the records are correct some are still pulling incorrectly. I am trying to troubleshoot through it, but it feels like we are a lot closer. – Scott Jul 08 '19 at 18:08
2

@Scott You're welcome. You should be able to simply copy the first SQL block, find/replace `YourTable` with `AR`, and copy it into the SQL pane for a query - does this not work for you? If not, what error are you receiving? Don't use `Last` or `First` when trying to return fields associated with aggregated records, they will not reliably return the fields for the record corresponding to the `max` or `min` (or other aggregation) of a particular field, but will merely be dependent on the order in which the query encounters the records. – Lee Mac Jul 08 '19 at 22:10
1

(@Scott &) LeeMac There's no benefit in joining the subquery with the subquery's FROM table when we only keep id & max & columns single-valued per id, because we could have just used the subquery code with SELECT of the columns & max we need. We join the subquery with its FROM table to get *other* FROM table columns in rows with the max ids. But the asker only keeps id, max & parent & they seem to expect 1 child per id & 1 parent per child. The problem might involve there not being 1. But there's no clear specification & [mre] so the question is not clear so we don't know. – philipxy Jul 08 '19 at 23:15
@leemac -- I thoroughly appreciate your patience and assistance. I spent some time reviewing philipxy's comments/links and updated the original question to assist with providing a minimal reproducible example. – Scott Jul 09 '19 at 16:55
@Scott You implemented my code incorrectly - please review my code and my instructions in my earlier comment - you only need to find/replace `YourTable` with `AR`, none of the other code should change. – Lee Mac Jul 09 '19 at 18:46
@LeeMac Hey Lee, I implemented the code as you suggested with only doing a find and replace. This results in too many instances of RefID's being returned 154,031 where I would expect 145,395. I think this is due to the phenomenon I attempted to show in my example above. As you can see, the RefID for Westlake is repeated 11 times because all of the collectamounts are equal at $170 AR. I attempted to set the values to uniques only, but that didn't solve the issue. – Scott Jul 09 '19 at 19:35
@Scott You'll need to decide how you want to handle such records for which the maximum `collectamount` is equal, but other fields differ. In your given example, using `select distinct` should remove the duplicates, but only if all displayed field are equal. – Lee Mac Jul 09 '19 at 20:00
@LeeMac, probably a dumb solution, but I just made a query to pull only the fields I want and then applied your code to that Query instead of my source AR data. It appears to be working! Thank you! – Scott Jul 09 '19 at 20:10
@Scott That would work; alternatively, you could change `select t1.*` to `select t1.refid, t1.[Customer Name], t1.CollectAmount, t1.itemdesc` in my code. Glad you were able to work to a solution! – Lee Mac Jul 09 '19 at 20:31

Is there a way to return a value from a field in SQL/Access only when the max value is present?

1 Answers1