I have the following data in table VehiclesAccSummary:
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
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.