I'm stuck can not get total sum correct

the lst post looks like its hard to read I hope this is better:

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

--
LHEMA


"LHEMA" wrote:

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

"LHEMA" wrote in message
...
What sample are you looking for I have tried =count([Department]),
=Sum(IIf([Department]="1",[EmployeeID],0))
=IIf(Count([Department]) =0))
=Sum(IIf([department]= "42200",1,0)
=Sum(nz([Department]))
For all these attempts I get and error in returned.
LHEMA


"Duane Hookom" wrote:

I don't see any sample records or how you would want to display these
with
your calculated totals in your report. Could you take the time to
manually
type a few records into a reply so that we can "see" what you want rather
than attempting to understand your description.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I want is to total each department separately...right now employee
total
is working great using the expression =count([employeeID]) but how can
I
get
the department to do the same. Using this expression
=count([department])
does not count them separately it count them together. Now if I have a
employee that has to accident it counts that correctly but I have other
employees in that department that had an accident and I need to total
them
together. I hope I am making since.

--
LHEMA


"Duane Hookom" wrote:

I'm not sure what you want where at the moment. Your expression:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0)
shouldn't result in anything useable. Where is this expression and
what
are
you attempting to calculate. If [qry Employee Accident] has more than
one
record, who knows which value it might return. Normally the first
argument
in DLookup() is not a true/false type expression.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
Good morning, Duane
They are in a report footer and the employee total does work. I
think I
told
you it did not work. The dept total des not work, I have tried this
expression but it does not give me the numeric it gives me the name
of
the
deparment name:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0) and using
this
expression it shows and error in employee total but ehn I take that
expression out employee total works
--
LHEMA


"Duane Hookom" wrote:

Did you place the new text boxes in a group or report footer
section?
I
can't imagine you would want them to appear anywhere else except
maybe
a
header section.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
I have added 10 more records and the expression is use for
deparment:
=Count([Department]) and employee: =Count([EmployeeID]) and the
results
that i am getting is 1 for everybody
--
LHEMA


"LHEMA" wrote:

ok..I will let you know the results
--
LHEMA


"Duane Hookom" wrote:

Why don't you take the time to type in about 10-12 records to
show
us
exactly what you want in your report. This would save a whole
lot
of
guessing and only take you about a minute.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I need to so count the number of employees that had an
accident
and
each
employee should have a separate report with the total of
accident
they
have
had. And for the department it should count only the
accidents
in
that
department not a all department. I use this expression and I
am
getting a
character for result what I mean is that I am getting the
name
of
the
Dept
and not the numeric number
=nz(DLookUp("Department","[qry Employee Accident]"),0)
=count([employeeID])

I hope I answer your question

--
LHEMA


"Duane Hookom" wrote:

Are you attempting to count the number of employees? What
field
needs to
be
Sum()'d?

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
I have been working on this for a while, I have restarted
my
database it
works fine. Now I need to get total for accident per
employee
and
the
total
for accident per departments. This is what I have done
thus
far, I
have
put a
footer on my report for each grouping employee total and
dept
total.
This
is
the expression that I am using =Sum([Department]) and for
employees
=Sum([EmployeeID]). Using this expression I get a
mismatch
data
type.
Any
help is appreciation.
--
LHEMA

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

"LHEMA" wrote in message
...
What sample are you looking for I have tried =count([Department]),
=Sum(IIf([Department]="1",[EmployeeID],0))
=IIf(Count([Department]) =0))
=Sum(IIf([department]= "42200",1,0)
=Sum(nz([Department]))
For all these attempts I get and error in returned.
LHEMA


"Duane Hookom" wrote:

I don't see any sample records or how you would want to display these
with
your calculated totals in your report. Could you take the time to
manually
type a few records into a reply so that we can "see" what you want
rather
than attempting to understand your description.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I want is to total each department separately...right now
employee
total
is working great using the expression =count([employeeID]) but how
can
I
get
the department to do the same. Using this expression
=count([department])
does not count them separately it count them together. Now if I have
a
employee that has to accident it counts that correctly but I have
other
employees in that department that had an accident and I need to
total
them
together. I hope I am making since.

--
LHEMA


"Duane Hookom" wrote:

I'm not sure what you want where at the moment. Your expression:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0)
shouldn't result in anything useable. Where is this expression and
what
are
you attempting to calculate. If [qry Employee Accident] has more
than
one
record, who knows which value it might return. Normally the first
argument
in DLookup() is not a true/false type expression.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
Good morning, Duane
They are in a report footer and the employee total does work. I
think I
told
you it did not work. The dept total des not work, I have tried
this
expression but it does not give me the numeric it gives me the
name
of
the
deparment name:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0) and
using
this
expression it shows and error in employee total but ehn I take
that
expression out employee total works
--
LHEMA


"Duane Hookom" wrote:

Did you place the new text boxes in a group or report footer
section?
I
can't imagine you would want them to appear anywhere else except
maybe
a
header section.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
I have added 10 more records and the expression is use for
deparment:
=Count([Department]) and employee: =Count([EmployeeID]) and
the
results
that i am getting is 1 for everybody
--
LHEMA


"LHEMA" wrote:

ok..I will let you know the results
--
LHEMA


"Duane Hookom" wrote:

Why don't you take the time to type in about 10-12 records
to
show
us
exactly what you want in your report. This would save a
whole
lot
of
guessing and only take you about a minute.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I need to so count the number of employees that had
an
accident
and
each
employee should have a separate report with the total of
accident
they
have
had. And for the department it should count only the
accidents
in
that
department not a all department. I use this expression
and I
am
getting a
character for result what I mean is that I am getting the
name
of
the
Dept
and not the numeric number
=nz(DLookUp("Department","[qry Employee Accident]"),0)
=count([employeeID])

I hope I answer your question

--
LHEMA


"Duane Hookom" wrote:

Are you attempting to count the number of employees?
What
field
needs to
be
Sum()'d?

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in
message
...
I have been working on this for a while, I have
restarted
my
database it
works fine. Now I need to get total for accident per
employee
and
the
total
for accident per departments. This is what I have done
thus
far, I
have
put a
footer on my report for each grouping employee total
and
dept
total.
This
is
the expression that I am using =Sum([Department]) and
for
employees
=Sum([EmployeeID]). Using this expression I get a
mismatch
data
type.
Any
help is appreciation.
--
LHEMA

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

"LHEMA" wrote in message
...
What sample are you looking for I have tried =count([Department]),
=Sum(IIf([Department]="1",[EmployeeID],0))
=IIf(Count([Department]) =0))
=Sum(IIf([department]= "42200",1,0)
=Sum(nz([Department]))
For all these attempts I get and error in returned.
LHEMA


"Duane Hookom" wrote:

I don't see any sample records or how you would want to display these
with
your calculated totals in your report. Could you take the time to
manually
type a few records into a reply so that we can "see" what you want
rather
than attempting to understand your description.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I want is to total each department separately...right now
employee
total
is working great using the expression =count([employeeID]) but how
can
I
get
the department to do the same. Using this expression
=count([department])
does not count them separately it count them together. Now if I have
a
employee that has to accident it counts that correctly but I have
other
employees in that department that had an accident and I need to
total
them
together. I hope I am making since.

--
LHEMA


"Duane Hookom" wrote:

I'm not sure what you want where at the moment. Your expression:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0)
shouldn't result in anything useable. Where is this expression and
what
are
you attempting to calculate. If [qry Employee Accident] has more
than
one
record, who knows which value it might return. Normally the first
argument
in DLookup() is not a true/false type expression.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
Good morning, Duane
They are in a report footer and the employee total does work. I
think I
told
you it did not work. The dept total des not work, I have tried
this
expression but it does not give me the numeric it gives me the
name
of
the
deparment name:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0) and
using
this
expression it shows and error in employee total but ehn I take
that
expression out employee total works
--
LHEMA


"Duane Hookom" wrote:

Did you place the new text boxes in a group or report footer
section?
I
can't imagine you would want them to appear anywhere else except
maybe
a
header section.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
I have added 10 more records and the expression is use for
deparment:
=Count([Department]) and employee: =Count([EmployeeID]) and
the
results
that i am getting is 1 for everybody
--
LHEMA


"LHEMA" wrote:

ok..I will let you know the results
--
LHEMA


"Duane Hookom" wrote:

Why don't you take the time to type in about 10-12 records
to
show
us
exactly what you want in your report. This would save a
whole
lot
of
guessing and only take you about a minute.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I need to so count the number of employees that had
an
accident
and
each
employee should have a separate report with the total of
accident
they
have
had. And for the department it should count only the
accidents
in
that
department not a all department. I use this expression
and I
am
getting a
character for result what I mean is that I am getting the
name
of
the
Dept
and not the numeric number
=nz(DLookUp("Department","[qry Employee Accident]"),0)
=count([employeeID])

I hope I answer your question

--
LHEMA


"Duane Hookom" wrote:

Are you attempting to count the number of employees?
What
field
needs to
be
Sum()'d?

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in
message
...
I have been working on this for a while, I have
restarted
my
database it
works fine. Now I need to get total for accident per
employee
and
the
total
for accident per departments. This is what I have done
thus
far, I
have
put a
footer on my report for each grouping employee total
and
dept
total.
This
is
the expression that I am using =Sum([Department]) and
for
employees
=Sum([EmployeeID]). Using this expression I get a
mismatch
data
type.
Any
help is appreciation.
--
LHEMA

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

"LHEMA" wrote in message
...
What sample are you looking for I have tried =count([Department]),
=Sum(IIf([Department]="1",[EmployeeID],0))
=IIf(Count([Department]) =0))
=Sum(IIf([department]= "42200",1,0)
=Sum(nz([Department]))
For all these attempts I get and error in returned.
LHEMA


"Duane Hookom" wrote:

I don't see any sample records or how you would want to display
these
with
your calculated totals in your report. Could you take the time to
manually
type a few records into a reply so that we can "see" what you want
rather
than attempting to understand your description.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I want is to total each department separately...right now
employee
total
is working great using the expression =count([employeeID]) but
how
can
I
get
the department to do the same. Using this expression
=count([department])
does not count them separately it count them together. Now if I
have
a
employee that has to accident it counts that correctly but I have
other
employees in that department that had an accident and I need to
total
them
together. I hope I am making since.

--
LHEMA


"Duane Hookom" wrote:

I'm not sure what you want where at the moment. Your expression:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0)
shouldn't result in anything useable. Where is this expression
and
what
are
you attempting to calculate. If [qry Employee Accident] has more
than
one
record, who knows which value it might return. Normally the
first
argument
in DLookup() is not a true/false type expression.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
Good morning, Duane
They are in a report footer and the employee total does work.
I
think I
told
you it did not work. The dept total des not work, I have tried
this
expression but it does not give me the numeric it gives me the
name
of
the
deparment name:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0) and
using
this
expression it shows and error in employee total but ehn I take
that
expression out employee total works
--
LHEMA


"Duane Hookom" wrote:

Did you place the new text boxes in a group or report footer
section?
I
can't imagine you would want them to appear anywhere else
except
maybe
a
header section.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
I have added 10 more records and the expression is use for
deparment:
=Count([Department]) and employee: =Count([EmployeeID]) and
the
results
that i am getting is 1 for everybody
--
LHEMA


"LHEMA" wrote:

ok..I will let you know the results
--
LHEMA


"Duane Hookom" wrote:

Why don't you take the time to type in about 10-12
records
to
show
us
exactly what you want in your report. This would save a
whole
lot
of
guessing and only take you about a minute.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in
message
...
What I need to so count the number of employees that
had
an
accident
and
each
employee should have a separate report with the total
of
accident
they
have
had. And for the department it should count only the
accidents
in
that
department not a all department. I use this expression
and I
am
getting a
character for result what I mean is that I am getting
the
name
of
the
Dept
and not the numeric number
=nz(DLookUp("Department","[qry Employee Accident]"),0)
=count([employeeID])

I hope I answer your question

--
LHEMA


"Duane Hookom" wrote:

Are you attempting to count the number of employees?
What
field
needs to
be
Sum()'d?

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in
message
...
I have been working on this for a while, I have
restarted
my
database it
works fine. Now I need to get total for accident
per
employee
and
the
total
for accident per departments. This is what I have
done
thus
far, I
have
put a
footer on my report for each grouping employee
total
and
dept
total.
This
is
the expression that I am using =Sum([Department])
and
for
employees
=Sum([EmployeeID]). Using this expression I get a
mismatch
data
type.
Any
help is appreciation.
--
LHEMA

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

"LHEMA" wrote in message
...
What sample are you looking for I have tried =count([Department]),
=Sum(IIf([Department]="1",[EmployeeID],0))
=IIf(Count([Department]) =0))
=Sum(IIf([department]= "42200",1,0)
=Sum(nz([Department]))
For all these attempts I get and error in returned.
LHEMA


"Duane Hookom" wrote:

I don't see any sample records or how you would want to display
these
with
your calculated totals in your report. Could you take the time to
manually
type a few records into a reply so that we can "see" what you want
rather
than attempting to understand your description.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I want is to total each department separately...right now
employee
total
is working great using the expression =count([employeeID]) but
how
can
I
get
the department to do the same. Using this expression
=count([department])
does not count them separately it count them together. Now if I
have
a
employee that has to accident it counts that correctly but I have
other
employees in that department that had an accident and I need to
total
them
together. I hope I am making since.

--
LHEMA


"Duane Hookom" wrote:

I'm not sure what you want where at the moment. Your expression:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0)
shouldn't result in anything useable. Where is this expression
and
what
are
you attempting to calculate. If [qry Employee Accident] has more
than
one
record, who knows which value it might return. Normally the
first
argument
in DLookup() is not a true/false type expression.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
Good morning, Duane
They are in a report footer and the employee total does work.
I
think I
told
you it did not work. The dept total des not work, I have tried
this
expression but it does not give me the numeric it gives me the
name
of
the
deparment name:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0) and
using
this
expression it shows and error in employee total but ehn I take
that
expression out employee total works
--
LHEMA


"Duane Hookom" wrote:

Did you place the new text boxes in a group or report footer
section?
I
can't imagine you would want them to appear anywhere else
except
maybe
a
header section.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
I have added 10 more records and the expression is use for
deparment:
=Count([Department]) and employee: =Count([EmployeeID]) and
the
results
that i am getting is 1 for everybody
--
LHEMA


"LHEMA" wrote:

ok..I will let you know the results
--
LHEMA


"Duane Hookom" wrote:

Why don't you take the time to type in about 10-12
records
to
show
us
exactly what you want in your report. This would save a
whole
lot
of
guessing and only take you about a minute.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in
message
...
What I need to so count the number of employees that
had
an
accident
and
each
employee should have a separate report with the total
of
accident
they
have
had. And for the department it should count only the
accidents
in
that
department not a all department. I use this expression
and I
am
getting a
character for result what I mean is that I am getting
the
name
of
the
Dept
and not the numeric number
=nz(DLookUp("Department","[qry Employee Accident]"),0)
=count([employeeID])

I hope I answer your question

--
LHEMA


"Duane Hookom" wrote:

Are you attempting to count the number of employees?
What
field
needs to
be
Sum()'d?

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in

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

"LHEMA" wrote in message
...
What sample are you looking for I have tried
=count([Department]),
=Sum(IIf([Department]="1",[EmployeeID],0))
=IIf(Count([Department]) =0))
=Sum(IIf([department]= "42200",1,0)
=Sum(nz([Department]))
For all these attempts I get and error in returned.
LHEMA


"Duane Hookom" wrote:

I don't see any sample records or how you would want to display
these
with
your calculated totals in your report. Could you take the time
to
manually
type a few records into a reply so that we can "see" what you
want
rather
than attempting to understand your description.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I want is to total each department separately...right now
employee
total
is working great using the expression =count([employeeID]) but
how
can
I
get
the department to do the same. Using this expression
=count([department])
does not count them separately it count them together. Now if
I
have
a
employee that has to accident it counts that correctly but I
have
other
employees in that department that had an accident and I need
to
total
them
together. I hope I am making since.

--
LHEMA


"Duane Hookom" wrote:

I'm not sure what you want where at the moment. Your
expression:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0)
shouldn't result in anything useable. Where is this
expression
and
what
are
you attempting to calculate. If [qry Employee Accident] has
more
than
one
record, who knows which value it might return. Normally the
first
argument
in DLookup() is not a true/false type expression.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
Good morning, Duane
They are in a report footer and the employee total does
work.
I
think I
told
you it did not work. The dept total des not work, I have
tried
this
expression but it does not give me the numeric it gives me
the
name
of
the
deparment name:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0)
and
using
this
expression it shows and error in employee total but ehn I
take
that
expression out employee total works
--
LHEMA


"Duane Hookom" wrote:

Did you place the new text boxes in a group or report
footer
section?
I
can't imagine you would want them to appear anywhere else
except
maybe
a
header section.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
I have added 10 more records and the expression is use
for
deparment:
=Count([Department]) and employee: =Count([EmployeeID])
and
the
results
that i am getting is 1 for everybody
--
LHEMA


"LHEMA" wrote:

ok..I will let you know the results
--
LHEMA


"Duane Hookom" wrote:

Why don't you take the time to type in about 10-12
records
to
show
us
exactly what you want in your report. This would save
a
whole
lot
of
guessing and only take you about a minute.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in
message
...
What I need to so count the number of employees
that
had
an
accident
and
each
employee should have a separate report with the
total
of
accident
they
have
had. And for the department it should count only
the
accidents
in
that
department not a all department. I use this
expression
and I
am
getting a
character for result what I mean is that I am
getting
the
name
of
the
Dept
and not the numeric number
=nz(DLookUp("Department","[qry Employee
Accident]"),0)
=count([employeeID])

I hope I answer your question

--
LHEMA


"Duane Hookom" wrote:

Are you attempting to count the number of
employees?
What
field
needs to
be
Sum()'d?

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in

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

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

"LHEMA" wrote in message
...
What sample are you looking for I have tried
=count([Department]),
=Sum(IIf([Department]="1",[EmployeeID],0))
=IIf(Count([Department]) =0))
=Sum(IIf([department]= "42200",1,0)
=Sum(nz([Department]))
For all these attempts I get and error in returned.
LHEMA


"Duane Hookom" wrote:

I don't see any sample records or how you would want to display
these
with
your calculated totals in your report. Could you take the time
to
manually
type a few records into a reply so that we can "see" what you
want
rather
than attempting to understand your description.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I want is to total each department separately...right now
employee
total
is working great using the expression =count([employeeID]) but
how
can
I
get
the department to do the same. Using this expression
=count([department])
does not count them separately it count them together. Now if
I
have
a
employee that has to accident it counts that correctly but I
have
other
employees in that department that had an accident and I need
to
total
them
together. I hope I am making since.

--
LHEMA


"Duane Hookom" wrote:

I'm not sure what you want where at the moment. Your
expression:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0)
shouldn't result in anything useable. Where is this
expression
and
what
are
you attempting to calculate. If [qry Employee Accident] has
more
than
one
record, who knows which value it might return. Normally the
first
argument
in DLookup() is not a true/false type expression.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
Good morning, Duane
They are in a report footer and the employee total does
work.
I
think I

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

"LHEMA" wrote in message
...
What sample are you looking for I have tried
=count([Department]),
=Sum(IIf([Department]="1",[EmployeeID],0))
=IIf(Count([Department]) =0))
=Sum(IIf([department]= "42200",1,0)
=Sum(nz([Department]))
For all these attempts I get and error in returned.
LHEMA


"Duane Hookom" wrote:

I don't see any sample records or how you would want to
display
these
with
your calculated totals in your report. Could you take the
time
to
manually
type a few records into a reply so that we can "see" what you
want
rather
than attempting to understand your description.
--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
What I want is to total each department separately...right
now
employee
total
is working great using the expression =count([employeeID])
but
how
can
I
get
the department to do the same. Using this expression
=count([department])
does not count them separately it count them together. Now
if
I
have
a
employee that has to accident it counts that correctly but
I
have
other
employees in that department that had an accident and I
need
to
total
them
together. I hope I am making since.

--
LHEMA


"Duane Hookom" wrote:

I'm not sure what you want where at the moment. Your
expression:
=nz(DLookUp("Department=1","[qry Employee Accident]"),0)
shouldn't result in anything useable. Where is this
expression
and
what
are
you attempting to calculate. If [qry Employee Accident]
has
more
than
one
record, who knows which value it might return. Normally
the
first
argument
in DLookup() is not a true/false type expression.

--
Duane Hookom
MS Access MVP

"LHEMA" wrote in message
...
Good morning, Duane
They are in a report footer and the employee total does
work.
I
think I

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