A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

sum for past tweleve months



 
 
Thread Tools Display Modes
  #1  
Old February 2nd, 2010, 08:25 PM posted to microsoft.public.access.queries
tighe
external usenet poster
 
Posts: 53
Default 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  
Old February 2nd, 2010, 08:42 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old February 2nd, 2010, 10:42 PM posted to microsoft.public.access.queries
tighe
external usenet poster
 
Posts: 53
Default 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  
Old February 2nd, 2010, 11:36 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old February 3rd, 2010, 01:17 AM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old February 3rd, 2010, 02:16 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old February 3rd, 2010, 04:15 PM posted to microsoft.public.access.queries
tighe
external usenet poster
 
Posts: 53
Default 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  
Old February 6th, 2010, 09:26 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:56 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.