Search code examples
sqlms-access

How to select all rows that are related to satisfied condition in ms ACCESS

I have the following data in table VehiclesAccSummary:

enter image description here

I am not sure how to do this but what I want is a query that would return all rows where there where only two car in the accident and it was a head on crash so in both cars PointOfImpact was 'Front' i know in need to do some inner join on the same table but i don' want to join same car with it self. Any idea ?

The end result should be something like this

enter image description here

Solution

  • You can use a subquery to count the number of "Front' records for each AccRef.

       SELECT *
   FROM VehiclesAccSummary INNER JOIN
        (SELECT VehiclesAccSummary.AccRef, Count(VehiclesAccSummary.PointOfImpact) AS CountOfPointOfImpact FROM VehiclesAccSummary GROUP BY VehiclesAccSummary.AccRef, VehiclesAccSummary.PointOfImpact HAVING VehiclesAccSummary.PointOfImpact="Front")  AS FrontCount
   ON VehiclesAccSummary.AccRef = FrontCount.AccRef
   WHERE VehiclesAccSummary.NumCars = 2 AND CountOfPointOfImpact = 2;

    This will limit the records to AccRefs with NumCar = 2 and the count of Front records = 2.

    Edit: Since the same car can be listed in multiple records, we need a new approach. Try this:

    SELECT VehiclesAccSummary.*, Subquery.CountOfPointOfImpact
FROM VehiclesAccSummary LEFT JOIN (SELECT VehiclesAccSummary.AccRef, Count(VehiclesAccSummary.PointOfImpact) AS CountOfPointOfImpact
FROM VehiclesAccSummary
WHERE (((VehiclesAccSummary.PointOfImpact)<>"Front"))
GROUP BY VehiclesAccSummary.AccRef) AS Subquery ON VehiclesAccSummary.AccRef = Subquery.AccRef
WHERE (((Subquery.CountOfPointOfImpact) Is Null));

    Instead of confirming that the count of front accidents is 2, this confirms that the count of non-front accidents is 0.