Need Help and advice

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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6250-need-help-and-advice.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
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
--
Sheri

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"LHEMA" wrote in message
news6101016-D136-4BE5-9D08-39A0FF5E561F@microsoft.com...
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
> 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
> --
> Sheri

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250

--
Brendan Reynolds

"LHEMA" wrote in message
news6101016-D136-4BE5-9D08-39A0FF5E561F@microsoft.com...
>I am trying to create a database for our Safety department. In the database
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
>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
> --
> Sheri

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


"Al Camp" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
> 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.
> --
> hth
> Al Camp
> Candia Computer Consulting - Candia NH
> http://home.comcast.net/~cccsolutions
>
>
> "LHEMA" wrote in message
> news6101016-D136-4BE5-9D08-39A0FF5E561F@microsoft.com...
> >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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
> > 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
> > --
> > Sheri
>
>
>

Thanks, will it also give me the total for individual employees and their
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
department
--
Sheri


"Brendan Reynolds" wrote:

> 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
>
> --
> Brendan Reynolds
>
> "LHEMA" wrote in message
> news6101016-D136-4BE5-9D08-39A0FF5E561F@microsoft.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
> >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
> > --
> > Sheri
>
>
>

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.

--
Brendan Reynolds


"LHEMA" wrote in message
news:BDADEE44-1AE9-4A2B-A484-8936FE852547@microsoft.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
> Thanks, will it also give me the total for individual employees and their
> department
> --
> Sheri
>
>
> "Brendan Reynolds" wrote:
>
>> 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
>>
>> --
>> Brendan Reynolds
>>
>> "LHEMA" wrote in message
>> news6101016-D136-4BE5-9D08-39A0FF5E561F@microsoft.com...
>> >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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
>> > have
>> > an idea it would be greatly appreciate. Thanks
>> > --
>> > Sheri
>>
>>
>>

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...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"LHEMA" wrote in message
news:BDADEE44-1AE9-4A2B-A484-8936FE852547@microsoft.com...
> Thanks, will it also give me the total for individual employees and their
> department
> --
> Sheri
>
>
> "Brendan Reynolds" wrote:
>
>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
>> 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
>>
>> --
>> Brendan Reynolds
>>
>> "LHEMA" wrote in message
>> news6101016-D136-4BE5-9D08-39A0FF5E561F@microsoft.com...
>> >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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
>> > have
>> > an idea it would be greatly appreciate. Thanks
>> > --
>> > Sheri
>>
>>
>>

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.

--
Brendan Reynolds

"Al Camp" wrote in message
news:%23DfdRcp3FHA.2364@TK2MSFTNGP12.phx.gbl...
> 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...
> --
> hth
> Al Camp
> Candia Computer Consulting - Candia NH
> http://home.comcast.net/~cccsolutions
>
>
> "LHEMA" wrote in message
> news:BDADEE44-1AE9-4A2B-A484-8936FE852547@microsoft.com...
>> Thanks, will it also give me the total for individual employees and their
>> department
>> --
>> Sheri
>>
>>
>> "Brendan Reynolds" wrote:
>>
>>> 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
>>>
>>> --
>>> Brendan Reynolds
>>>
>>> "LHEMA" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
>>> news6101016-D136-4BE5-9D08-39A0FF5E561F@microsoft.com...
>>> >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
>>> > --
>>> > Sheri
>>>
>>>
>>>
>
>

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" wrote:

>
> Precisely. Given a good, appropriately normalised database design, the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
> grouping and counting will not be a problem. The model at the URL I posted
> provides a basis for such a database design.
>
> --
> Brendan Reynolds
>
> "Al Camp" wrote in message
> news:%23DfdRcp3FHA.2364@TK2MSFTNGP12.phx.gbl...
> > 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...
> > --
> > hth
> > Al Camp
> > Candia Computer Consulting - Candia NH
> > http://home.comcast.net/~cccsolutions
> >
> >
> > "LHEMA" wrote in message
> > news:BDADEE44-1AE9-4A2B-A484-8936FE852547@microsoft.com...
> >> Thanks, will it also give me the total for individual employees and their
> >> department
> >> --
> >> Sheri
> >>
> >>
> >> "Brendan Reynolds" wrote:
> >>
> >>> 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
> >>>
> >>> --
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6250
> >>> Brendan Reynolds
> >>>
> >>> "LHEMA" wrote in message
> >>> news6101016-D136-4BE5-9D08-39A0FF5E561F@microsoft.com...
> >>> >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
> >>> > --
> >>> > Sheri
> >>>
> >>>
> >>>
> >
> >
>
>
>