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.
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);