Looking for answers and have done a search in books and websites and still can't find the answer:
I am setting up a Market Trend DB on housing figures. I have the following fields in my table:
My objective is to find the percent of change between this month and last month. Easy enough, but how do I set this up? It's not like excel where you can just enter the cell.
Month (Jan,Feb,Mar, etc..)
Year
Market Trend Area
SFR Median Sales Prices
SFR % of Change of Sales Price Formula: (Current Month SFR Median Sales Price Previous Month SFR Median Sales Price)/(Previous Month SFR Median Sales Price)
How do I set up the formula so that it recognizes which column is the previous month and which is the current month? And how do I have the DB take the year into consideration?
I hope this makes sense.
Thanks any help would be really appreciated.
Hi, there.
I have a working solution for you.
First of all you need to reorganize table structure as following.
Tables: tblMonthSales
keyMonthSaleID Autonumber(Long), PK
keyMonthID Long, FK(tblMonths)
lngYear Long
curSale Currency
tblMonths
keyMonthID Autonumber(Long), PK
txtMonth Text
Having done this you will be able to build the following queries.
Each of them does the same.
So far the queries don't calculate change of sales between January and December of previous year. If you will find this solution acceptable, I'll help you to solve this little problem too. :)
Queries: qryChangeOfSales 
SELECT tblMonthSales.*, (SELECT t.curSale FROM tblMonthSales AS t WHERE t.keyMonthID=tblMonthSales.keyMonthID1 AND t.lngYear=tblMonthSales.lngYear) AS curPrevMonthSale, tblMonthSales.curSale/curPrevMonthSale AS ChangeOfSales

FROM tblMonthSales;

qryChangeOfSales 
SELECT tblMonthSales.*, tblMonthSales.curSale/tblMonthSales_1.curSale AS ChangeOfSale

FROM tblMonthSales LEFT JOIN tblMonthSales AS tblMonthSales_1 ON (tblMonthSales.keyMonthID = tblMonthSales_1.keyMonthID+1) AND (tblMonthSales.lngYear = tblMonthSales_1.lngYear);
