Access SQL Query to Count Unique Occurrences of One Field Matching Multiple Parameters/Rows, Some Identical

Struggling with ms-access’s flavor of SQL queries still, though I’ve made some progress (thanks to y’all). I have an event log table like this:

Logs Table

logID (auto#)	modID (str)	relID (str)	DateTime (date)	TxType (short)
1	1234	22.3	10/1/22 0800	6
2	1234	22.3	10/1/22 0900	7
3	1234	22.3	10/1/22 1000	13
4	1234	22.3	10/1/22 1100	15
5	4321	22.3	10/1/22 0830	1
6	4321	22.3	10/1/22 0930	13
7	4321	22.3	10/1/22 1030	15
8	4321	22.3	10/1/22 1130	13
9	1234	23.1	11/1/22 0800	1
10	1234	23.1	11/1/22 0900	15
11	1234	23.1	11/1/22 1000	13
12	1234	23.1	11/1/22 1100	15
13	4321	23.1	11/1/22 0830	13
14	4321	23.1	11/1/22 0930	7
15	4321	23.1	11/1/22 1030	13
16	4321	23.1	11/1/22 1130	15

What I need to do is:

• filter the table by relID, then
• count the number of modID’s that have a 15 txType as the last/most recent chronological event in their rows.

So ideally I’d filter e.g. by relID=23.1 and get these results (but not logID # 10 for example) and then count them:

logID (auto#)	modID (str)	relID (str)	DateTime (date)	TxType (short)
12	1234	23.1	11/1/22 1100	15
16	4321	23.1	11/1/22 1130	15

As part of another function I have been able to count any modID’s having a single txType successfully using

SELECT COUNT(*)
FROM (
SELECT DISTINCT Logs.modID, Logs.relID
FROM Logs
WHERE ((Logs.relID='23.1') AND ((Logs.TxType=13)))
);

Another stackoverflow user (exception – thanks!) showed me how to get the last event type for a given modID, relID combination using

SELECT TOP 1 TxType
FROM Logs
WHERE (((Logs.modID=[EnterModID])) AND ((Logs.relID=[EnterRelID])))
ORDER BY DateTime DESC;

But I’m having trouble combining these two. I know I can combine COUNT and GROUP BY but Access treats GROUP BY very particularly, and I’m not sure how to use SELECT TOP to get the latest events for each modID rather than just the latest events in the table, period.

Answer

This should give you the logID from the row with the latest DateTime for each combination of modIDand your target relID:

PARAMETERS which_relID Text(255);
SELECT DISTINCT
    (
        SELECT TOP 1 logID
        FROM Logs
        WHERE modID=l.modID AND relID=l.relID
        ORDER BY [DateTime] DESC
    ) AS latest_modID
FROM Logs AS l
WHERE l.relID=[which_relID]

Use it as a subquery which you INNER JOIN to your Logs table. Note the subquery evaluates rows regardless of TxType. So have the parent query select only rows whose TxType = 15

PARAMETERS which_relID Text(255);
SELECT l2.*
FROM
    Logs AS l2
    INNER JOIN
    (
        SELECT DISTINCT
            (
                SELECT TOP 1 logID
                FROM Logs
                WHERE modID=l.modID AND relID=l.relID
                ORDER BY [DateTime] DESC
            ) AS latest_modID
        FROM Logs AS l
        WHERE l.relID=[which_relID]
    ) AS sub
    ON l2.logID=sub.latest_modID
WHERE l2.TxType=15;

Note I moved the PARAMETERS clause into the parent query. But you can eliminate it altogether if you believe it’s causing trouble.

DateTime is a reserved word. I enclosed it in square brackets to ensure Access understands we mean the name of an object.

Using your sample data, I get these 2 rows when I supply 23.1 for the query parameter:

logID	modID	relID	DateTime	TxType
12	1234	23.1	11/1/2022 11:00:00 AM	15
16	4321	23.1	11/1/2022 11:30:00 AM	15

I get a single row with 22.3 for the parameter:

logID	modID	relID	DateTime	TxType
4	1234	22.3	10/1/2022 11:00:00 AM	15