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 |
#31
|
|||
|
|||
I'm stuck can not get total sum correct
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 "LHEMA" wrote in message ... No, what I am printing is the total by employee and department but not the grand total for department. This is the expression I use =count([department]) , this works fine. Just can not get the grand total for all the accidents in that department. Just looking at the report itself I need a total field to get the results for the department. I was thinking that I can have it in that one field but it looks impossible. LHEMA "Duane Hookom" wrote: You asked "how can I get the total department not the total the employee receive but for the entire department." We don't have a clue how the total accidents a department's employees are involved in differs from the total for the entire department. Again, you have stated just part of the issue. You told us a calculation is wrong but didn't tell us what the actual calculation should be and how it is determined. If the employees in a department have 5 accidents wouldn't you expect to see 5 in the department footer? Isn't that what you are printing? -- Duane Hookom MS Access MVP "LHEMA" wrote in message ... Yes,some employees may have 5 accident with the year so having multiple is fine but each employee is assigned to one department only. I want it to be unique by employees and department. For instance: AccidentID EmployeeID Department 1 A RD 2 B HR 3 A RD 4 C RD 5 A RD 6 D HR 7 C RD There are 7 accidents total which is not a problem, the problem that I have in my report is counting the department. right now in my control source for department I have =count([Department]) that works but how can I get the total department not the total the employee receive but for the entire department. Department RD EmployeeID A 3 EmployeeID C 2 RD Footer 5 --------------------------------------------------------- Department HR EmployeeID B 1 EmployeeID D 1 FN Footer 2 --------------------------------------------------------- LHEMA "Duane Hookom" wrote: Do you possibly have multiple employees per accident as your table structure suggests? If so, you may be double or triple counting acciendents with 2 or 3 employees. Also, I expect that if more than one employee is involved in an accident, they may be from different departments. Correct? Do you want to count unique accidents, unique accidents by department or accidents by employee and department. For instance AccidentID EmployeeID Department 1 A HR 1 B HR 2 A HR 2 C MT 2 D IS 3 D IS 3 E FN There are only 3 accidents but there are 5 employees involved in accidents and 4 departments. How would you expect your report to be displayed ie: Department FN EmployeeID E 1 FN Footer 1 --------------------------------------------------------- Department HR EmployeeID A 2 B 1 HR Footer 3 --------------------------------------------------------- Department IS EmployeeID D 2 IS Footer 2 --------------------------------------------------------- Department MT EmployeeID C 1 MT Footer 1 =Report Footer====================== Total EmployeeAccidents 7 -- Duane Hookom MS Access MVP "LHEMA" wrote in message ... 1. Sorting and grouping I have Department EmployeeID Department Total - group footer - group leve is 0 Employee Total- group footer - group level is 1 2. I remove the headers because it di not work so my report does not have any headers but I have two footers and they are department total and employee total 3. Everything works fine expect department. the totals are not combing they are on separate report pages. Here is my SQl maybe that will solve something because right now I am confused SELECT [tbl Employee Accident].AccidentID, [tbl Employee Accident].EmployeeID, [tbl Employee Accident].Name, [tbl Employee Accident].Address, [tbl Employee Accident].City, [tbl Employee Accident].Zipcode, [tbl Employee Accident].DOB, [tbl Employee Accident].[Date Hired], [tbl Employee Accident].Department, [tbl Employee Accident].Jobtitle, [tbl Accidents].[Location of accident], [tbl Accidents].[Date of accident], [tbl Accidents].[Time of accident], [tbl Accidents].[Time workday began], [tbl Accidents].[Date employer notified], [tbl Accidents].[Did employee work the next day], [tbl Accidents].[Type of Injury], [tbl Accidents].[Part of body affected], [tbl Accidents].Summary, [tbl Accidents].[Treating Physician], [tbl Accidents].[Treating Hospital], [tbl Accidents].[No treatment], [tbl Accidents].[Minor:by employer], [tbl Accidents].[Minor:by clinic/hospital], [tbl Accidents].[Emergency care], [tbl Accidents].[Hospitalized24hrs], [tbl Accidents].[Report prepared by], [tbl Accidents].Position, [tbl Accidents].Telephone, [tbl Accidents].[Date of report], [tbl Accidents].Avoidable, [tbl Accidents].Unavoidable, [tbl Total Accident].[Employee Total], [tbl Total Accident].[Department Total] FROM ([tbl Accidents] INNER JOIN [tbl Employee Accident] ON [tbl Accidents].AccidentID = [tbl Employee Accident].AccidentID) LEFT JOIN [tbl Total Accident] ON [tbl Accidents].EmployeeID = [tbl Total Accident].AccidentID; -- LHEMA "Duane Hookom" wrote: 1. if you are sorting and grouping by department then the records you typed previously were wrong since they were not sorted by either Department or Employee. 3. There are many different header and footer sections in a report. You didn't tell us which header or footer section your expression doesn't work in. What are the sorting and grouping levels in your report and which ones have header and footer sections? -- Duane Hookom MS Access MVP "LHEMA" wrote in message ... 1. I am sorting and grouping by department 2. employee total works fine using =count([employeeID]) 3. Yes and employee only can belong to one department that is true, I tried using the =count(*) and that does not work I put one in the header as well and it just count the number of records 4. I enter in another employee that previous had and accidnet and the total for department works per employee but it does not group together per department 5. Maybe something is wrong with my relationship this is what I have: 1 to 1 relationship, accidentID is the PK tbl employee accident is related to tbl accident -- LHEMA "Duane Hookom" wrote: Am I correct that: 1) You aren't sorting or grouping by Department but you expect to count by Department? 2) You are showing only employee summaries and not details 3) An employee can belong to only one department If the above is true, you should be able to sort/group by Department with a header or footer and then by employee. Add a Count(*) in every non-page header and footer that you have created. Remove the counts and/or footers/headers that you don't want to display. -- Duane Hookom MS Access MVP "LHEMA" wrote in message ... Sorry for your frustration but maybe I am not being clear, here is another example Department Employee Employee Total Dept Total 42200-Road Frank 2 2 33000- Sheriff Orrin 1 1 33260- County Jail Ken 3 3 42200- Road Steve 1 3 33000-Sheriff Jeff 1 2 33260-County Jail Alan 3 6 I hope that helps -- LHEMA "Duane Hookom" wrote: Recently I have asked posters to "manually type a few records into a reply". I'm not sure what it is about this request but it seems very difficult for the OP to comply. This thread is nearly 20 posts in length and I believe could be satisfactorily completed if you would provide enough fields and records with your desired calculations to thoroughly describe your needs. I kinda expect that you want to maybe count unique values. I don't think you have even stated anything like "I get a count value of 8 when I only have 2 departments". -- Duane Hookom MS Access MVP |
#32
|
|||
|
|||
I'm stuck can not get total sum correct
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 "AccessVandal via AccessMonster.com" wrote: LHEMA wrote: I have =count([department]) and =count([employeeID]) in the footer but its not returning the correct results for department. What I need is to count 1. I have done a little check on your SQL. Your “[tbl Accidents].EmployeeID = [tbl Total Accident].AccidentID” , is this correct? EmployeeID is the same as AccidentID?!!!! 2. Should it be “[tbl Accidents].AccidentID = [tbl Total Accident]. AccidentID”? 3. Have you run your original SQL/Query grid and is the data correct? For example, does the correct number of rows for “Department” is displayed? 4. Try to use the Group By in your query grid (group by “[tbl Employee Accident].Department”) and run to see if your results are correct? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200604/1 |
#33
|
|||
|
|||
I'm stuck can not get total sum correct
LHEMA wrote:
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200604/1 |
#34
|
|||
|
|||
I'm stuck can not get total sum correct
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 "AccessVandal via AccessMonster.com" wrote: LHEMA wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200604/1 |
#35
|
|||
|
|||
I'm stuck can not get total sum correct
LHEMA wrote:
I did exactly what you said and the result are not correct. Employee total works great. You dont 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 dont 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 |
#36
|
|||
|
|||
I'm stuck can not get total sum correct
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 |
#37
|
|||
|
|||
I'm stuck can not get total sum correct
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 |
#38
|
|||
|
|||
I'm stuck can not get total sum correct
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 |
#39
|
|||
|
|||
I'm stuck can not get total sum correct
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 |
#40
|
|||
|
|||
I'm stuck can not get total sum correct
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 |
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 |