Question Counting events by year

[nicolaasjan2005]

Hello All:

I'm tracking driver accidents and trying to generate a report for safe driving awards.

I have 2 tables I'm using for this. The first is the driver table which contains the key (employee number) driver names and the date they started work.

The second is the driving record table that is linked to the driver table via the employee number. This table contains three fields - the DriverID (linked); AccidentDate (self-explanatory); and Adjudication (whether the accident was a "preventable accident" or "no preventable accidents.")

I'm trying to create a query that will pull all drivers and will report each driver; the number of accidents (or no accidents) whatever the case may be - but here is where it gets a bit complicated.

Currently I'm only able to extract either the "preventable accidents" or "no preventable accidents".

I'm not able to get them both on one report... and secondly;

Where a driver has an accident in a calendar year, he is only disqualified from receiving an award for that year regardless of the number of accidents he has in that particular year.

Any suggestions?

 

[John Big Booty]

Have you had a play with the totals Button? That's the Sigma (Σ) button that appears in the tool bar when a query is in design view. It should help you get the result you are looking for.

To get both results you will need to have two queries one for each type of accident and then have a third query that brings those two results together.