Home > Net >  Access SQL Query to Count Unique Occurrences of One Field Matching Multiple Parameters/Rows, Some Id
Access SQL Query to Count Unique Occurrences of One Field Matching Multiple Parameters/Rows, Some Id

Time:10-20

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.

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:

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
  • Related