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?