Adding Amt to Previous Month's Amt (1 Viewer)

ALewis06

Registered User.
Local time
Today, 00:36
Joined
Jun 21, 2012
Messages
124
I have 2 queries. The results of Query1 are:
Revenue_Month (text)
Revenue_Year (number)
RevenueDate (Date)
Legacy_Bucket (text)
Legacy_Prod (text)
ProdRevType (text)
RevenueAmt (number)

Query2 Results:
Assumption_Month (text)
Assumption_Year (number)
AssumpDate (date)
Legacy_Bucket (text)
Legacy_Prod (text)
ProdRevType (text)
CurrFcstAmt (number)

I created a new query and I joined the two queries by Legacy_Bucket, Legacy_Prod and ProdRevType. I want a query result that will show the RevenueAmt for each month but then I also in that same query want to create a field called AssumptionAmt that for each month is the RevenueAmt for the PREVIOUS month plus whatever is the CurrFcstAmt field for the current month. Help me, please! :banghead:
 

plog

Banishment Pending
Local time
Yesterday, 23:36
Joined
May 11, 2011
Messages
11,613
You need to add a field(s) to Query1 which calculates what the previous month is, that way you can link another instance of it and get the prior month's data.

This would be easy to do if your date fields (Month, Year) resolved to an actual date. You could then use DateAdd to easily determine the month before. So my advice is to add a field in Query 1 and Query 2 that contains that date instead of your Month and Year fields.

For example instead of this:

RevenueYear, RevenueMonth
2015, January
2014, November
2014, February

You would have this:

RevenueDate
1/1/2015
11/1/2014
2/1/2014

You just convert it to the first day of the month and make it an actual date. Then you can use the DateAdd function (http://www.techonthenet.com/access/functions/date/dateadd.php) to determine the prior month. With both those fields in your queries you can then easily link to the prior month's data and include it in any subsequent queries you build upon these 2.
 

ALewis06

Registered User.
Local time
Today, 00:36
Joined
Jun 21, 2012
Messages
124
You need to add a field(s) to Query1 which calculates what the previous month is, that way you can link another instance of it and get the prior month's data.

This would be easy to do if your date fields (Month, Year) resolved to an actual date. You could then use DateAdd to easily determine the month before. So my advice is to add a field in Query 1 and Query 2 that contains that date instead of your Month and Year fields.

For example instead of this:

RevenueYear, RevenueMonth
2015, January
2014, November
2014, February

You would have this:

RevenueDate
1/1/2015
11/1/2014
2/1/2014

You just convert it to the first day of the month and make it an actual date. Then you can use the DateAdd function (http://www.techonthenet.com/access/functions/date/dateadd.php) to determine the prior month. With both those fields in your queries you can then easily link to the prior month's data and include it in any subsequent queries you build upon these 2.

Thank you, I did create a field based on the first day of the month. That is the RevenueDate and AssumpDate fields in both queries. Sorry that I didn't explain that part. I will see if I can figure it out using DateAdd as you mentioned.
 

ALewis06

Registered User.
Local time
Today, 00:36
Joined
Jun 21, 2012
Messages
124
I have re-read your response and now I get what you were saying: I need to create a RevenueDate field based on the Revenue_Month and Revenue_Year minus 1 month so that it will join to the correct AssumpDate field that I want.

Thank you!
 

Users who are viewing this thread

Top Bottom