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 |
#1
|
|||
|
|||
sum for past tweleve months
i feel a little retarded i can’t pull the available information together to
get the result i need. i have looked at http://support.microsoft.com/kb/208714 and a number of other posts, but i have not been able to get anything but the most recent sum for all Occur Date. That equation is Expr1: DSum("[HiredNum]","Firm_Count_Hired_Fired","[Hired.CurDate]= [OccurDate]")=28 for every record. i need to be able to get the gross hired/fired for an occur date but only the last twelve months. currently i have the first 4 columns below and used excel to show what the answers would be: OccurDate EmployeeCount HiredNum FirmNum Sum_ChangePast12Months 200710 1 1 0 1 200711 1 0 0 1 200712 1 0 0 1 200801 1 0 0 1 200802 1 0 0 1 200803 1 0 0 1 200804 1 0 0 1 200805 4 3 0 4 200806 4 0 0 4 200807 4 0 0 4 200808 5 1 0 5 200809 5 0 0 5 200810 6 1 0 5 200811 7 1 0 6 200812 10 3 0 9 200901 10 0 0 9 200902 11 1 0 10 200903 13 2 0 12 200904 14 1 0 13 200905 15 1 0 11 200906 16 1 0 12 200907 17 1 -1 12 200908 16 0 -2 9 200909 16 2 -1 10 200910 17 2 0 11 200911 20 3 0 13 200912 21 1 0 11 201001 24 3 0 14 201002 24 0 0 13 TIA |
#2
|
|||
|
|||
sum for past tweleve months
Have you looked into using a Totals query?
You could get the count (?or sum?) of a field, and use selection criterion to limit it to OccurDate values within the last year. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "tighe" wrote in message ... i feel a little retarded i can't pull the available information together to get the result i need. i have looked at http://support.microsoft.com/kb/208714 and a number of other posts, but i have not been able to get anything but the most recent sum for all Occur Date. That equation is Expr1: DSum("[HiredNum]","Firm_Count_Hired_Fired","[Hired.CurDate]= [OccurDate]")=28 for every record. i need to be able to get the gross hired/fired for an occur date but only the last twelve months. currently i have the first 4 columns below and used excel to show what the answers would be: OccurDate EmployeeCount HiredNum FirmNum Sum_ChangePast12Months 200710 1 1 0 1 200711 1 0 0 1 200712 1 0 0 1 200801 1 0 0 1 200802 1 0 0 1 200803 1 0 0 1 200804 1 0 0 1 200805 4 3 0 4 200806 4 0 0 4 200807 4 0 0 4 200808 5 1 0 5 200809 5 0 0 5 200810 6 1 0 5 200811 7 1 0 6 200812 10 3 0 9 200901 10 0 0 9 200902 11 1 0 10 200903 13 2 0 12 200904 14 1 0 13 200905 15 1 0 11 200906 16 1 0 12 200907 17 1 -1 12 200908 16 0 -2 9 200909 16 2 -1 10 200910 17 2 0 11 200911 20 3 0 13 200912 21 1 0 11 201001 24 3 0 14 201002 24 0 0 13 TIA |
#3
|
|||
|
|||
sum for past tweleve months
jeff maybe i am misunderstanding, but i dont need the sum from the last
twleve months. i need a sum from 12 months prior to an OccurDate, if OccurDate=200809 sum is all hirings and firings from 200710-200809. which i can't figure out how to get the system to return that idea. but it also needs to returmn the answer for all available OccurDate. the data is client specific so one might just have since 200910 but another's might start in 199904. "Jeff Boyce" wrote: Have you looked into using a Totals query? You could get the count (?or sum?) of a field, and use selection criterion to limit it to OccurDate values within the last year. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "tighe" wrote in message ... i feel a little retarded i can't pull the available information together to get the result i need. i have looked at http://support.microsoft.com/kb/208714 and a number of other posts, but i have not been able to get anything but the most recent sum for all Occur Date. That equation is Expr1: DSum("[HiredNum]","Firm_Count_Hired_Fired","[Hired.CurDate]= [OccurDate]")=28 for every record. i need to be able to get the gross hired/fired for an occur date but only the last twelve months. currently i have the first 4 columns below and used excel to show what the answers would be: OccurDate EmployeeCount HiredNum FirmNum Sum_ChangePast12Months 200710 1 1 0 1 200711 1 0 0 1 200712 1 0 0 1 200801 1 0 0 1 200802 1 0 0 1 200803 1 0 0 1 200804 1 0 0 1 200805 4 3 0 4 200806 4 0 0 4 200807 4 0 0 4 200808 5 1 0 5 200809 5 0 0 5 200810 6 1 0 5 200811 7 1 0 6 200812 10 3 0 9 200901 10 0 0 9 200902 11 1 0 10 200903 13 2 0 12 200904 14 1 0 13 200905 15 1 0 11 200906 16 1 0 12 200907 17 1 -1 12 200908 16 0 -2 9 200909 16 2 -1 10 200910 17 2 0 11 200911 20 3 0 13 200912 21 1 0 11 201001 24 3 0 14 201002 24 0 0 13 TIA . |
#4
|
|||
|
|||
sum for past tweleve months
I think what you want is 'Running Sum'. Search on that.
-- Build a little, test a little. "tighe" wrote: jeff maybe i am misunderstanding, but i dont need the sum from the last twleve months. i need a sum from 12 months prior to an OccurDate, if OccurDate=200809 sum is all hirings and firings from 200710-200809. which i can't figure out how to get the system to return that idea. but it also needs to returmn the answer for all available OccurDate. the data is client specific so one might just have since 200910 but another's might start in 199904. "Jeff Boyce" wrote: Have you looked into using a Totals query? You could get the count (?or sum?) of a field, and use selection criterion to limit it to OccurDate values within the last year. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "tighe" wrote in message ... i feel a little retarded i can't pull the available information together to get the result i need. i have looked at http://support.microsoft.com/kb/208714 and a number of other posts, but i have not been able to get anything but the most recent sum for all Occur Date. That equation is Expr1: DSum("[HiredNum]","Firm_Count_Hired_Fired","[Hired.CurDate]= [OccurDate]")=28 for every record. i need to be able to get the gross hired/fired for an occur date but only the last twelve months. currently i have the first 4 columns below and used excel to show what the answers would be: OccurDate EmployeeCount HiredNum FirmNum Sum_ChangePast12Months 200710 1 1 0 1 200711 1 0 0 1 200712 1 0 0 1 200801 1 0 0 1 200802 1 0 0 1 200803 1 0 0 1 200804 1 0 0 1 200805 4 3 0 4 200806 4 0 0 4 200807 4 0 0 4 200808 5 1 0 5 200809 5 0 0 5 200810 6 1 0 5 200811 7 1 0 6 200812 10 3 0 9 200901 10 0 0 9 200902 11 1 0 10 200903 13 2 0 12 200904 14 1 0 13 200905 15 1 0 11 200906 16 1 0 12 200907 17 1 -1 12 200908 16 0 -2 9 200909 16 2 -1 10 200910 17 2 0 11 200911 20 3 0 13 200912 21 1 0 11 201001 24 3 0 14 201002 24 0 0 13 TIA . |
#5
|
|||
|
|||
sum for past tweleve months
I guess I did misunderstand.
The technique, however, would be analogous, wouldn't it? If you have a date know ([OccurDate], and you want the total of a field where the dates happen between [OccurDate] and [OccurDate]-12 months, I think the same approach should work. Or maybe I'm still misunderstanding... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "tighe" wrote in message ... jeff maybe i am misunderstanding, but i dont need the sum from the last twleve months. i need a sum from 12 months prior to an OccurDate, if OccurDate=200809 sum is all hirings and firings from 200710-200809. which i can't figure out how to get the system to return that idea. but it also needs to returmn the answer for all available OccurDate. the data is client specific so one might just have since 200910 but another's might start in 199904. "Jeff Boyce" wrote: Have you looked into using a Totals query? You could get the count (?or sum?) of a field, and use selection criterion to limit it to OccurDate values within the last year. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "tighe" wrote in message ... i feel a little retarded i can't pull the available information together to get the result i need. i have looked at http://support.microsoft.com/kb/208714 and a number of other posts, but i have not been able to get anything but the most recent sum for all Occur Date. That equation is Expr1: DSum("[HiredNum]","Firm_Count_Hired_Fired","[Hired.CurDate]= [OccurDate]")=28 for every record. i need to be able to get the gross hired/fired for an occur date but only the last twelve months. currently i have the first 4 columns below and used excel to show what the answers would be: OccurDate EmployeeCount HiredNum FirmNum Sum_ChangePast12Months 200710 1 1 0 1 200711 1 0 0 1 200712 1 0 0 1 200801 1 0 0 1 200802 1 0 0 1 200803 1 0 0 1 200804 1 0 0 1 200805 4 3 0 4 200806 4 0 0 4 200807 4 0 0 4 200808 5 1 0 5 200809 5 0 0 5 200810 6 1 0 5 200811 7 1 0 6 200812 10 3 0 9 200901 10 0 0 9 200902 11 1 0 10 200903 13 2 0 12 200904 14 1 0 13 200905 15 1 0 11 200906 16 1 0 12 200907 17 1 -1 12 200908 16 0 -2 9 200909 16 2 -1 10 200910 17 2 0 11 200911 20 3 0 13 200912 21 1 0 11 201001 24 3 0 14 201002 24 0 0 13 TIA . |
#6
|
|||
|
|||
sum for past tweleve months
Is OccurDate a date field? Or a number field? Or a textfield?
Also what is CurDate? and what table is it in? You might want to post the SQL text of the query you are currently using. For instance you have FirmNum in the columns you posted. If you are trying to do this by firm, then we need to know that also. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County tighe wrote: i feel a little retarded i can’t pull the available information together to get the result i need. i have looked at http://support.microsoft.com/kb/208714 and a number of other posts, but i have not been able to get anything but the most recent sum for all Occur Date. That equation is Expr1: DSum("[HiredNum]","Firm_Count_Hired_Fired","[Hired.CurDate]= [OccurDate]")=28 for every record. i need to be able to get the gross hired/fired for an occur date but only the last twelve months. currently i have the first 4 columns below and used excel to show what the answers would be: OccurDate EmployeeCount HiredNum FirmNum Sum_ChangePast12Months 200710 1 1 0 1 200711 1 0 0 1 200712 1 0 0 1 200801 1 0 0 1 200802 1 0 0 1 200803 1 0 0 1 200804 1 0 0 1 200805 4 3 0 4 200806 4 0 0 4 200807 4 0 0 4 200808 5 1 0 5 200809 5 0 0 5 200810 6 1 0 5 200811 7 1 0 6 200812 10 3 0 9 200901 10 0 0 9 200902 11 1 0 10 200903 13 2 0 12 200904 14 1 0 13 200905 15 1 0 11 200906 16 1 0 12 200907 17 1 -1 12 200908 16 0 -2 9 200909 16 2 -1 10 200910 17 2 0 11 200911 20 3 0 13 200912 21 1 0 11 201001 24 3 0 14 201002 24 0 0 13 TIA |
#7
|
|||
|
|||
sum for past tweleve months
overall i guess my best solution is running sum buit i cant get the right
results, for each period no less adding in a 12 month constraint. Jeff:right but i guess my problem is i dont know how to werite it to get the required answers. all my tries do not get the expected result. Karl: i did, http://support.microsoft.com/kb/208714, but my result from DSum("[HiredNum]","Firm_Count_Hired_Fired",[Firm_Count_Hired_Fired]![Hired.CurDate]=[Firm_Employee_Count_time].[OccurDate]) , coming from the table below.is not running but i get the total sum for every record(row) in the result. John: it should be but i did format it :Format([myDate],"yyyymm") current table Curdate is the date for the hiring/firing records, you will notice some are blamk where no action took place in that month: OccurDate EmployeeCount Fired.CurDate Hired.CurDate HiredNum FiredNum 200910 1 200910 1 0 200911 1 0 0 200912 1 0 0 201001 1 0 0 201002 1 0 0 i font have anything except the Dsum above, the FirmNum was incorrectly labeld and is really FiredNum, as seen above. hope this additional information helps, maybe ive over complicated the whole matter. the end result i need is YearMonth, total employess, HiredNum, FiredNum, Running sum for twelve months, if that sum goes over 10 employee if under 33.3 or over 30% if over 33.3 employees. last the last part can probably be done with conditional formatting. also the above table is really a query:SELECT Firm_Employee_Count_time.OccurDate, Fired.CurDate, Hired.CurDate, Firm_Employee_Count_time.EmployeeCount, nz([fired].[Firm],0) AS FiredNum, nz([hired].[Firm],0) AS HiredNum FROM (Firm_Employee_Count_time LEFT JOIN Fired ON Firm_Employee_Count_time.OccurDate=Fired.CurDate) LEFT JOIN Hired ON Firm_Employee_Count_time.OccurDate=Hired.CurDate GROUP BY Firm_Employee_Count_time.OccurDate, Fired.CurDate, Hired.CurDate, Firm_Employee_Count_time.EmployeeCount, nz([fired].[Firm],0), nz([hired].[Firm],0); "John Spencer" wrote: Is OccurDate a date field? Or a number field? Or a textfield? Also what is CurDate? and what table is it in? You might want to post the SQL text of the query you are currently using. For instance you have FirmNum in the columns you posted. If you are trying to do this by firm, then we need to know that also. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County tighe wrote: i feel a little retarded i can’t pull the available information together to get the result i need. i have looked at http://support.microsoft.com/kb/208714 and a number of other posts, but i have not been able to get anything but the most recent sum for all Occur Date. That equation is Expr1: DSum("[HiredNum]","Firm_Count_Hired_Fired","[Hired.CurDate]= [OccurDate]")=28 for every record. i need to be able to get the gross hired/fired for an occur date but only the last twelve months. currently i have the first 4 columns below and used excel to show what the answers would be: OccurDate EmployeeCount HiredNum FirmNum Sum_ChangePast12Months 200710 1 1 0 1 200711 1 0 0 1 200712 1 0 0 1 200801 1 0 0 1 200802 1 0 0 1 200803 1 0 0 1 200804 1 0 0 1 200805 4 3 0 4 200806 4 0 0 4 200807 4 0 0 4 200808 5 1 0 5 200809 5 0 0 5 200810 6 1 0 5 200811 7 1 0 6 200812 10 3 0 9 200901 10 0 0 9 200902 11 1 0 10 200903 13 2 0 12 200904 14 1 0 13 200905 15 1 0 11 200906 16 1 0 12 200907 17 1 -1 12 200908 16 0 -2 9 200909 16 2 -1 10 200910 17 2 0 11 200911 20 3 0 13 200912 21 1 0 11 201001 24 3 0 14 201002 24 0 0 13 TIA . |
#8
|
|||
|
|||
sum for past tweleve months
Using your query
John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County tighe wrote: overall i guess my best solution is running sum buit i cant get the right results, for each period no less adding in a 12 month constraint. Jeff:right but i guess my problem is i dont know how to werite it to get the required answers. all my tries do not get the expected result. Karl: i did, http://support.microsoft.com/kb/208714, but my result from DSum("[HiredNum]","Firm_Count_Hired_Fired",[Firm_Count_Hired_Fired]![Hired.CurDate]=[Firm_Employee_Count_time].[OccurDate]) , coming from the table below.is not running but i get the total sum for every record(row) in the result. John: it should be but i did format it :Format([myDate],"yyyymm") current table Curdate is the date for the hiring/firing records, you will notice some are blamk where no action took place in that month: OccurDate EmployeeCount Fired.CurDate Hired.CurDate HiredNum FiredNum 200910 1 200910 1 0 200911 1 0 0 200912 1 0 0 201001 1 0 0 201002 1 0 0 i font have anything except the Dsum above, the FirmNum was incorrectly labeld and is really FiredNum, as seen above. hope this additional information helps, maybe ive over complicated the whole matter. the end result i need is YearMonth, total employess, HiredNum, FiredNum, Running sum for twelve months, if that sum goes over 10 employee if under 33.3 or over 30% if over 33.3 employees. last the last part can probably be done with conditional formatting. also the above table is really a query:SELECT Firm_Employee_Count_time.OccurDate, Fired.CurDate, Hired.CurDate, Firm_Employee_Count_time.EmployeeCount, nz([fired].[Firm],0) AS FiredNum, nz([hired].[Firm],0) AS HiredNum FROM (Firm_Employee_Count_time LEFT JOIN Fired ON Firm_Employee_Count_time.OccurDate=Fired.CurDate) LEFT JOIN Hired ON Firm_Employee_Count_time.OccurDate=Hired.CurDate GROUP BY Firm_Employee_Count_time.OccurDate, Fired.CurDate, Hired.CurDate, Firm_Employee_Count_time.EmployeeCount, nz([fired].[Firm],0), nz([hired].[Firm],0); "John Spencer" wrote: Is OccurDate a date field? Or a number field? Or a textfield? Also what is CurDate? and what table is it in? You might want to post the SQL text of the query you are currently using. For instance you have FirmNum in the columns you posted. If you are trying to do this by firm, then we need to know that also. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County tighe wrote: i feel a little retarded i can’t pull the available information together to get the result i need. i have looked at http://support.microsoft.com/kb/208714 and a number of other posts, but i have not been able to get anything but the most recent sum for all Occur Date. That equation is Expr1: DSum("[HiredNum]","Firm_Count_Hired_Fired","[Hired.CurDate]= [OccurDate]")=28 for every record. i need to be able to get the gross hired/fired for an occur date but only the last twelve months. currently i have the first 4 columns below and used excel to show what the answers would be: OccurDate EmployeeCount HiredNum FirmNum Sum_ChangePast12Months 200710 1 1 0 1 200711 1 0 0 1 200712 1 0 0 1 200801 1 0 0 1 200802 1 0 0 1 200803 1 0 0 1 200804 1 0 0 1 200805 4 3 0 4 200806 4 0 0 4 200807 4 0 0 4 200808 5 1 0 5 200809 5 0 0 5 200810 6 1 0 5 200811 7 1 0 6 200812 10 3 0 9 200901 10 0 0 9 200902 11 1 0 10 200903 13 2 0 12 200904 14 1 0 13 200905 15 1 0 11 200906 16 1 0 12 200907 17 1 -1 12 200908 16 0 -2 9 200909 16 2 -1 10 200910 17 2 0 11 200911 20 3 0 13 200912 21 1 0 11 201001 24 3 0 14 201002 24 0 0 13 TIA . |
Thread Tools | |
Display Modes | |
|
|