Web Development

Ask something
Question Locked

Get a Query to Query Itself!

0 helpful
2 replies

by gregmorrisuk

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

2 replies

by tony hopkinson

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.

This discussion is closed to new replies.