How to Make The Right Numbers Add Up

[plog]

Your problem isn't with this query--its with one of the 2 queries its built on. Its looking for a PastRevenueDate field in the query called qryPastRevenue. Either qry704 or qry705 is built on qryPastRevenue and it can't find it. You need to run qry704 and qry705 to see which one gives this error--most likely qry705.

 

[ALewis06]

Your problem isn't with this query--its with one of the 2 queries its built on. Its looking for a PastRevenueDate field in the query called qryPastRevenue. Either qry704 or qry705 is built on qryPastRevenue and it can't find it. You need to run qry704 and qry705 to see which one gives this error--most likely qry705.

You're right--I found the issue and fixed that. Taking your code and changing field/table names to my actual query, here's what I ran:

qry706_subLastRevenueDates
SELECT qry704_AssumpSumGroupedByProdRevType.ProdRevTypeMatch, qry704_AssumpSumGroupedByProdRevType.AssumpDate, qry705_PastRevenue.RevenueDate, qry705_PastRevenue.PastRevenue
FROM qry704_AssumpSumGroupedByProdRevType INNER JOIN qry705_PastRevenue ON (qry704_AssumpSumGroupedByProdRevType.AssumpDateMinus1 = qry705_PastRevenue.RevenueDate) AND (qry704_AssumpSumGroupedByProdRevType.ProdRevTypeMatch = qry705_PastRevenue.ProdRevType)
WHERE (((qry704_AssumpSumGroupedByProdRevType.AssumpDate)>=[qry705_PastRevenue].[RevenueDate]) AND ((qry705_PastRevenue.PastRevenue)>0));


query2
SELECT qry704_AssumpSumGroupedByProdRevType.AssumpDate, qry704_AssumpSumGroupedByProdRevType.ProdRevTypeMatch, [qry704_AssumpSumGroupedByProdRevType.SumOfCurrFcst_Amt]+Nz([qry706_subLastRevenueDates.PastRevenue]) AS ForecastAmount
FROM (qry704_AssumpSumGroupedByProdRevType LEFT JOIN qry706_subLastRevenueDates ON (qry704_AssumpSumGroupedByProdRevType.AssumpDate = qry706_subLastRevenueDates.AssumpDate) AND (qry704_AssumpSumGroupedByProdRevType.ProdRevTypeMatch = qry706_subLastRevenueDates.ProdRevTypeMatch)) LEFT JOIN qry705_PastRevenue ON (qry706_subLastRevenueDates.RevenueDate = qry705_PastRevenue.RevenueDate) AND (qry706_subLastRevenueDates.ProdRevTypeMatch = qry705_PastRevenue.ProdRevType);

but and again it seemed to add the July Revenue to the August assumptions as it should but didn't so it with the following months. see attached result.

 

[plog]

Its probably this part of your JOIN clause in qry706:

qry704_AssumpSumGroupedByProdRevType.AssumpDateMi nus1 = qry705_PastRevenue.RevenueDate

I don't think it belongs.

 

[ALewis06]

Its probably this part of your JOIN clause in qry706:

qry704_AssumpSumGroupedByProdRevType.AssumpDateMi nus1 = qry705_PastRevenue.RevenueDate

I don't think it belongs.

I have to use qry704_AssumpSumGroupedByProdRevType.AssumpDateMinus1 in the JOIN clause instead of qry704_AssumpSumGroupedByProdRevType.AssumpDate due to the fact that Forecast will based on Revenue from the most recent closed month plus Assumption amounts from the PREVIOUS month. So I if I try to JOIN
qry704_AssumpSumGroupedByProdRevType.AssumpDate = qry705_PastRevenue.RevenueDate

I get zero results.

 

[plog]

Honestly I'm lost. The last set of data you provided that I wrote code on listed queries/tables that aren't the same as the code you most recently posted. Further there's a JOIN in there that I don't think should be in there.

I'm lost.

 

[ALewis06]

I've tried everything I can think of to arrive at my desired results but no dice. Since I have managed to thoroughly confuse you, is it ok if I close out this thread and start a whole new one, keeping my question as simple as possible?

 

[plog]

sounds like a good idea