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. |
|
|
Thread Tools | Display Modes |
#41
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 04: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 02:37 PM |
Grand Total Problem | PW11111 | Running & Setting Up Queries | 2 | March 11th, 2005 05:44 PM |