Total in a Report

  • Thread starter Thread starter Guest
  • Start date Start date
Instead of using sum function I use count and it work fine but in the total
point accessed I need to sum the total by employee . I put in the sum
function and I get the message mismatch in criteria expression...whats wrong
now
--
LHEMA


LHEMA said:
Need Help again...I was having problems with the form I fix that...I just set
the data entry back to no instead of yes. Now when I fix that problem my
report has a invalis controlsource this is what I have =Sum([Total by
Department]) and
-- =Sum([Total by EmployeeID])...any help
LHEMA


LHEMA said:
Thanks Duane it works

now I have another problem about my form...I guess I should report that in
the form sections. Again Thanks for your patient and time
--
LHEMA


Duane Hookom said:
Ok, so you have a department group footer and an employee group footer. In
either of these footers, you can add a text box with a control source like:
=Sum([YourNumericField])
This will absolutely display the sum of your numeric field. You can use the
exact same control sources in report or group headers.

--
Duane Hookom
MS Access MVP
--

Sorry for the confusion...my report is group by deparment and employees
they
are footers. I do have a numeric field for department I have
=count([department]) and employees I have =count([employee]). The only
thing
I need to see is how many accidents an employee had in that department,
example in the Road Dept there was 5 accidents total Paul had 2 and Albert
had 3. So when I pull the report for Albert I need to see 3 accidents by
Albert and 5 by department. And 2 by Paul. I figure this part out, what
the
problem is I can not get it to total any driving points access or points
access without driving. I hope I have not confuse you, any help is
appreciate
--
LHEMA


:

There is rarely a reason why you couldn't add records to a form. I don't
know what this has to do with your report.
Is your report grouped by Department and Employee with group headers
and/or
footers for these sections?

Do you have a numeric field or fields in your report's record source that
you want to sum, count, average,.. in a group or report section?

Do you want to see all the details as well as the totals?

Do you want to see all Deparment totals in the footer of the report or a
single department totals in the department header or footer?

--
Duane Hookom
MS Access MVP
--

Yes there is still a problem...the idea for the database is to keep
track
of
all accidents by employee, department and also the total pointsaccess
as
well
as total driving points. I tried to start over but it still does not
work,
my
results are all null and now I can not add anymore records to my form.
-- any help is appreciate
LHEMA


:

I'm not sure if you still have an issue or a question here?

--
Duane Hookom
MS Access MVP
--

ok...the way to fix something that is corrupt is start over...now
this
is
what I have now:my SQL

SELECT [tbl Employee Accidents].AccidentID, [tbl Employee
Accidents].EmployeeID, [tbl Employee Accidents].Department, [tbl
Employee
Accidents].Jobtitle, [tbl Employee Accidents].DOB, [tbl Employee
Accidents].EmployeeName, [tbl Employee Accidents].EmployeeAddress,
[tbl
Employee Accidents].City, [tbl Employee Accidents].ZipCode, [tbl
Employee
Accidents].Locationofaccident, [tbl Employee
Accidents].DateofInjury,
[tbl
Employee Accidents].TimeofInjury, [tbl Employee
Accidents].TimeWorkdaybegan,
[tbl Employee Accidents].DateEmployeeNotified, [tbl Employee
Accidents].DateHired, [tbl Employee
Accidents].[DidEmployeeWorkNextDay?],
[tbl Employee Accidents].InjuryType, [tbl Employee
Accidents].Partofbodyaffected, [tbl Employee Accidents].Summary,
[tbl
Employee Accidents].NoTreatment, [tbl Employee
Accidents].[Minor:byemployer],
[tbl Employee Accidents].[Minor:byclinic/hospital], [tbl Employee
Accidents].EmergencyCare, [tbl Employee
Accidents].[Hospitalized>24hrs],
[tbl
Employee Accidents].[Treating Physician(name and address)], [tbl
Employee
Accidents].[Treating Hospital(name and address)], [tbl Employee
Accidents].Reportpreparedby, [tbl Employee Accidents].Position, [tbl
Employee
Accidents].Telephone, [tbl Employee Accidents].DateofReport,
Sum([tbl
Employee Accidents].PointsAccess) AS SumOfPointsAccess, Sum([tbl
Employee
Accidents].Drivingpointsaccess) AS SumOfDrivingpointsaccess,
Count([tbl
Total
Accidents].TotEmpAccident) AS CountOfTotEmpAccident, Count([tbl
Total
Accidents].TotDeptAccident) AS CountOfTotDeptAccident, Sum([tbl
Total
Accidents].TotPointsAccess) AS SumOfTotPointsAccess, Sum([tbl Total
Accidents].TotDrivingpointsaccess) AS SumOfTotDrivingpointsaccess
FROM [tbl Employee Accidents] LEFT JOIN [tbl Total Accidents] ON
[tbl
Employee Accidents].AccidentID = [tbl Total
Accidents].TotalAccidents
GROUP BY [tbl Employee Accidents].AccidentID, [tbl Employee
Accidents].EmployeeID, [tbl Employee Accidents].Department, [tbl
Employee
Accidents].Jobtitle, [tbl Employee Accidents].DOB, [tbl Employee
Accidents].EmployeeName, [tbl Employee Accidents].EmployeeAddress,
[tbl
Employee Accidents].City, [tbl Employee Accidents].ZipCode, [tbl
Employee
Accidents].Locationofaccident, [tbl Employee
Accidents].DateofInjury,
[tbl
Employee Accidents].TimeofInjury, [tbl Employee
Accidents].TimeWorkdaybegan,
[tbl Employee Accidents].DateEmployeeNotified, [tbl Employee
Accidents].DateHired, [tbl Employee
Accidents].[DidEmployeeWorkNextDay?],
[tbl Employee Accidents].InjuryType, [tbl Employee
Accidents].Partofbodyaffected, [tbl Employee Accidents].Summary,
[tbl
Employee Accidents].NoTreatment, [tbl Employee
Accidents].[Minor:byemployer],
[tbl Employee Accidents].[Minor:byclinic/hospital], [tbl Employee
Accidents].EmergencyCare, [tbl Employee
Accidents].[Hospitalized>24hrs],
[tbl
Employee Accidents].[Treating Physician(name and address)], [tbl
Employee
Accidents].[Treating Hospital(name and address)], [tbl Employee
Accidents].Reportpreparedby, [tbl Employee Accidents].Position, [tbl
Employee
Accidents].Telephone, [tbl Employee Accidents].DateofReport;
--
LHEMA


:

You have a field named [PointsAccess] in your report's record
source.
Assuming this field is numeric, you can total the PointsAccess in a
Report
or Group Header or Footer with a control source of:
=Sum(PointsAccess)
If that doesn't work, then I assume you:
-The sum adds up to 0
-You have mistakenly placed the text box in the Page Footer
-The field is not numeric
-you have a wonky format property
-the name of the control is wonky
-your report is corrupt

--
Duane Hookom
MS Access MVP
--

I did that an I am getting a 0 instead of the actual total....here
is
my
SQL
if that will help:
SELECT [tbl Employee Accidents].AccidentID, [tbl Employee
Accidents].EmployeeID, [tbl Employee Accidents].Department, [tbl
Employee
Accidents].Jobtitle, [tbl Employee Accidents].DOB, [tbl Employee
Accidents].EmployeeName, [tbl Employee
Accidents].EmployeeAddress,
[tbl
Employee Accidents].City, [tbl Employee Accidents].ZipCode, [tbl
Employee
Accidents].Locationofaccident, [tbl Employee
Accidents].DateofInjury,
[tbl
Employee Accidents].TimeofInjury, [tbl Employee
Accidents].TimeWorkdaybegan,
[tbl Employee Accidents].DateEmployeeNotified, [tbl Employee
Accidents].DateHired, [tbl Employee
Accidents].[DidEmployeeWorkNextDay?],
[tbl Employee Accidents].InjuryType, [tbl Employee
Accidents].Partofbodyaffected, [tbl Employee Accidents].Summary,
[tbl
Employee Accidents].NoTreatment, [tbl Employee
Accidents].[Minor:byemployer],
[tbl Employee Accidents].[Minor:byclinic/hospital], [tbl Employee
Accidents].EmergencyCare, [tbl Employee
Accidents].[Hospitalized>24hrs],
[tbl
Employee Accidents].[Treating Physician(name and address)], [tbl
Employee
Accidents].[Treating Hospital(name and address)], [tbl Employee
Accidents].Reportpreparedby, [tbl Employee Accidents].Position,
[tbl
Employee
Accidents].Telephone, [tbl Employee Accidents].DateofReport, [tbl
Employee
Accidents].PointsAccess, [tbl Employee
Accidents].Drivingpointsaccess,
[tbl
Employee Accidents].TotEmpAccidents, [tbl Employee
Accidents].TotDeptAccidents, [tbl Employee
Accidents].TotDrivingPoints
FROM [tbl Employee Accidents];

--
LHEMA


:

Group your report by employee and place a control in the
employee
group
header or footer with a control source like:
=Sum([PointsAccess])

I have a feeling you aren't telling us something about your
requirements.
--
Duane Hookom
MS Access MVP
--

On the form I have a textbox for points access if they have
receive
points
for any violation, what I need is in the report it would show
the
total
points that Albert receive and not the total points for
everyone.
I
need
each
employee to have there on total.
--
LHEMA


:

I doubt you have any employees with the name "employeeID".
Where do you want users to enter what?
Click to expand...
Click to expand...
Click to expand...
 
When you view the report in datasheet, look at the field you want to sum.
Are the values numeric? Are the values left or right aligned?

--
Duane Hookom
MS Access MVP
--

LHEMA said:
Instead of using sum function I use count and it work fine but in the
total
point accessed I need to sum the total by employee . I put in the sum
function and I get the message mismatch in criteria expression...whats
wrong
now
--
LHEMA


LHEMA said:
Need Help again...I was having problems with the form I fix that...I just
set
the data entry back to no instead of yes. Now when I fix that problem my
report has a invalis controlsource this is what I have =Sum([Total by
Department]) and
-- =Sum([Total by EmployeeID])...any help
LHEMA


LHEMA said:
Thanks Duane it works

now I have another problem about my form...I guess I should report that
in
the form sections. Again Thanks for your patient and time
--
LHEMA


:

Ok, so you have a department group footer and an employee group
footer. In
either of these footers, you can add a text box with a control source
like:
=Sum([YourNumericField])
This will absolutely display the sum of your numeric field. You can
use the
exact same control sources in report or group headers.

--
Duane Hookom
MS Access MVP
--

Sorry for the confusion...my report is group by deparment and
employees
they
are footers. I do have a numeric field for department I have
=count([department]) and employees I have =count([employee]). The
only
thing
I need to see is how many accidents an employee had in that
department,
example in the Road Dept there was 5 accidents total Paul had 2 and
Albert
had 3. So when I pull the report for Albert I need to see 3
accidents by
Albert and 5 by department. And 2 by Paul. I figure this part out,
what
the
problem is I can not get it to total any driving points access or
points
access without driving. I hope I have not confuse you, any help is
appreciate
--
LHEMA


:

There is rarely a reason why you couldn't add records to a form. I
don't
know what this has to do with your report.
Is your report grouped by Department and Employee with group
headers
and/or
footers for these sections?

Do you have a numeric field or fields in your report's record
source that
you want to sum, count, average,.. in a group or report section?

Do you want to see all the details as well as the totals?

Do you want to see all Deparment totals in the footer of the
report or a
single department totals in the department header or footer?

--
Duane Hookom
MS Access MVP
--

Yes there is still a problem...the idea for the database is to
keep
track
of
all accidents by employee, department and also the total
pointsaccess
as
well
as total driving points. I tried to start over but it still does
not
work,
my
results are all null and now I can not add anymore records to my
form.
-- any help is appreciate
LHEMA


:

I'm not sure if you still have an issue or a question here?

--
Duane Hookom
MS Access MVP
--

ok...the way to fix something that is corrupt is start
over...now
this
is
what I have now:my SQL

SELECT [tbl Employee Accidents].AccidentID, [tbl Employee
Accidents].EmployeeID, [tbl Employee Accidents].Department,
[tbl
Employee
Accidents].Jobtitle, [tbl Employee Accidents].DOB, [tbl
Employee
Accidents].EmployeeName, [tbl Employee
Accidents].EmployeeAddress,
[tbl
Employee Accidents].City, [tbl Employee Accidents].ZipCode,
[tbl
Employee
Accidents].Locationofaccident, [tbl Employee
Accidents].DateofInjury,
[tbl
Employee Accidents].TimeofInjury, [tbl Employee
Accidents].TimeWorkdaybegan,
[tbl Employee Accidents].DateEmployeeNotified, [tbl Employee
Accidents].DateHired, [tbl Employee
Accidents].[DidEmployeeWorkNextDay?],
[tbl Employee Accidents].InjuryType, [tbl Employee
Accidents].Partofbodyaffected, [tbl Employee
Accidents].Summary,
[tbl
Employee Accidents].NoTreatment, [tbl Employee
Accidents].[Minor:byemployer],
[tbl Employee Accidents].[Minor:byclinic/hospital], [tbl
Employee
Accidents].EmergencyCare, [tbl Employee
Accidents].[Hospitalized>24hrs],
[tbl
Employee Accidents].[Treating Physician(name and address)],
[tbl
Employee
Accidents].[Treating Hospital(name and address)], [tbl
Employee
Accidents].Reportpreparedby, [tbl Employee
Accidents].Position, [tbl
Employee
Accidents].Telephone, [tbl Employee Accidents].DateofReport,
Sum([tbl
Employee Accidents].PointsAccess) AS SumOfPointsAccess,
Sum([tbl
Employee
Accidents].Drivingpointsaccess) AS SumOfDrivingpointsaccess,
Count([tbl
Total
Accidents].TotEmpAccident) AS CountOfTotEmpAccident,
Count([tbl
Total
Accidents].TotDeptAccident) AS CountOfTotDeptAccident,
Sum([tbl
Total
Accidents].TotPointsAccess) AS SumOfTotPointsAccess, Sum([tbl
Total
Accidents].TotDrivingpointsaccess) AS
SumOfTotDrivingpointsaccess
FROM [tbl Employee Accidents] LEFT JOIN [tbl Total Accidents]
ON
[tbl
Employee Accidents].AccidentID = [tbl Total
Accidents].TotalAccidents
GROUP BY [tbl Employee Accidents].AccidentID, [tbl Employee
Accidents].EmployeeID, [tbl Employee Accidents].Department,
[tbl
Employee
Accidents].Jobtitle, [tbl Employee Accidents].DOB, [tbl
Employee
Accidents].EmployeeName, [tbl Employee
Accidents].EmployeeAddress,
[tbl
Employee Accidents].City, [tbl Employee Accidents].ZipCode,
[tbl
Employee
Accidents].Locationofaccident, [tbl Employee
Accidents].DateofInjury,
[tbl
Employee Accidents].TimeofInjury, [tbl Employee
Accidents].TimeWorkdaybegan,
[tbl Employee Accidents].DateEmployeeNotified, [tbl Employee
Accidents].DateHired, [tbl Employee
Accidents].[DidEmployeeWorkNextDay?],
[tbl Employee Accidents].InjuryType, [tbl Employee
Accidents].Partofbodyaffected, [tbl Employee
Accidents].Summary,
[tbl
Employee Accidents].NoTreatment, [tbl Employee
Accidents].[Minor:byemployer],
[tbl Employee Accidents].[Minor:byclinic/hospital], [tbl
Employee
Accidents].EmergencyCare, [tbl Employee
Accidents].[Hospitalized>24hrs],
[tbl
Employee Accidents].[Treating Physician(name and address)],
[tbl
Employee
Accidents].[Treating Hospital(name and address)], [tbl
Employee
Accidents].Reportpreparedby, [tbl Employee
Accidents].Position, [tbl
Employee
Accidents].Telephone, [tbl Employee Accidents].DateofReport;
--
LHEMA


:

You have a field named [PointsAccess] in your report's
record
source.
Assuming this field is numeric, you can total the
PointsAccess in a
Report
or Group Header or Footer with a control source of:
=Sum(PointsAccess)
If that doesn't work, then I assume you:
-The sum adds up to 0
-You have mistakenly placed the text box in the Page
Footer
-The field is not numeric
-you have a wonky format property
-the name of the control is wonky
-your report is corrupt

--
Duane Hookom
MS Access MVP
--

I did that an I am getting a 0 instead of the actual
total....here
is
my
SQL
if that will help:
SELECT [tbl Employee Accidents].AccidentID, [tbl Employee
Accidents].EmployeeID, [tbl Employee
Accidents].Department, [tbl
Employee
Accidents].Jobtitle, [tbl Employee Accidents].DOB, [tbl
Employee
Accidents].EmployeeName, [tbl Employee
Accidents].EmployeeAddress,
[tbl
Employee Accidents].City, [tbl Employee
Accidents].ZipCode, [tbl
Employee
Accidents].Locationofaccident, [tbl Employee
Accidents].DateofInjury,
[tbl
Employee Accidents].TimeofInjury, [tbl Employee
Accidents].TimeWorkdaybegan,
[tbl Employee Accidents].DateEmployeeNotified, [tbl
Employee
Accidents].DateHired, [tbl Employee
Accidents].[DidEmployeeWorkNextDay?],
[tbl Employee Accidents].InjuryType, [tbl Employee
Accidents].Partofbodyaffected, [tbl Employee
Accidents].Summary,
[tbl
Employee Accidents].NoTreatment, [tbl Employee
Accidents].[Minor:byemployer],
[tbl Employee Accidents].[Minor:byclinic/hospital], [tbl
Employee
Accidents].EmergencyCare, [tbl Employee
Accidents].[Hospitalized>24hrs],
[tbl
Employee Accidents].[Treating Physician(name and
address)], [tbl
Employee
Accidents].[Treating Hospital(name and address)], [tbl
Employee
Accidents].Reportpreparedby, [tbl Employee
Accidents].Position,
[tbl
Employee
Accidents].Telephone, [tbl Employee
Accidents].DateofReport, [tbl
Employee
Accidents].PointsAccess, [tbl Employee
Accidents].Drivingpointsaccess,
[tbl
Employee Accidents].TotEmpAccidents, [tbl Employee
Accidents].TotDeptAccidents, [tbl Employee
Accidents].TotDrivingPoints
FROM [tbl Employee Accidents];

--
LHEMA


:

Group your report by employee and place a control in the
employee
group
header or footer with a control source like:
=Sum([PointsAccess])

I have a feeling you aren't telling us something about
your
requirements.
--
Duane Hookom
MS Access MVP
--

message
On the form I have a textbox for points access if they
have
receive
points
for any violation, what I need is in the report it
would show
the
total
points that Albert receive and not the total points for
everyone.
I
need
each
employee to have there on total.
--
LHEMA


:

I doubt you have any employees with the name
"employeeID".
Where do you want users to enter what?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Text Box Total 2
Filtering a report based upon a subtotal 2
summing a text box 1
Access Running Balance in Access 1
Year to Date 5
SumIf equation in report 2
account statement in access reports 1
Enter Parameter Value - Custom Calculation In Report 2

Back
Top