CODEWITHSUNDEEP
ms-access
mchen
mchen

Reputation: 10156

How to construct price time series from price changes using MS-Access 2007 queries?

Background

I've got a table of price changes tblPriceChanges of various items tblItems (example below - data made up):

[tblPriceChanges]:
Timestamp       Item    Price
9AM 01/01/2013  Orange  50p
9AM 01/01/2013  Apple   30p
2PM 01/01/2013  Pen     80p
2PM 02/01/2013  Orange  55p
2PM 02/01/2013  Pen     85p
9AM 03/01/2013  Apple   25p
9AM 05/01/2013  Pencil  10p
9AM 05/01/2013  Pen     70p
2PM 05/01/2013  Pencil  15p    <- Notice there can be multiple price changes on the same day
...

[tblItems]:
Item    Category    Ratio
Orange  Fruit       1
Apple   Fruit       3
Pen     Stationary  2
Pencil  Stationary  5
...

Problem

The end result is that I want to be able to see how the average price of each category changes through time - for example:

enter image description here

Specifically, the average price series of Fruits, for instance, should be calculated as the weighted average of Orange and Apple prices in a Ratio of 1:3. So in the end I'm looking to generate (via some combination of queries) the following table for the underlying data of the chart:

Timestamp   Fruit   Stationary
01/01/2013  40.0    80.0
02/01/2013  55.0    85.0
03/01/2013  50.0    85.0
04/01/2013  50.0    85.0
05/01/2013  50.0    75.0
...
(this data is also made up and so probably not consistent with the original example)

I've managed to get an inner join on the two tables, but not too sure how to proceed. My main problem is how to handle days with no prices changes such as 01/04/13. The average prices still exist, but is not getting picked up by any query I try.

So how to use queries to construct data for the chart?

Upvotes: 1

Views: 1064

Answers (1)

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

One remark before we start on a solution: be careful not to use reserved words for field names. Timestamp is a reserved word. Access will let you name a column that way, but you may encounter strange issues later on, especially if you ever move to another database in the future or use some other tools that fetch data from your Access database.

So here, I renamed your Timestamp column to DateTimeStamp.

I think that your requirements would be more straightforward to implement in VBA rather than in pure SQL queries: while you can easily build a query that gets you the average of each category per day, you are going to struggle to fill-in the data for the days where you have no data.

Simple but incomplete SQL solution

However, maybe having these holes in your data isn't that much of an issue since the graph will simply skip those missing values (it's not like they would show up as a data value of 0.00).
In that case, the following query should give you the results:

SELECT Dateserial(Year([DateTimeStamp]), 
                  Month([DateTimeStamp]), 
                  Day([DateTimeStamp])) AS NormalisedDate,
       tblItems.Category,
       SUM([Price]*[Ratio])/SUM([Ratio]) AS AvgPrice
FROM   tblPriceChanges
       INNER JOIN tblItems
         ON tblPriceChanges.Item=tblItems.Item
GROUP  BY Dateserial(Year([DateTimeStamp]), 
                     Month([DateTimeStamp]), 
                     Day([DateTimeStamp])),
          tblItems.Category

Would result in the following:

NormalisedDate    Category     AvgPrice
01/01/2013        Fruit        35
01/01/2013        Stationary   80
02/01/2013        Fruit        55
02/01/2013        Stationary   85
03/01/2013        Fruit        25
05/01/2013        Stationary   22.0833333333333

Upvotes: 1

Related Questions