Search results

I do. In fact, qryPastRevenue only has 3 fields in it: ProdRevType, PastRevenueAmt, and 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...

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.

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...

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...

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...

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

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...

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!

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.

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)...

No wonder you asked if I had played around with it; I made the ridiculously simple seem hard. Thanks

I meant to name the column AssumptionMonth of course, not AssumptionMo. I am indeed playing around with it now. Thank you

I have 2 columns that are listed as such: AssumptionMo AssumptionYr MAY 2014 JUN 2015 JUL 2015 OCT 2016 I need to create a field called AssumpDate that converts the month into a date field on the 1st day of the month...

TRANSFORM Sum(qry303e_SFADetailProjRevMRC_ONLY.ProjRevMRC) AS SumOfProjRevMRC SELECT qry303e_SFADetailProjRevMRC_ONLY.ProdRevType, qry303e_SFADetailProjRevMRC_ONLY.[Rev Lag], qry303e_SFADetailProjRevMRC_ONLY.[Rev Flow Through] FROM qry303e_SFADetailProjRevMRC_ONLY GROUP BY...

ProjRevDate is a date field formatted as mmm-yy

I have a query where these are the fields: ProductRevType RevLag RevFlowThru CloseMoYr ProjRevDate CurrentMRC ProjRevMRC The ProjRevMRC field is an expression that reads: ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_SFADetailMRC_ONLY]![Rev...

Ranman256 I am not sure I follow you. I did the calculated field in the base query, and in the base query it allowed me to right-click and just change the Properties of the result there. But no such luck once that field was summed in a crosstab.

I have a very basic query with the following calculated field in it: ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0) I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places...

It miht be just one troublesome form or object, but I can't even get into the db. As soon as I select it when trying to import it into a new blank db, I get the error message "Unrecognized database format [my file path]" :banghead: