I'm sure this is profoundly basic stuff, but for the life of me I can't construct a search term that gets me anywhere so:
Two tables, one has account holders and their ID:
| CID | Name |
|---|---|
| 1 | Will |
| 2 | Sam |
| 3 | Matt |
One has updates to those account holders' information, including the source of the information for the update, the author of the update, and the date the update was made.
| UID | CID | DataOne | DataOne_SRC | Timestamp | Author |
|---|---|---|---|---|---|
| 1 | 1 | D1-1 | InitialPop | 5/26/2022 | Will |
| 3 | 2 | D1-1 | InitialPop | 5/27/2022 | Will |
| 4 | 1 | D1-2 | Subsequent | 5/27/2022 | Will |
| 5 | 2 | D1-2 | Subsequent | 5/28/2022 | Will |
| 6 | 1 | D1-3 | Final | 5/29/2022 | Will |
I want to be able to pull the most current information we have for a given account holder.
SELECT CID, Max(Timestamp) AS MaxOfTimestamp
FROM Updates
WHERE (Updates.DataOne Is Not Null)
GROUP BY Updates.CID;
Will give me exactly what I expect:
| CID | MaxOfTimestamp |
|---|---|
| 1 | 5/29/2022 |
| 2 | 5/28/2022 |
But I also want to know the Update ID (UID) for that record so that I can pull that update's other information elsewhere.
I foolishly assumed that I simply needed to add it to the SELECT but
SELECT UID, CID, Max(Updates.Timestamp) AS MaxOfTimestamp
FROM updates
WHERE (Updates.DataOne IS Not Null)
GROUP BY Updates.CID, Updates.UID;
(Access forces me to include anything not being used in Max() as a 'group by' FNAR)
Yields everything I don't want:
| UID | CID | MaxOfTimestamp |
|---|---|---|
| 1 | 1 | 5/26/2022 |
| 3 | 2 | 5/27/2022 |
| 4 | 1 | 5/27/2022 |
| 5 | 2 | 5/28/2022 |
| 6 | 1 | 5/29/2022 |
After playing around with it, it seems that what it's trying to do is show me the Max(Timestamp) for every possible combination of UID/CID, but since UID is unique that means it's just reproducing the whole Updates table.
Because multiple updates can go in on a single day, how do I return the UID of the records returned by the first query, without it using UID as a criteria?
You'll need to get the Max() UID Grouped by CID, and Date. Then join that back to your original query on the Date and CID field.
This will only work if your ID's are incremental.