Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculate each month

Status
Not open for further replies.

hockeylvr

Technical User
Nov 26, 2002
140
Could someone please tell me what type of query/function I should be looking for to accomplish this task:

I have a table of projects. Each project has a cost savings effective date, i.e. 5/1/06. The total savings for each project is based on monthly volumes in another table. I have to calculate the savings for each month from the effective date + the next 11 months. Each month will be different.

Project 1 - reduction of $.50 effdate of 5/1/06
Need to calculate
May volume - 100 Savings for May - $50
June volume - 200 Savings for June - $100
July volume - 150 Savings for July - $75

Through April 2007

Do I need to create a query with fields for each month(which I can already do) or is there something I can do in VBA like a for/loop function that will go through and calculate each month? I really don't even care if it calculates past 12 months, I just need to get the monthly's calculated.
Would appreciate any help.

 
If I understand what your trying to do, you could use a query.
This is untested, just off the top of my head so you'll have to play with it.

SELECT DATEPART("M",dMonth), SUM(iVolumn) * .5 as Savings
GROUP BY DATEPART("M",dMonth)
HAVING dMonth BETWEEN "#5/1/2006#" AND "#" & DATEADD("M", 12, 5/1/2006)& "#
 
Thanks for the help - In looking at your code, it sounds like there is no way to be able to do this in one field in a query and I will need to create a separate field query for every single month to get the desired results.


Toni
 
hockeylvr,
Do you really want all eleven values in the same field?

You could use a sub-query or [tt]DSum()[/tt]:
Code:
LastEleven: DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date])) * .5 & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 1)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 2)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 3)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 4)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 5)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 6)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 7)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 8)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 9)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 10)) * .5  & ", " & DSum("[Volume]", "Monthly Volumes", "Month([Date])=" & Month([ThisTable].[Date]) + 11) * .5

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)
 
Have you considered
1) Creating a query that feeds a report? Do the summing in the report rather than in the query (holding the sum in a table). Why put it in a table if you can calculate it from the raw data. I do put results in table if I'm setting up a sort of warehouse database to hold summary data.
2) Sometimes this type of iterative problem requires two queries (double queries). The first gets data to a given stage, then the 2nd query uses the first query as its source. Sorry can't be more specific. Seems that the queries would be Group By / Sum type query.
Jeff
 
With the ideas given you should be able to make it work with a query. You may have to nest the queries to get your final results as you want them, just need to be willing to try.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top