Calculating figures from a previous month

[Massimo30]

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.

[cecil]

Hi,

I have been scrambling around for solutions to the problem you mention below. I saw you post, but no replies, did you find an answer?

Cecil

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.

[Massimo30]

No still waiting... I'm still searching other sites though -if I find the answer I'll let you know.

Thanks!

[FishVal]

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:

qryChangeOfSale s
[code=sql]
SELECT tblMonthSales.* , (SELECT t.curSale FROM tblMonthSales AS t WHERE t.keyMonthID=tb lMonthSales.key MonthID-1 AND t.lngYear=tblMo nthSales.lngYea r) AS curPrevMonthSal e, tblMonthSales.c urSale/curPrevMonthSal e AS ChangeOfSales
FROM tblMonthSales;
[/code]

qryChangeOfSale s
[code=sql]
SELECT tblMonthSales.* , tblMonthSales.c urSale/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);
[/code]

[Massimo30]

Hi There FishVal,

Thank you for the quick response! I'm going to get going on the expert suggestion you provided!

Thanks again! :)

[FishVal]

Hi There FishVal,

Thank you for the quick response! I'm going to get going on the expert suggestion you provided!

Thanks again! :)

You are welcome and don't hecitate to ask additional questions.

[FishVal]

BTW, here is SQL for enhanced queries which take care of "Januaries" as well.

qryChangeOfSale s

SELECT tblMonthSales.*, iif(tblMonthSales.keyMonthID=1,(SELECT t.curSale FROM tblMonthSales AS t WHERE t.keyMonthID=12 AND t.lngYear=tblMonthSales.lngYear-1), (SELECT t.curSale FROM tblMonthSales AS t WHERE t.keyMonthID=tblMonthSales.keyMonthID-1 AND t.lngYear=tblMonthSales.lngYear)) AS curPrevMonthSale, tblMonthSales.curSale/curPrevMonthSale AS ChangeOfSales
FROM tblMonthSales;

qryChangeOfSale s

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)) OR ((tblMonthSales.keyMonthID=1) AND (tblMonthSales.lngYear=tblMonthSales_1.lngYear+1));

[Massimo30]

I appreciate the help FishVal. I may contact you again if I expand the query further, but for now I think this is what I was looking for.

:)

[FishVal]

I appreciate the help FishVal. I may contact you again if I expand the query further, but for now I think this is what I was looking for.

:)

Thanks. You are welcome. :)

[Massimo30]

Hi FishVal,

-You helped me at the beginning of the week on the below and I have a follow-up question for you:
------------------------------------
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:

qryChangeOfSale s

Code: ( sql )
SELECT tblMonthSales.* , (SELECT t.curSale FROM tblMonthSales AS t WHERE t.keyMonthID=tb lMonthSales.key MonthID-1 AND t.lngYear=tblMo nthSales.lngYea r) AS curPrevMonthSal e, tblMonthSales.c urSale/curPrevMonthSal e AS ChangeOfSales
FROM tblMonthSales;


qryChangeOfSale s

Code: ( sql )
SELECT tblMonthSales.* , tblMonthSales.c urSale/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);
-------------------

New Question:
I ran a simple query to divide 2 additional fields (that was added to tblMonthSales) it returns an answer which I need for an additonal formula. My question is:

1. I need to use that answer to run the same qryChangeofSale s which you gave above.
2. How would I write the answer into the query above to give me a calculation again taking into consideration previous and current month?

tblMonthSales:
Field 1:CurInventoryL istings
Fields2:Number of Sales

Query: qryInventory
(CurInventoryLi stings/Number of Sales) =MonthsInventor ySupply


Thanks you in advance! :)

[FishVal]

New Question:
I ran a simple query to divide 2 additional fields (that was added to tblMonthSales) it returns an answer which I need for an additonal formula. My question is:

1. I need to use that answer to run the same qryChangeofSale s which you gave above.
2. How would I write the answer into the query above to give me a calculation again taking into consideration previous and current month?

tblMonthSales:
Field 1:CurInventoryL istings
Fields2:Number of Sales

Query: qryInventory
(CurInventoryLi stings/Number of Sales) =MonthsInventor ySupply


Thanks you in advance! :)

Hi, Massimo

Sorry. The formula you've posted is somewhat unclear for me.

(CurInventoryLi stings/Number of Sales) =MonthsInventor ySupply

Do you mean [CurInventoryLis tings] and [Number of Sales] fields supposed to belong to different records (previous/next month) ?

Plz, provide the tables metadata and queries SQL as they are looking so far.

Here is an example of how to post table MetaData :
Table Name=tblStudent

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time