A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

I'm stuck can not get total sum correct



 
 
Thread Tools Display Modes
  #41  
Old April 27th, 2006, 09:05 PM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default I'm stuck can not get total sum correct

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


"LHEMA" wrote:

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


"Duane Hookom" wrote:

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

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



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


"Duane Hookom" wrote:

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

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

--
Duane Hookom
MS Access MVP


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

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


"AccessVandal via AccessMonster.com" wrote:

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

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

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

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

Next:

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

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

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


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

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

And Report Footer is not Group Footer.

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

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

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

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

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

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

Should be something like this?

Department Name - Road Dept
---------------------Detail----------------------------------
XXXX XXXX EmpID XXXX Dept
xxx xxx Frank xxx Road Dept
xxx xxx Frank xxx Road Dept
xxx xxx John xxx Road Dept
---------------------------------------------------------------
Group Footer Name - Department
Dept Count = 3 EmpID Count = 3
---------------------------------------------------------------

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







  #42  
Old April 28th, 2006, 04:37 AM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default I'm stuck can not get total sum correct

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


Good, nice to hear from you.

"tbl Total Accident" is a bad joint.

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




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating Business Hours Between 2 Dates tanya216 General Discussion 2 April 11th, 2006 03:22 PM
not correct running total in group cmk Setting Up & Running Reports 0 November 10th, 2005 05:30 PM
More of a math question,but I am stuck getting a correct figure. Michael Using Forms 6 August 2nd, 2005 02:33 PM
help needed jkendrick75 Running & Setting Up Queries 19 March 17th, 2005 03:37 PM
Grand Total Problem PW11111 Running & Setting Up Queries 2 March 11th, 2005 06:44 PM


All times are GMT +1. The time now is 06:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.