Help with count

[Archie1]

Hi I have a table that records Employees (Name)and their Accidents . I would like a report which shows each employees name, the accident details and then the total amount of accident for each person. Its the count feature that I am struggling with as a newbie

 

[CJ_London]

use a group by query - group by employee name and count the number of accidents.

Any employees that have not had an accident will not appear in the list. If you need zero accidents to be reported as well, create a new query with the employee table left joined to the group by query and use the name from the employee table.

something like

qry1:

SELECT empID, empname, count(*) as NoOfAccidents from tblAccidents group by empID, empname

qry2:

SELECT tblemployees.empname, NoOfAccidents
FROM tblemployees LEFT JOIN qry1 ON tbleemployees.empID=qry1.empID

 

[Archie1]

Thank you. The fields in my table are "Date of Accident" "Accident type" etc. Do I need to add another field for to my table as if I choose count for say "date of accident" -the count works but the field name in the query becomes "count of date of accident"

 

[CJ_London]

No, that is the default sql will use unless you provide an alternative name.

I would lose the spaces in your field names tho' - it will cause you problems at some point in the future. Just because you can, doesn't mean you should. Use the caption property instead if you want spaces.

 

[Archie1]

Ahh thanks a million -that worked!