I have an Access table (Logs) like this:
| pk | modID | relID | DateTime | TxType |
|---|---|---|---|---|
| 1 | 1234 | 22.3 | 10/1/22 04:00 | 1 |
| 2 | 1234 | 23.1 | 10/10/22 06:00 | 1 |
| 3 | 1234 | 23.1 | 10/11/22 07:00 | 2 |
| 4 | 1234 | 23.1 | 10/12/22 08:00 | 3 |
| 5 | 4321 | 22.3 | 10/2/22 06:00 | 7 |
| 6 | 4321 | 23.1 | 10/10/22 06:00 | 1 |
| 7 | 4321 | 23.1 | 10/11/22 07:30 | 3 |
Trying to write a query as part of a function that searches this table:
- for all records matching a given modID and relID (e.g. 1234 and 23.1),
- picks the most recent one (the MAX of DateTime),
- returns the TxType for that record.
However, a bit new to Access and its query structure is vexing me. I landed on this but because I have to include a Total/Aggregate function for TxType I had to either choose Group By (not what I want) or Last (closer, but returns junk results). The SQL for my query is currently:
SELECT Last(Logs.TxType) AS LastOfTxType, Max(Logs.DateTime) AS MaxOfDateTime
FROM Logs
GROUP BY Logs.dmID, Logs.relID
HAVING (((Logs.dmID)=[EnterdmID]) AND ((Logs.relID)=[EnterrelID]));
It returns the TxType field when I pass it the right parameters, but not the correct record - I would like to be rid of the Last() bit but if I remove it Access complains that I don't have it as part of an aggregate function.
Anyone that can point me in the right direction here?
CodePudding user response：
Have you tried
SELECT TOP 1 TxtType
FROM Logs
WHERE (((Logs.dmID)=[EnterdmID]) AND ((Logs.relID)=[EnterrelID]))
ORDER BY DateTime DESC;
That will give you the latest single data row based on your DateTime field and other criteria.