Web Development



Get a Query to Query Itself!

By gregmorrisuk ·
Please Help! I have a query in an mdb.

one column gives a period number for a specific month. So September would be 9 and October would be 10 for example

another column captures a currency data as month forcast.

the column i would like to create would be "Last Months Forcast".

Here the query would look at the previous period and display the month forcast currency data in this New column called "Last Months Forcast" current

please help.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

I might be missing something but

by Tony Hopkinson In reply to Get a Query to Query Itse ...

Are you saying if

Query1 returns say
9, 234.45
10, 456.67

Query2 retrns say
Month, Actual
10, 200.00
11, 301.12

and you want

Month, Actual, [Last Month's Forecast]
11 301.12 200


Somthing like
Select Actual.Month,Actual.Actual,Forecast.Forecast as [Last Month's Forecast]
Where Actual.Month = @Month and Forecast.Month = Actual.Month - 1

Basically bolt your queries together. NB when querying for month 1 you'll have to make decsions. Add year to your tables and then something like

((Actual.Month = @Month and Forecast.Month = Actual.Month - 1) and (Actual.Year = Forecast.Year))
((Actual.Month = 1 and Forecast.Month = 12) and (Actual.Year = Forecast.Year + 1))

If in doubt cheat!

Hope theres a clue somewhere in there for you.

Related Discussions

Related Forums