Need Help and advice

[Guest]

I am trying to create a database for our Safety department. In the database I
need the employee name, address, DOB, Department they are in and Jobtitle,
Date of incident, time of incident, type of injury. place of incidnet,
summary of incident. Now I have and idea of how I want the design to be laid
out but my problem was given to me the other day I need to display somewhere
in the form or report the total of incidents that has happen and how many
types of incident, for example Bob had 3 accident but 2 where lower back pain
how can I get the database to identify the 2 lower back pain and then the
total that Bob has had. I hope I was clear with what I want, if anyone have
an idea it would be greatly appreciate. Thanks

 

[Al Camp]

LHEMA,
Your accidents should be in a subform, related to Employee in a One to
Many relationship. One employee, Many accidents... potentially.

In the subform table, you'll need a field like Injury that allows you to
select from a list of "preset" injury values.
ex. Head Injury, Laceration, Lower Back Pain, Shoulder... etc...

Then, in a report, you can Count the number of injuries against each
Employee, or against the whole Department. The injury values are preset so
that all Lower Back injury entries are exactly identical. Only then can
they be properly counted.

 

[Brendan Reynolds]

Assuming a table 'Accidents' with fields 'AccidentID' (PK), 'EmployeeID' and
'AccidentType', the following query would give the number of each type of
accident per employee ...

SELECT Accidents.EmployeeID, Accidents.AccidentType,
Count(Accidents.AccidentID) AS CountOfAccidentID
FROM Accidents
GROUP BY Accidents.EmployeeID, Accidents.AccidentType;

In a report based on this query, you would set up a group level on
EmployeeID, and in the group footer an expression such as
=Sum(CountOfAccidentID) would give the total number of accidents of all
types per employee. An identical expression in the report footer would give
the total number of accidents for all employees.

http://www.databaseanswers.com/data_models/index.htm

 

[Guest]

Thanks for the information, using subforms you create them with the table, is
it similar to vaule list or the lookup
Sheri

 

[Guest]

Thanks, will it also give me the total for individual employees and their
department

 

[Brendan Reynolds]

The technique can be adapted to slice and dice your data pretty much any way
you want. This is the kind of thing Access was designed for, and it does it
very well.

I forgot to explain why I included that link in my previous post. It's a
link to a web page that provides examples of designs for databases, and the
very first item in the list is an 'Accidents at Work' database. It's not
complete, but it is well worth a look.

 

[Al Camp]

LHEMA,
I think Brendan and I are both just trying to get good table/s and a form
set up, and from there you can easily adapt to those "special needs" you
might have... whether on the form or subsequent reports.
Try getting the tables and form set up and working... just to collect
accident data properly for each employee, and come on back if you still want
help with the "finishing touches."
Good luck...

 

[Brendan Reynolds]

Precisely. Given a good, appropriately normalised database design, the
grouping and counting will not be a problem. The model at the URL I posted
provides a basis for such a database design.

 

[Guest]

Thank you both, you have really help me design my database, I appreciate all
the help. Just to make sure that I am on the right page I have create 2
tables one for Employees and the other for Injuries, do I need another table
to count the total of injuries per employee

Thanks Sheri

 

[Brendan Reynolds]

No. You will likely want additional tables for other things, though, such as
Departments and InjuryTypes. Did you take a look at the URL I posted
(http://www.databaseanswers.com/data_models/index.htm) There is a data model
for an 'Accidents at Work' database there.

BTW: I live and work in Ireland, and here there is a statutory body known as
the Health and Safety Executive, to which serious accidents must be
reported. There are regulations defining what information must be included
in those reports. Do you have a similar agency where you are? If so, don't
forget to call them or visit their web site to find out what information
they may require from you.

 

[Guest]

Yes, I need to split my tales I have too many in one table that does not
relate. I work for the emergency management agency and we have a lot of
accidents within our county and each quarter I need to show how many accident
happen in each department and employee along with the different type and
total them. I am in a rush, so let me go back and anaylze the information you
gave me so that I can get it correct. Again thanks for your help

 

[Guest]

Yes, I need to split my tales I have too many in one table that does not
relate. I work for the emergency management agency and we have a lot of
accidents within our county and each quarter I need to show how many accident
happen in each department and employee along with the different type and
total them. I am in a rush, so let me go back and anaylze the information you
gave me so that I can get it correct. Again thanks for your help

 

[Guest]

Yes, I need to split my tales I have too many in one table that does not
relate. I work for the emergency management agency and we have a lot of
accidents within our county and each quarter I need to show how many accident
happen in each department and employee along with the different type and
total them. I am in a rush, so let me go back and anaylze the information you
gave me so that I can get it correct. Again thanks for your help