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 |
#21
|
|||
|
|||
I'm stuck can not get total sum correct
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 "LHEMA" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "LHEMA" wrote: ok..I will let you know the results -- LHEMA "Duane Hookom" wrote: 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 "LHEMA" wrote in 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 "Duane Hookom" wrote: Are you attempting to count the number of employees? What field needs to be Sum()'d? -- Duane Hookom MS Access MVP "LHEMA" wrote in 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. -- LHEMA |
#22
|
|||
|
|||
I'm stuck can not get total sum correct
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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "LHEMA" wrote: ok..I will let you know the results -- LHEMA "Duane Hookom" wrote: 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 "LHEMA" wrote in 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 "Duane Hookom" wrote: Are you attempting to count the number of employees? What field needs to be Sum()'d? -- Duane Hookom MS Access MVP "LHEMA" wrote in 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. -- LHEMA |
#23
|
|||
|
|||
I'm stuck can not get total sum correct
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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "LHEMA" wrote: ok..I will let you know the results -- LHEMA "Duane Hookom" wrote: 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 "LHEMA" wrote in 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 "Duane Hookom" wrote: Are you attempting to count the number of employees? What field needs to be Sum()'d? -- Duane Hookom MS Access MVP "LHEMA" wrote in 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. -- LHEMA |
#24
|
|||
|
|||
I'm stuck can not get total sum correct
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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "LHEMA" wrote: ok..I will let you know the results -- LHEMA "Duane Hookom" wrote: 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 "LHEMA" wrote in 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 "Duane Hookom" wrote: Are you attempting to count the number of employees? What field needs to be Sum()'d? -- Duane Hookom MS Access MVP "LHEMA" wrote in 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. -- LHEMA |
#25
|
|||
|
|||
I'm stuck can not get total sum correct
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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "LHEMA" wrote: ok..I will let you know the results -- LHEMA "Duane Hookom" wrote: 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 "LHEMA" wrote in 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 "Duane Hookom" wrote: Are you attempting to count the number of employees? What field needs to be Sum()'d? -- Duane Hookom MS Access MVP "LHEMA" wrote in |
#26
|
|||
|
|||
I'm stuck can not get total sum correct
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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "LHEMA" wrote: ok..I will let you know the results -- LHEMA "Duane Hookom" wrote: 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 "LHEMA" wrote in 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 "Duane Hookom" wrote: Are you attempting to count the number of employees? What field needs to be Sum()'d? -- Duane Hookom MS Access MVP "LHEMA" wrote in |
#27
|
|||
|
|||
I'm stuck can not get total sum correct
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 |
#28
|
|||
|
|||
I'm stuck can not get total sum correct
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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... Good morning, Duane They are in a report footer and the employee total does work. I think I |
#29
|
|||
|
|||
I'm stuck can not get total sum correct
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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... 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 "Duane Hookom" wrote: 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 "LHEMA" wrote in message ... Good morning, Duane They are in a report footer and the employee total does work. I think I |
#30
|
|||
|
|||
I'm stuck can not get total sum correct
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 |
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 |