records for the year

[jackrobyn1]

I have a table/ form that i record employee accidents in, with fields - Ref
No, Name, Accident type, Date details recieved. The accident type field
choices are:- B = blameworthy, N = No blame, P = Part blame, so with a drop
down you select B, N or P. What i need is a query that i can use to give me
the number of blameworthy or part blame accidents per employee, so i can use
this in a weekly report. The report will have the weeks accidents on it but i
also want to include a sub report or new field or something that also shows
the count of blameworthy/part blame accident for each record shown for the
past year from the date of the most recently recieved accident details.
something like this:-
Ref Name Type Date recieved Number in last year
Acc01 J Smith B 01/08/2008 3
Acc02 F Bloggs p 02/08/2008 1
Acc03 P Jones B 02/08/2008 4

I would be grateful for any help

 

[louisjohnphillips]

I have a table/ form that i record employee accidents in, with fields - Ref
No, Name, Accident type, Date details recieved. The accident type field
choices are:- B = blameworthy, N = No blame, P = Part blame, so with a drop
down you select B, N or P. What i need is a query that i can use to give me
the number of blameworthy or part blame accidents per employee, so i can use
this in a weekly report. The report will have the weeks accidents on it but i
also want to include a sub report or new field or something that also shows
the count of blameworthy/part blame accident for each record shown for the
past year from the date of the most recently recieved accident details.
something like this:-
Ref        Name       Type       Date recieved         Number in last year
Acc01    J Smith        B           01/08/2008           3
Acc02    F Bloggs       p           02/08/2008           1
Acc03    P Jones        B           02/08/2008           4

I would be grateful for any help

Can a column [EmployeeID] be subsitituted for the [Name] column?

If so, consider:

SELECT A.EmployeeID, A.Ref, A.[Date Received], B.BlameCount
from EmployeeAccidents as A
inner join
( SELECT EmployeeID, count(*) as BlameCount
from EmployeeAccidents
WHERE [Date Received] > Date() - 365
AND Type in ( 'B','P' )
GROUP BY EmployeeID ) as B
ON A.EmployeeID = B.EmployeeID
WHERE A.Type in ( 'B', 'P' )
AND A.[Date Received] > Date() - 7 ;

This reports each accident of type 'B' or 'P' that an employee had
during the previous seven days. It also counts accidents the employee
had within the last 365 days--also of type 'B' or 'P'. Of course, it
omits employees who had no accidents within the last seven days.

Does this meet your requirements?

 

[jackrobyn1]

As a complete beginner to access, i'm not sure. Where do i put this?
Sorry if this seems dumb

I have a table/ form that i record employee accidents in, with fields - Ref
No, Name, Accident type, Date details recieved. The accident type field
choices are:- B = blameworthy, N = No blame, P = Part blame, so with a drop
down you select B, N or P. What i need is a query that i can use to give me
the number of blameworthy or part blame accidents per employee, so i can use
this in a weekly report. The report will have the weeks accidents on it but i
also want to include a sub report or new field or something that also shows
the count of blameworthy/part blame accident for each record shown for the
past year from the date of the most recently recieved accident details.
something like this:-
Ref Name Type Date recieved Number in last year
Acc01 J Smith B 01/08/2008 3
Acc02 F Bloggs p 02/08/2008 1
Acc03 P Jones B 02/08/2008 4

I would be grateful for any help

Can a column [EmployeeID] be subsitituted for the [Name] column?

If so, consider:

SELECT A.EmployeeID, A.Ref, A.[Date Received], B.BlameCount
from EmployeeAccidents as A
inner join
( SELECT EmployeeID, count(*) as BlameCount
from EmployeeAccidents
WHERE [Date Received] > Date() - 365
AND Type in ( 'B','P' )
GROUP BY EmployeeID ) as B
ON A.EmployeeID = B.EmployeeID
WHERE A.Type in ( 'B', 'P' )
AND A.[Date Received] > Date() - 7 ;

This reports each accident of type 'B' or 'P' that an employee had
during the previous seven days. It also counts accidents the employee
had within the last 365 days--also of type 'B' or 'P'. Of course, it
omits employees who had no accidents within the last seven days.

Does this meet your requirements?

 

[Lord Kelvan]

when you make a query click on the view button in the menu bar and
then click on sql view delete everything that is there and then past
it then run the query and see if it works