This is enough to make my head swim.
It would appear you are trying to get a list of all accidents combined
with
a total number of accidents.
--------------------------------------------------individual accidents
[tbl EmpAccidents].[tbl EmpAccidentsID],
[tbl EmpAccidents].EmployeeID,
[tbl EmpAccidents].FirstName,
[tbl EmpAccidents].LastName,
[tbl EmpAccidents].JobTitle,
[tbl EmpAccidents].Address,
[tbl EmpAccidents].City,
[tbl EmpAccidents].Zipcode,
[tbl EmpAccidents].DOB,
[tbl Accidents].AccidentDate,
[tbl Accidents].AccidentType,
[tbl Accidents].[Date of Injury],
[tbl Accidents].[Time of Injury],
[tbl Accidents].[Location of accident],
[tbl Accidents].[Time work began],
[tbl Accidents].[Date employer notified],
[tbl Accidents].Summary,
[tbl Accidents].[Physician/Hospital],
[tbl Accidents].[Physician/Hospital Address],
[tbl Accidents].[No treatment],
[tbl Accidents].[Minor:by employee],
[tbl Accidents].[Minor:clinic/hospital],
[tbl Accidents].[Emergency care],
[tbl Accidents].[Hospitalized>24 hrs],
[tbl Accidents].PointsAssigned,
[tbl Accidents].[Report prepared by],
[tbl Accidents].Position,
[tbl Accidents].Telephone,
[tbl Accidents].[Date of Report],
[tbl Body Parts].[Part of body affected],
[tbl CoDepartments].DepartmentName,
'----------------------------------------accident summary
[tbl Total Accidents].TotalbyEmp,
[tbl Total Accidents].TotalbyDept,
[tbl Total Accidents].TotDrivingPoints,
[tbl Total Accidents].Total
'---------------------------------------------------
If I understand your comments correctly, the TotalbyEmp, etc. are not
correct.
So how are you generating the [tblTotalAccidents] and what is the key
field
for it?
It appears to contain rows like
[EmployeeTotalAccidents] [DepartmentTotalAccidents] and
[TotalAccidents]
which logically would not go together.
I would not use a table but a set of queries.
qryTotalbyEmp :
SELECT [EmployeeID], Count([EmpAccidentID) AS TotEmpAccidents
FROM [EmpAccidents]
GROUP BY [EmployeeID];
This will provide a set of data like
EmployeeID TotEmpAccidents
1 3
2 1
3 0
4 2
--------------
a similar query is required for getting the number of accidents for a
department
Select [DepartmentID], Count([EmpAccidentID]) as TotDeptAccidents
From [EmpAccidents]
Group By [DepartmentID];
Then to get the final total of all accidents
Select Count[EmpAccidentID] as TotdepTaccidents
From [EmpAccidents];
(this should return only one number, the total count of accidents.
Ed Warren.
LHEMA said:
Here's my SQL:
SELECT [tbl EmpAccidents].[tbl EmpAccidentsID], [tbl
EmpAccidents].EmployeeID, [tbl EmpAccidents].FirstName, [tbl
EmpAccidents].LastName, [tbl EmpAccidents].JobTitle, [tbl
EmpAccidents].Address, [tbl EmpAccidents].City, [tbl
EmpAccidents].Zipcode,
[tbl EmpAccidents].DOB, [tbl Accidents].AccidentDate, [tbl
Accidents].AccidentType, [tbl Accidents].[Date of Injury], [tbl
Accidents].[Time of Injury], [tbl Accidents].[Location of accident],
[tbl
Accidents].[Time work began], [tbl Accidents].[Date employer notified],
[tbl
Accidents].Summary, [tbl Accidents].[Physician/Hospital], [tbl
Accidents].[Physician/Hospital Address], [tbl Accidents].[No
treatment],
[tbl
Accidents].[Minor:by employee], [tbl
Accidents].[Minor:clinic/hospital],
[tbl
Accidents].[Emergency care], [tbl Accidents].[Hospitalized>24 hrs],
[tbl
Accidents].PointsAssigned, [tbl Accidents].[Report prepared by], [tbl
Accidents].Position, [tbl Accidents].Telephone, [tbl Accidents].[Date
of
Report], [tbl Body Parts].[Part of body affected], [tbl
CoDepartments].DepartmentName, [tbl Total Accidents].TotalbyEmp, [tbl
Total
Accidents].TotalbyDept, [tbl Total Accidents].TotDrivingPoints, [tbl
Total
Accidents].Total
FROM ([tbl EmpAccidents] INNER JOIN (([tbl Body Parts] INNER JOIN [tbl
CoDepartments] ON [tbl Body Parts].PartofBodyID = [tbl
CoDepartments].DepartmentID) INNER JOIN [tbl Accidents] ON [tbl
CoDepartments].DepartmentID = [tbl Accidents].AccidentID) ON [tbl
EmpAccidents].[tbl EmpAccidentsID] = [tbl Accidents].AccidentID) INNER
JOIN
[tbl Total Accidents] ON [tbl Accidents].AccidentID = [tbl Total
Accidents].AccidentID;
--
LHEMA
:
It would help if you would select View "sql" in your query then , copy
the
sql code to the clipboard, then paste it here so we could actually see
your
query code. Otherwise, we are not able to help and would probably
hinder.
Ed Warren.
Thank you Ed....everything works great except the total fields
I need to total all accidents (separately)by employee, department,
driving
points and a grand total of all accidents. So far I create a qry to
start,
in
the qry I use the count function to count the total by department,
employee
and points. I use the (*) to count total accidents but nothing works
can
you
help me
--
LHEMA
:
??
There are lots of 'what I did wrong' ways to go I don't have a
clue
what
you have or have not done so here are some thoughts.
Now you have the tables built, you need to build forms to put data
into
the
tables.
You will need a form for each table.
One to enter employee data
one to enter the types of accidents
one to enter the type of injury
one to enter the departments
etc.
After you get through building forms you will need to build some
reports
to
provide the final output of the system.
Remember:
Tables -- hold data (don't let the user near them)
Queries -- retrieve\maintain data in the form\format you want and
do
the
calculations required
Forms -- Enter Data
Reports -- 'report data'
Modules -- contain the buisness logic needed in VBA code
Then you will want a form based on the Employee table with a
subform
(based
on the Accidents, for entering the accidents that employee has been
involved
in). That SubForm should be linked to the employee table using the
EmployeeID field as (child,parent). This SubForm should contain a
dropdown
list box to all selection of the type of injury from the injury
table,
plus
other fields as required to fill in the required data fields.
Likewise you in the employee form you will have a dropdown box to
select
the
department from the department table.
And around, around, we go....
Ed Warren
Thanks I have them all link correctly. I create a table for Total
accidents
and I link that table to employees but as i enter my information
on
the
form
all the tables are blank except for employees, what did I do
wrong
--
LHEMA
:
????
Each AccidentType can have 0 to many Accidents
Therefore::
AccidentType should be linked 1:M to Accidents (each Accident
can
be
of 1
and only 1 type, but each accidentType can be linked to many
accidents).
So the link should be AccidentType (AccidentTypeID) 1:M
Accidents.
(AccidentTypeID, foreign key)
Ed Warren.
I'm sorry Ed, I corrected my mistake I did not link them
correctly.
Now
the
only problem i see now is with the accident type I have that
as a
1
to
1
relation
--
LHEMA
:
Hi Ed
I link everything like you said and I am getting 1 to1 on all
except
dept
to
employee(1 to many) what am I doing wrong, also do I need a
table
for
the
total of accidents
by emp, depart, points, grandtotal
--
LHEMA
:
Not only Ok that is the way it should be. Note, that is the
way I
have
suggested they be configured.
Ed Warren.
Thanks Ed, I'll let you know the outcome
Oh one more thing I created the Bodyparts and department
as
a
combox
(separate tables) is that ok
--
LHEMA
:
