I'm stuck can not get total sum correct

[Guest]

Thanks AccessVandal via AccessMonster/Duane
It works, I went through my SQL and saw that Total Accident was still there,
I remove it from the query and run my report , everything works great. Thank
you for your patient
--
LHEMA

Like I have state earlier Department is my level 1 and EmployeeID is level 2.
What are raw records( do you mena example of my report) and I have responded
to every answer you have given me. Going back to 4/24 I explained to you like
I have in all the previous records about how the report should be. I am not
understanding what you want. I sorry that this have taken too long, I will
retrack myself to find out what I am doing wrong. Thanks for all your help
Duane
--
LHEMA

Your example report display clearly shows that Department is NOT your level
one sort. If it was your first sorting level the all "42200 Road" records
would be grouped together and would display following the "33000 Sheriff"
department.

You have stated earlier that each employee belongs to a single department.
Can you take the time to type in about 8 "raw" records including the
Employee, Department, and AccidentID. Then type this records as you would
expect them to display in the report. If you have a question about how to
type the display, go back to my reply on 4/24 which you never responded to.
--
Duane Hookom
MS Access MVP

I have both department and employeeID sorted. Department is level one and
EmployeeID is level two. I have a footer for each in department footer I
have
=count([department]) as my control source and in EmployeeID footer I have
=count([employeeID]). Like I have said before Employee works fine and the
department is not giving me the total for the department it is giving me
the
total for that employee in that department. For instance on my report for
employee Frank he had 2 accident in the Road on my report it shows
department
total=2 and employee Total=2 which is correct but when Henry comes along
an
have an accident in the Road department it should change the total of
department .Henry had 3 accident so now for this report it should show
employee total =3 and department total=5 and so forth. THis is happen to
anyone who have had accident through their department....I hope I am clear
this time
Thanks --
LHEMA


:

I don't know how you can total by department when you are not grouping by
department. I think I have asked about or suggested sorting and grouping
levels in messages in this thread. From you records below, you don't seem
to
sorting or grouping by anything.

What are your sorting and grouping levels. I would expect Department to
be
the first level and employee to be the second. You would have a footer
for
each where all you need to do is add text boxes with control sources of:
=Count(*)

--
Duane Hookom
MS Access MVP


Department is an lookup field so the spelling is correct. I check the
query
relationship and I have tbl accident - tbl employee accident link to
accidentID 1 to 1 relationship. As for the employeeID I need too keep
in a
goup footer because I need to know how many accidents did Frank have in
his
Department and the Department total.
My report should look like this:
Employee Frank --------------Department-- 42200 Road
Empoyee total --------2
Department total------2
----------------------------------------------------------------------------------
Employee-------------Henry--------Department-- 42200 Road
Employee Total-------3
Department Total-----5
-------------------------------------------------------------------------------------
Employee-------------ALan------- Department --33000 Sheriff
Employee Total------1
Department Total---1
-------------------------------------------------------------------------
Employee -----------------Orrin -------Department--33000 Sheriff
Employee Total---------4
Department Total------5
---------------------------------------------------------------------------------
Empoyee---------------Jim--------------Department---42200--Road
Employee Total-------------1
Department Total---------6
-------------------------------------------------------------------------------------
Department is a running total I tried change it to over group but it
does
not calculate correctly

I have both Department and EmployeeID group
controlsource = count([department]) =count([employeeId])
Thanks
LHEMA


:

LHEMA wrote:
I did exactly what you said and the result are not correct. Employee
total
works great.

You don't need to post "I want "Road Dept = 4" blah blah blah.

Just give the result of " =Count([Department])" of the Department
Footer.
Need to know what is the value. And how many rows for "Department"
were
displayed from your query.

If your query is correct, than check your Report Sorting and Grouping.
It
appears that you are not grouping for Department but instead
EmployeeID?
(from your original query). You don't need to have Group Footer
"EmployeeID".
Delete "EmployeeID" Footer.

Next:

So, if you did modify your SQL query and the results were not what you
wanted
than you will need to,

1.Check your input data, like misspelled words, "Road Dept" or
"RoadDept"
or
RoodDept" or so on.
2.Check your relationship in the Query Grid, is the "[tbl Employee
Accident].
AccidentID = [tbl Accidents].AccidentID"?

Both Department and EmployeeID are unique, make sure they are input
correctly.


One thing is for sure that your query, if it is correct, the
Department
and
EmployeeID count at the Group Footer will always equals to 4 for the
Road
Dept. ..that means Department = 4 and EmployeeID = 4.(both must be in
Department footer)

Unless, you say Department = 1 and EmployeeID = 4.

And Report Footer is not Group Footer.

Back to your SQL, without knowing the number of rows for Department
"Road
Dept", but you said EmployeeID count from the Report at the Department
Group
Footer was correct.

Show your new SQL again. It should be like this,

SELECT [tbl Employee Accident].AccidentID, [tbl Employee
Accident].EmployeeID, [tbl Employee Accident].Name, [tbl Employee
Accident].Address, [tbl Employee Accident].City, [tbl Employee
Accident].Zipcode, [tbl Employee Accident].DOB, [tbl Employee
Accident].[Date

Hired], [tbl Employee Accident].Department, [tbl Employee
Accident].Jobtitle,

[tbl Accidents].[Location of accident], [tbl Accidents].[Date of
accident],
[tbl Accidents].[Time of accident], [tbl Accidents].[Time workday
began],
[tbl Accidents].[Date employer notified], [tbl Accidents].[Did
employee
work
the next day], [tbl Accidents].[Type of Injury], [tbl Accidents].[Part
of
body affected], [tbl Accidents].Summary, [tbl Accidents].[Treating
Physician], [tbl Accidents].[Treating Hospital], [tbl Accidents].[No
treatment], [tbl Accidents].[Minor:by employer], [tbl
Accidents].[Minor:by
clinic/hospital], [tbl Accidents].[Emergency care], [tbl
Accidents].[Hospitalized>24hrs], [tbl Accidents].[Report prepared by],
[tbl
Accidents].Position, [tbl Accidents].Telephone, [tbl Accidents].[Date
of
report], [tbl Accidents].Avoidable, [tbl Accidents].Unavoidable
FROM ([tbl Accidents] INNER JOIN [tbl Employee Accident] ON [tbl
Accidents].AccidentID = [tbl Employee Accident].AccidentID)
GROUP BY [tbl Employee Accident].Department;

Make ensure "[tbl Total Accident]" and the related "fields" are
remove.

Should be something like this?

Department Name - Road Dept
---------------------Detail----------------------------------
XXXX XXXX EmpID XXXX Dept
xxx xxx Frank xxx Road Dept
xxx xxx Frank xxx Road Dept
xxx xxx John xxx Road Dept

 

[AccessVandal via AccessMonster.com]

Thanks AccessVandal via AccessMonster/Duane
It works, I went through my SQL and saw that Total Accident was still there,

Good, nice to hear from you.

"tbl Total Accident" is a bad joint.