I'm stuck can not get total sum correct

You aren't answering my questions. What is the difference between "total by
.... department" and "grand total for department"?

I have asked what values you would expect to see for these calculations and
have yet to see them in a reply. It might just be me or the questions I ask
but it seems that you could easily modify the following to show what numbers
you actually want and how those numbers were calculated.

Department RD
EmployeeID A 3
EmployeeID C 2

RD Footer 5
---------------------------------------------------------

Department HR
EmployeeID B 1
EmployeeID D 1

FN Footer 2
---------------------------------------------------------

--
Duane Hookom
MS Access MVP


"LHEMA" wrote in message
...
No, what I am printing is the total by employee and department but not the
grand total for department. This is the expression I use
=count([department])
, this works fine. Just can not get the grand total for all the accidents
in
that department. Just looking at the report itself I need a total field to
get the results for the department. I was thinking that I can have it in
that
one field but it looks impossible.
LHEMA


"Duane Hookom" wrote:

You asked "how can I get the total department not the total the employee
receive but for the entire department." We don't have a clue how the
total
accidents a department's employees are involved in differs from the total
for the entire department.

Again, you have stated just part of the issue. You told us a calculation
is
wrong but didn't tell us what the actual calculation should be and how it
is
determined.

If the employees in a department have 5 accidents wouldn't you expect to
see
5 in the department footer? Isn't that what you are printing?
--
Duane Hookom
MS Access MVP


"LHEMA" wrote in message
...
Yes,some employees may have 5 accident with the year so having multiple
is
fine but each employee is assigned to one department only. I want it to
be
unique by employees and department. For instance:

AccidentID EmployeeID Department
1 A RD
2 B HR
3 A RD
4 C RD
5 A RD
6 D HR
7 C RD

There are 7 accidents total which is not a problem, the problem that I
have
in my report is counting the department. right now in my control source
for
department I have =count([Department]) that works but how can I get the
total
department not the total the employee receive but for the entire
department.

Department RD
EmployeeID A 3
EmployeeID C 2

RD Footer 5
---------------------------------------------------------

Department HR
EmployeeID B 1
EmployeeID D 1

FN Footer 2
---------------------------------------------------------



LHEMA


"Duane Hookom" wrote:

Do you possibly have multiple employees per accident as your table
structure
suggests? If so, you may be double or triple counting acciendents with
2
or
3 employees. Also, I expect that if more than one employee is involved
in
an
accident, they may be from different departments. Correct?

Do you want to count unique accidents, unique accidents by department
or
accidents by employee and department. For instance

AccidentID EmployeeID Department
1 A HR
1 B HR
2 A HR
2 C MT
2 D IS
3 D IS
3 E FN

There are only 3 accidents but there are 5 employees involved in
accidents
and 4 departments. How would you expect your report to be displayed
ie:

Department FN
EmployeeID E 1
FN Footer 1
---------------------------------------------------------
Department HR
EmployeeID A 2
B 1
HR Footer 3
---------------------------------------------------------
Department IS
EmployeeID D 2
IS Footer 2
---------------------------------------------------------
Department MT
EmployeeID C 1
MT Footer 1
=Report Footer======================
Total EmployeeAccidents 7

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
1. Sorting and grouping I have
Department
EmployeeID
Department Total - group footer - group leve is 0
Employee Total- group footer - group level is 1
2. I remove the headers because it di not work so my report does not
have
any headers but I have two footers and they are department total and
employee
total
3. Everything works fine expect department. the totals are not
combing
they
are on separate report pages.

Here is my SQl maybe that will solve something because right now I
am
confused
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].[Hospitalized24hrs], [tbl Accidents].[Report prepared
by],
[tbl
Accidents].Position, [tbl Accidents].Telephone, [tbl
Accidents].[Date
of
report], [tbl Accidents].Avoidable, [tbl Accidents].Unavoidable,
[tbl
Total
Accident].[Employee Total], [tbl Total Accident].[Department Total]
FROM ([tbl Accidents] INNER JOIN [tbl Employee Accident] ON [tbl
Accidents].AccidentID = [tbl Employee Accident].AccidentID) LEFT
JOIN
[tbl
Total Accident] ON [tbl Accidents].EmployeeID = [tbl Total
Accident].AccidentID;

--
LHEMA


"Duane Hookom" wrote:

1. if you are sorting and grouping by department then the records
you
typed
previously were wrong since they were not sorted by either
Department
or
Employee.
3. There are many different header and footer sections in a report.
You
didn't tell us which header or footer section your expression
doesn't
work
in.

What are the sorting and grouping levels in your report and which
ones
have
header and footer sections?
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
1. I am sorting and grouping by department
2. employee total works fine using =count([employeeID])
3. Yes and employee only can belong to one department that is
true,
I
tried
using the =count(*) and that does not work I put one in the
header
as
well
and it just count the number of records
4. I enter in another employee that previous had and accidnet and
the
total
for department works per employee but it does not group together
per
department
5. Maybe something is wrong with my relationship this is what I
have: 1
to
1
relationship, accidentID is the PK tbl employee accident is
related
to
tbl
accident
--
LHEMA


"Duane Hookom" wrote:

Am I correct that:
1) You aren't sorting or grouping by Department but you expect
to
count
by
Department?
2) You are showing only employee summaries and not details
3) An employee can belong to only one department
If the above is true, you should be able to sort/group by
Department
with
a
header or footer and then by employee. Add a Count(*) in every
non-page
header and footer that you have created. Remove the counts
and/or
footers/headers that you don't want to display.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
Sorry for your frustration but maybe I am not being clear,
here
is
another
example

Department Employee
Employee
Total
Dept Total
42200-Road Frank
2
2
33000- Sheriff Orrin
1
1
33260- County Jail Ken
3
3
42200- Road Steve
1
3
33000-Sheriff Jeff
1
2
33260-County Jail Alan
3
6

I hope that helps

--
LHEMA


"Duane Hookom" wrote:

Recently I have asked posters to "manually type a few records
into
a
reply".
I'm not sure what it is about this request but it seems very
difficult
for
the OP to comply. This thread is nearly 20 posts in length
and I
believe
could be satisfactorily completed if you would provide enough
fields
and
records with your desired calculations to thoroughly describe
your
needs.

I kinda expect that you want to maybe count unique values. I
don't
think
you
have even stated anything like "I get a count value of 8 when
I
only
have
2
departments".
--
Duane Hookom
MS Access MVP

EmployeeID and AccidentID are not the same. AccidentId is the PK and
EmployeeID is unique for digit number that is assigned to all employees.

3.yes I have ran the query and no the correct information is not their. Its
blank
--4. I tried group and tbl empoyee accident.department and I can not edit
the database.

I believe my problem is with my relationship. I have
tbl employee accident - tbl accident as a 1 to 1 - accidentID/accidentID
tbl accident - total accident as a 1 to 1- accidnetID/employeeID

LHEMA


"AccessVandal via AccessMonster.com" wrote:

LHEMA wrote:
I have =count([department]) and =count([employeeID]) in the footer but its
not returning the correct results for department. What I need is to count

1. I have done a little check on your SQL. Your “[tbl Accidents].EmployeeID
= [tbl Total Accident].AccidentID� , is this correct? EmployeeID is the
same
as AccidentID?!!!!

2. Should it be “[tbl Accidents].AccidentID = [tbl Total Accident].
AccidentID�?

3. Have you run your original SQL/Query grid and is the data correct? For
example,
does the correct number of rows for “Department� is displayed?

4. Try to use the Group By in your query grid (group by “[tbl Employee
Accident].Department�)
and run to see if your results are correct?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200604/1

LHEMA wrote:
EmployeeID and AccidentID are not the same. AccidentId is the PK and
EmployeeID is unique for digit number that is assigned to all employees.

I don't understand this english,

"3.yes I have ran the query and no the correct information is not their. Its
blank"

So, you are saying there are no records? If so, than your query is wrong or
your data input is wrong.

Does not matter, you will learn more later.

"4. I tried group and tbl empoyee accident.department and I can not edit
the database."

Remove "[tbl Total Accident]" and the query related "fields" from your SQL
query,
it's redundant.

Don't keep calculated records, unless required. They are a waste of space.

Try again with the new SQL query and see the results.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200604/1

I did exactly what you said and the result are not correct. Employee total
works great. Department total is not adding departments with multiple
accidents I have 12 accidents and out of the 12 I have 4 accident that are
from the Road department. I need for my report to show look like this

Road Dept= 4
sheriff office = 2
County Jail = 2
detective office = 4

Is this possible - right now it is giving me the total accident per employee
in that department I need at total for that department.

LHEMA


"AccessVandal via AccessMonster.com" wrote:

LHEMA wrote:
EmployeeID and AccidentID are not the same. AccidentId is the PK and
EmployeeID is unique for digit number that is assigned to all employees.

I don't understand this english,

"3.yes I have ran the query and no the correct information is not their. Its
blank"

So, you are saying there are no records? If so, than your query is wrong or
your data input is wrong.

Does not matter, you will learn more later.

"4. I tried group and tbl empoyee accident.department and I can not edit
the database."

Remove "[tbl Total Accident]" and the query related "fields" from your SQL
query,
it's redundant.

Don't keep calculated records, unless required. They are a waste of space.

Try again with the new SQL query and see the results.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200604/1

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].[Hospitalized24hrs], [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
---------------------------------------------------------------
Group Footer Name - Department
Dept Count = 3 EmpID Count = 3
---------------------------------------------------------------

--
Message posted via http://www.accessmonster.com

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


"AccessVandal via AccessMonster.com" wrote:

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].[Hospitalized24hrs], [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
---------------------------------------------------------------
Group Footer Name - Department
Dept Count = 3 EmpID Count = 3
---------------------------------------------------------------

--
Message posted via http://www.accessmonster.com

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


"LHEMA" wrote in message
...
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


"AccessVandal via AccessMonster.com" wrote:

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].[Hospitalized24hrs], [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
---------------------------------------------------------------
Group Footer Name - Department
Dept Count = 3 EmpID Count = 3
---------------------------------------------------------------

--
Message posted via http://www.accessmonster.com

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


"Duane Hookom" wrote:

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


"LHEMA" wrote in message
...
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


"AccessVandal via AccessMonster.com" wrote:

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].[Hospitalized24hrs], [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
---------------------------------------------------------------
Group Footer Name - Department
Dept Count = 3 EmpID Count = 3
---------------------------------------------------------------

--
Message posted via http://www.accessmonster.com

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



"LHEMA" wrote in message
...
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


"Duane Hookom" wrote:

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


"LHEMA" wrote in message
...
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


"AccessVandal via AccessMonster.com" wrote:

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].[Hospitalized24hrs], [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
---------------------------------------------------------------
Group Footer Name - Department
Dept Count = 3 EmpID Count = 3
---------------------------------------------------------------

--
Message posted via http://www.accessmonster.com

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


"Duane Hookom" wrote:

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



"LHEMA" wrote in message
...
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


"Duane Hookom" wrote:

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


"LHEMA" wrote in message
...
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


"AccessVandal via AccessMonster.com" wrote:

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].[Hospitalized24hrs], [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
---------------------------------------------------------------
Group Footer Name - Department
Dept Count = 3 EmpID Count = 3
---------------------------------------------------------------

--
Message posted via http://www.accessmonster.com