sum for past tweleve months

  • Thread starter Thread starter tighe
  • Start date Start date
T

tighe

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
 
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.
 
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 said:
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 said:
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
Click to expand...


.
Click to expand...
 
I think what you want is 'Running Sum'. Search on that.

--
Build a little, test a little.


tighe said:
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 said:
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 said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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
 
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 said:
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
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
Click to expand...
.
Click to expand...
 
Using your query

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
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 said:
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
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
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top