I have an access database with records exported from our incident management system. I’m trying to resolve a duplicate counting issue we have due to the way we count incidents. Counting is fine as long as we have a single Operator for each incident. I’m having an issue with the use case where the operation of a vehicle involves two Operators or the other use case where we have an incident where two Operators collide with one another. For any incident an Operator is given a charge, Avoidable or Unavoidable. For an incident for either of the duplicate cases, Operator A can be charged with an Avoidable and Operator B can be charged an Unavoidable. However at the macro level we view this as one incident even though we have two records in the database with charges for both Operators.

Sample data

Incident_Number EmpName IncType Charge_1
1A  Joe     Collision   Avoidable
1B  Tom     Collision   Avoidable
1B  Sue     Collision   Unavoidable
1C  Harry   Collision   Avoidable
1C  John    Collision   Unavoidable
1C  Kathi   Collision   Unavoidable
1D  Larry   Collision   Unavoidable

Sample of how I would like the query results

Incident_Number EmpName IncType Charge_1
1A  Joe     Collision   Avoidable
1B  Tom     Collision   Avoidable
1C  Harry   Collision   Avoidable
1D  Larry   Collision   Unavoidable

In trying to get this to work I tried this test, but it didn't prevent the duplicates. Is our problem just the way we store our data? Should I be trying a DISTINCT on the Charge_1 column?

SELECT *
FROM tst2019 as c1 
WHERE Incident_Number <> 
 (SELECT MAX(Incident_Number) FROM tst2019 as c2 
  WHERE c2.charge_1=c1.charge_1);

1 Answers

0
Gustav On

Gustav, Here's how I used your solution:

SELECT 
    Incident_Number, 
    Date_of_Incident, 
    Mode, 
    Incident_Type, 
    charge_1, 
    First(employee_name) AS empname,
    Division
FROM 
    tst2019
GROUP BY 
    Incident_Number, 
    Date_of_Incident, 
    Mode, 
    Incident_Type,
    Charge_1,
    Division;

I did have one anomaly though, for the Cable Car case where operation of the vehicle requires two employees. For the same Incident Number the query results had a 1 row for Avoidable and 1 row for Unavoidable. Other than that this works exactly the way I would like, so thank you for the help.

Worst case, I could always do a manual check for the Cable Car incidents. Question, would it make sense to run a sql query, specific to the "Avoidables" then do a second append query specific to the "Unavoidables" but having the query omit any result where the incident number already exists in the initial "Avoidable" query results. If this is possible could you provide some guidance on how this could be done? Thanks again for your assistance in solving this duplicates issue.


You must group by Incident_Number:

SELECT 
    Incident_Number,
    First(EmpName) As Operator,
    IncType,
    Min(Avoidable) As Status
FROM 
    tst2019
GROUP BY
    Incident_Number,
    IncType

Note please, that FIRST just picks "a" operator, not necessarily the "first", as nothing in your table defines who/what is first.