By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,146 Members | 1,631 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,146 IT Pros & Developers. It's quick & easy.

Calculating figures from a previous month

P: 5
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.
Aug 27 '07 #1
Share this Question
Share on Google+
10 Replies


P: 5
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.
Aug 27 '07 #2

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

Thanks!
Aug 27 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMonthSales.*, (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
  2. FROM tblMonthSales;
  3.  
qryChangeOfSales
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMonthSales.*, tblMonthSales.curSale/tblMonthSales_1.curSale AS ChangeOfSale
  2. FROM tblMonthSales LEFT JOIN tblMonthSales AS tblMonthSales_1 ON (tblMonthSales.keyMonthID = tblMonthSales_1.keyMonthID+1) AND (tblMonthSales.lngYear = tblMonthSales_1.lngYear);
  3.  
Aug 27 '07 #4

P: 5
Hi There FishVal,

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

Thanks again! :)
Aug 27 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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.
Aug 27 '07 #6

FishVal
Expert 2.5K+
P: 2,653
BTW, here is SQL for enhanced queries which take care of "Januaries" as well.

qryChangeOfSales
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM tblMonthSales;
  3.  
qryChangeOfSales
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMonthSales.*, tblMonthSales.curSale/tblMonthSales_1.curSale AS ChangeOfSale
  2. 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));
  3.  
Aug 27 '07 #7

P: 5
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.

:)
Aug 28 '07 #8

FishVal
Expert 2.5K+
P: 2,653
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. :)
Aug 28 '07 #9

P: 5
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:

qryChangeOfSales

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


qryChangeOfSales

Code: ( sql )
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);
-------------------

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 qryChangeofSales 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:CurInventoryListings
Fields2:Number of Sales

Query: qryInventory
(CurInventoryListings/Number of Sales) =MonthsInventorySupply


Thanks you in advance! :)
Aug 31 '07 #10

FishVal
Expert 2.5K+
P: 2,653
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 qryChangeofSales 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:CurInventoryListings
Fields2:Number of Sales

Query: qryInventory
(CurInventoryListings/Number of Sales) =MonthsInventorySupply


Thanks you in advance! :)
Hi, Massimo

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

(CurInventoryListings/Number of Sales) =MonthsInventorySupply

Do you mean [CurInventoryListings] 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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Sep 3 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.