Skip to content
Advertisement

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

Advertisement

Answer

You can use a subquery to count the number of “Front’ records for each AccRef.

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:

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

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement