I'm stuck can not get total sum correct

[Guest]

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

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

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

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


:

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

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


:

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

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


:

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

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


:

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


:

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

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


:

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

--
Duane Hookom
MS Access MVP

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.

 

[Duane Hookom]

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

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

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

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


:

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

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


:

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

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


:

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

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


:

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


:

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

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


:

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

--
Duane Hookom
MS Access MVP

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.

 

[Guest]

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

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

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

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

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


:

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

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


:

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

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


:

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

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


:

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


:

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

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


:

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

--
Duane Hookom
MS Access MVP

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.

 

[Duane Hookom]

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

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

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

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


:

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

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


:

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

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


:

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

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


:

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

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


:

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


:

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

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


:

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

--
Duane Hookom
MS Access MVP

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.

 

[Guest]

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].[Hospitalized>24hrs], [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

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

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

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

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


:

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

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


:

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

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


:

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

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


:

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

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


:

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


:

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

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


:

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

--
Duane Hookom
MS Access MVP

"LHEMA" <[email protected]> wrote in

 

[Duane Hookom]

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

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].[Hospitalized>24hrs], [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

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

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


:

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

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


:

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

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


:

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

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


:

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

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


:

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

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


:

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


:

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

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


:

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

--
Duane Hookom
MS Access MVP

"LHEMA" <[email protected]> wrote in

 

[AccessVandal via AccessMonster.com]

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?

 

[Guest]

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

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

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].[Hospitalized>24hrs], [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

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

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


:

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

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


:

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

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


:

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

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


:

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

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

 

[Duane Hookom]

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

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

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

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].[Hospitalized>24hrs], [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


:

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

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


:

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

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


:

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

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


:

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

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


:

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

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

 

[Guest]

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

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

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

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

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].[Hospitalized>24hrs], [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


:

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

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


:

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

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


:

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]

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

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

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

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


:

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

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].[Hospitalized>24hrs], [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


:

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

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


:

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

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


:

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".

 

[Guest]

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

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?

 

[AccessVandal via AccessMonster.com]

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.

 

[Guest]

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

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.

 

[AccessVandal via AccessMonster.com]

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

You don’t need to post “I want “Road Dept = 4” blah blah blah…

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

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

Next:

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

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

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


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

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

And Report Footer is not Group Footer.

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

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

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

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

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

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

Should be something like this?

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

 

[Guest]

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

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

You don’t need to post “I want “Road Dept = 4†blah blah blah…

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

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

Next:

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

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

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


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

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

And Report Footer is not Group Footer.

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

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

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

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

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

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

Should be something like this?

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

 

[Duane Hookom]

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

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

--
Duane Hookom
MS Access MVP

Department is an lookup field so the spelling is correct. I check the
query
relationship and I have tbl accident - tbl employee accident link to
accidentID 1 to 1 relationship. As for the employeeID I need too keep in a
goup footer because I need to know how many accidents did Frank have in
his
Department and the Department total.
My report should look like this:

Employee Frank --------------Department-- 42200 Road
Empoyee total --------2
Department total------2 ----------------------------------------------------------------------------------
Employee-------------Henry--------Department-- 42200 Road
Employee Total-------3
Department Total-----5 -------------------------------------------------------------------------------------
Employee-------------ALan------- Department --33000 Sheriff
Employee Total------1
Department Total---1 -------------------------------------------------------------------------
Employee -----------------Orrin -------Department--33000 Sheriff
Employee Total---------4
Department Total------5 ---------------------------------------------------------------------------------
Empoyee---------------Jim--------------Department---42200--Road
Employee Total-------------1
Department Total---------6

-------------------------------------------------------------------------------------
Department is a running total I tried change it to over group but it does
not calculate correctly

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

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

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

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

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

Next:

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

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

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


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

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

And Report Footer is not Group Footer.

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

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

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

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

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

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

Should be something like this?

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

 

[Guest]

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

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

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

--
Duane Hookom
MS Access MVP

Department is an lookup field so the spelling is correct. I check the
query
relationship and I have tbl accident - tbl employee accident link to
accidentID 1 to 1 relationship. As for the employeeID I need too keep in a
goup footer because I need to know how many accidents did Frank have in
his
Department and the Department total.
My report should look like this:

Employee Frank --------------Department-- 42200 Road
Empoyee total --------2
Department total------2 ----------------------------------------------------------------------------------
Employee-------------Henry--------Department-- 42200 Road
Employee Total-------3
Department Total-----5 -------------------------------------------------------------------------------------
Employee-------------ALan------- Department --33000 Sheriff
Employee Total------1
Department Total---1 -------------------------------------------------------------------------
Employee -----------------Orrin -------Department--33000 Sheriff
Employee Total---------4
Department Total------5 ---------------------------------------------------------------------------------
Empoyee---------------Jim--------------Department---42200--Road
Employee Total-------------1
Department Total---------6

-------------------------------------------------------------------------------------
Department is a running total I tried change it to over group but it does
not calculate correctly

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

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

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

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

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

Next:

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

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

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


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

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

And Report Footer is not Group Footer.

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

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

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

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

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

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

Should be something like this?

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

 

[Duane Hookom]

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

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

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

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

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

--
Duane Hookom
MS Access MVP

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

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


:

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

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

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

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

Next:

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

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

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


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

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

And Report Footer is not Group Footer.

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

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

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

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

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

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

Should be something like this?

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

 

[Guest]

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

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

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

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

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

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

--
Duane Hookom
MS Access MVP


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

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


:

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

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

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

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

Next:

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

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

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


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

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

And Report Footer is not Group Footer.

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

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

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

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

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

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

Should be something like this?

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