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

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:

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.

CodePudding user response：

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:

I get a single row with 22.3 for the parameter: