Web Development·12,052 discussions

Get a Query to Query Itself!

Locked

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.

Cheers

Topic

Clarifications

In reply to Get a Query to Query Itself!

Clarifications

I might be missing something but

In reply to Get a Query to Query Itself!

Are you saying if

Query1 returns say
Month,Forecast
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

Where
((Actual.Month = @Month and Forecast.Month = Actual.Month – 1) and (Actual.Year = Forecast.Year))
OR
((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.

[Author]

Replies