How to Make The Right Numbers Add Up

[ALewis06]

I have 2 query results.

qryRevenue
Revenue_Month(text)
Revenue_Year (number)
RevenueMoYr (text)
RevenueDate (date)
SL_Bucket (text)
SL_Prod (text)
ProdRevType (text)
Revenue Amount (number)

qryAssumptions
Assumption_Month (text)
Assumption_Year (number)
AssumpMoYr (text)
AssumptionDate (date)
AssumpDateMinus1 (date)
SL_Bucket (text)
SL-Prod (text)
ProdRevType (text)
ForecastAmt (number)

Here's my quandary:
I want to combine the two in a query BUT the forecasted amounts by month are based on what happened the previous month. The problem is all future months (meaning Sept forward) must be an amount equal to whatever is in the Forecast Amt for Sept 2015 plus the amount in August 2015. Seems easy enough, but going forward Oct must be what's in the ForecastAmt field plus August, and Nov plus August and so on BUT only for ProdRevType that equals "MRR". I hope that makes sense.

I don't know how to write the expression or if there's another way. It needs to say something like "If the ProdRevType is MRR, then add the ForecastAmt to RevenueAmt where the Assumption Date progresses by month but the date associated with The RevenueAmt remains the same.

 

[plog]

Nope, doesn't make sense. Please demonstrate it with data. Show me 2 sets:

A. Starting data - include table/field names and enough sample data to demonstrate all cases.

B. Expected results - based on the data in A, show me what you expect as a final output of your query.

 

[ALewis06]

Understood, sorry. See attached. Hope that will help a little bit better.

 

[plog]

How come the expected ForecastAmt result for CPEADJ is -25478 and not 3592325 (-25478 + 3617803)?

I thought the expected Forecast Amt is qryAssumptions.ForecastAmt + immediate past 2 months RevenueAmt.

 

[ALewis06]

How come the expected ForecastAmt result for CPEADJ is -25478 and not 3592325 (-25478 + 3617803)?

I thought the expected Forecast Amt is qryAssumptions.ForecastAmt + immediate past 2 months RevenueAmt.

It's only true when the ProdRevType contains "MRR" (I don't know if you saw my notes below on rows 27-29) which brings me back to my original dilemma:
How do I show that condition--ProdRevType contains "MRR"--AND that TotForecastAmt = RevenueAmt of the most current month + ForecastAmt and treat the RevenueAmt field as you would an absolute value in Excel? I updated the attachment and put notes in it that I hope would better illustrate my questions.

 

[plog]

You make a query on qryRevenue to get all your relevant past revenue data:

SELECT qryRevenue.ProdRevType, qryRevenue.RevenueAmt As PastRevenue, qryRevenue.RevenueDate
FROM qryRevenue
WHERE (((qryRevenue.ProdRevType) Like "*MRR*"));

Let's call the above qryPastRevenue. Then, to get your desired results you built a query using qryAssumptions and qryPastRevenue. Link them via ProdRevenueType fields and then change the join to show all from qryAssumptions.

Then for your desired result ForecastAmt (which you will have to rename because qryAssumptons has a field called that) you create a calculation:

ForecastAmount: ForecastAmt + IIf(IsNull([PastRevenue]) OR AssumptionDate<RevenueDate, 0, PastRevenue)

 

[ALewis06]

You make a query on qryRevenue to get all your relevant past revenue data:

SELECT qryRevenue.ProdRevType, qryRevenue.RevenueAmt As PastRevenue, qryRevenue.RevenueDate
FROM qryRevenue
WHERE (((qryRevenue.ProdRevType) Like "*MRR*"));

Let's call the above qryPastRevenue. Then, to get your desired results you built a query using qryAssumptions and qryPastRevenue. Link them via ProdRevenueType fields and then change the join to show all from qryAssumptions.

Then for your desired result ForecastAmt (which you will have to rename because qryAssumptons has a field called that) you create a calculation:

ForecastAmount: ForecastAmt + IIf(IsNull([PastRevenue]) OR AssumptionDate<RevenueDate, 0, PastRevenue)

Unfortunately that didn't work. I followed your instructions but when I joined the tables with and changed join properties to include all records from qryAssumptions, the result is way too many records. I sent you a representative test records but in the real results: qryAssumptions has 4278 records, qryPastRevenue has 336 records but once I joined them the query result has 60,421 records, so of course the totals are grossly overstated. Ideas?

 

[plog]

Try changing it to an aggregate query (click the Sigma/Summation symbol in ribbon) and then run it.

If that doesn't work, can you provide an example of incorrect data? Use the same layout as you did before.

 

[ALewis06]

Try changing it to an aggregate query (click the Sigma/Summation symbol in ribbon) and then run it.

If that doesn't work, can you provide an example of incorrect data? Use the same layout as you did before.

I was just about to post a correction: it actually does work and it doesn't. And you're right I figured that I needed to do an aggregate query and group the qryAssumptions results before joining them with the qryPastRevenue results.

Here's how it did work: for the ProdRevType of "MRR" it perfectly added August's ForecastAmt to July's RevenueAmt.

Here's how it didn't work: when looking ahead to Sept, Oct, Nov, Dec, it didn't do it. That's the tricky part because while in August, I need to be able to say that August Total Forecast=August ForecastAmt+July RevenueAmt, but then (until we're in a new month) I also need to show that Sept Total Forecast=Sept ForecastAmt+July RevenueAmt, Oct Total Forecast+Oct ForecastAmt+July RevenueAmt and so on.

For any months past August, the total for ProdRevType "MRR" is reflecting the ForecastAmt only--not the ForecastAmt+July 2015 RevenueAmt.

 

[plog]

Sorry, I thought your last post explained how you had it figured out.

Could you provide more sample data to show how its not working? Use the prior layout you posted.

 

[ALewis06]

Sorry, I thought your last post explained how you had it figured out.

Could you provide more sample data to show how its not working? Use the prior layout you posted.

See attached. I tried to use more eaxmples showing more months to better illustrate it and I changed the field names on the Assumption amount and the Forecast amount, to better clarify.

 

[plog]

I'm sure if we started with just your table data this could be a lot more elegant, but using the data sources on your last post, this is what you should do:

Create a query called 'sub_LastRevenueDates':

SELECT qryAssumptions.ProdRevTypeMatch, qryAssumptions.AssumpDate, Max(qryPastRevenue.RevenueDate) AS LastRevenueDate
FROM qryAssumptions INNER JOIN qryPastRevenue ON (qryAssumptions.ProdRevTypeMatch = qryPastRevenue.ProdRevType) AND (qryAssumptions.AssumpDate >= qryPastRevenue.RevenueDate)
WHERE (((qryPastRevenue.RevenueAmt)>0))
GROUP BY qryAssumptions.ProdRevTypeMatch, qryAssumptions.AssumpDate;

That will tell you what record in qryPastRevenue you need to use for each record in qryAssumptions.

Then you build another query on top of qryAssumptions, qryPastRevenue and sub_LastRevenueDates to get your final data. The below SQL will get you started on that:

SELECT qryAssumptions.AssumpDate, qryAssumptions.ProdRevTypeMatch, [AssumpAmt]+Nz([RevenueAmt]) AS ForecastAmount
FROM (qryAssumptions LEFT JOIN sub_LastRevenueDates ON (qryAssumptions.ProdRevTypeMatch = sub_LastRevenueDates.ProdRevTypeMatch) AND (qryAssumptions.AssumpDate = sub_LastRevenueDates.AssumpDate)) LEFT JOIN qryPastRevenue ON (sub_LastRevenueDates.ProdRevTypeMatch = qryPastRevenue.ProdRevType) AND (sub_LastRevenueDates.LastRevenueDate = qryPastRevenue.RevenueDate);

 

[ALewis06]

I'm sure if we started with just your table data this could be a lot more elegant, but using the data sources on your last post, this is what you should do:

Create a query called 'sub_LastRevenueDates':

SELECT qryAssumptions.ProdRevTypeMatch, qryAssumptions.AssumpDate, Max(qryPastRevenue.RevenueDate) AS LastRevenueDate
FROM qryAssumptions INNER JOIN qryPastRevenue ON (qryAssumptions.ProdRevTypeMatch = qryPastRevenue.ProdRevType) AND (qryAssumptions.AssumpDate >= qryPastRevenue.RevenueDate)
WHERE (((qryPastRevenue.RevenueAmt)>0))
GROUP BY qryAssumptions.ProdRevTypeMatch, qryAssumptions.AssumpDate;
 
That will tell you what record in qryPastRevenue you need to use for each record in qryAssumptions.
 
Then you build another query on top of qryAssumptions, qryPastRevenue and sub_LastRevenueDates to get your final data. The below SQL will get you started on that:
 
[CODE]
SELECT qryAssumptions.AssumpDate, qryAssumptions.ProdRevTypeMatch, [AssumpAmt]+Nz([RevenueAmt]) AS ForecastAmount
FROM (qryAssumptions LEFT JOIN sub_LastRevenueDates ON (qryAssumptions.ProdRevTypeMatch = sub_LastRevenueDates.ProdRevTypeMatch) AND (qryAssumptions.AssumpDate = sub_LastRevenueDates.AssumpDate)) LEFT JOIN qryPastRevenue ON (sub_LastRevenueDates.ProdRevTypeMatch = qryPastRevenue.ProdRevType) AND (sub_LastRevenueDates.LastRevenueDate = qryPastRevenue.RevenueDate);

I'm getting tripped up when I try to run the query sub_LastRevenueDates. When I set the criteria (qryAssumptions.AssumpDate >= qryPastRevenue.RevenueDate) and execute the query, Access considers it a parameter query and then I have to input a RevenueDate. I don't see how this fits in with where I'm trying to go.

 

[plog]

It shouldn't be prompting you. In your last post you said you had a qryPastRevenue with a RevenueDate.

 

[ALewis06]

I do. In fact, qryPastRevenue only has 3 fields in it: ProdRevType, PastRevenueAmt, and RevenueDate

 

[ALewis06]

Screenshot attached

 

[plog]

Too small.

 

[ALewis06]

I thought that perhaps you could expand it once you open it; if I try to paste it onto a Word doc to make it larger, it exceeds the attachment size limit for this forum.

 

[plog]

No luck. Try cropping it

 

[ALewis06]

I compressed it. Can you view the attached?