I have three tables:
TableAccident (ID, Date, County)
TableVehicle (ID, UnitNumber, VehicleType,
TablePerson (ID, UnitNumber, PersonNumber, Age)
TableAccident has an ID column that is unique and has a one to many relationship with TableVehicle and TablePerson.
For example, for a crash with 2 vehicles (one person in one vehicle and 3 in the other) the rows would look like the attached image.
I want to create a new table that has the following columns:
MotorcycleCrash ('Yes' if ANY of the vehicles involved are VehicleType='Motorcycle")
TeenCrash ('Yes' if [Age] is between 13 and 19 AND the PersonNumber=1 for any of the vehicles involved in crash -- otherwise 'No')