Question

Locked

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.

Cheers

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| 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
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.

Back to Web Development Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums