Run Chart of Price Changes

[ckell23]

I have a table where I am storing the entire Price History (tblPriceHistory) of each product being sold.

The table is very simple. Contains the following fields:
ProductID,
UnitPrice,
BeginDate,
EndDate.

On one of my forms in the database, I currently have a Line Chart that displays the price changes over the entire history of the product. The chart works just fine now, however, the client has asked for a change that is going a bit beyond my skill level. The current chart was built using a very simple query against the table.

Right now, the chart plots a data point for each and every price change that has been logged into the table. The problem is that some products have not seen a price increase since their inception 3 years ago...in which case the chart only displays one data point. Others have had a price change at some point in their history, but maybe only every 3-4 years. While the chart is still technically correct, the client wants to always display the price as of the first of January for every year after the product came into existence.

One thought I had was to create a January 1 record for each product, whether or not there was a price change on that date or not. While that would get me the Chart I want, I feel like it is bastardizing the data.

Looking for a way to build a recordset that would populate the Chart and it would include A.) every actual record in the table (tblPriceHistory) and B.) the price that would have been in effect between every January 1st after the first effective date and now.

 

[PNGBill]

When you talk of a Price Chart you need to understand the difference between Data Stored (Tables) and Data Displayed (Forms and Reports).

If your Client wants a Display of all products with there price as at 1st January each year then this should be sql'd (queried) for him or her on demand ie, it does not need to be Stored on a table.

Your tblPriceHistory should be able to supply the data you require. There may well be better ways to handle this issue (tblPriceHistory) but if it works then that is all you need.

Query tblPriceHistory for all prices in each year. You would add Calculated Fields to your query and or format the date to be year only.
eg the price for 1st Jan 2010 is the last price greater then 1st Jan 2009 and less then 2nd Jan 2010.
If Null, then the last price.
Format the result as year only and you should have a result of all products with their price for each year which you can assume is 1st Jan of that year.
Match this to a query of all products and their Created Date.

Present this in a report and have N/A as a conditional Format or IsNull() so any product not created by that year will have N/A instead of the price.
You could have 0.00 returned for null price and Conditional Format display N/A where 0.00 is.

If you can attach a small sample database with none sensitive data I could do the sql's for you.

 

[ckell23]

Thanks PNGBill.

I certainly would appreciate any help you can give to writing that sql statement. I've attached a stripped down version of the database and form that has the current unit price history chart.

To reiterate what I need to display... the price history currently shows only a value where there is an entry in the tblPriceHistory Table. This works fine, but the issue is that we have some products that have stayed at the same price for several years. What we want to show in addition to the actual changes in price history is to also include a value for January 1 of each calendar year since the product was introduced.

Again, I appreciate your assistance.

 

[PNGBill]

I would add some fields to tblProducts eg, DateCreated, SupplierID and many more. DateCreated is important as it provides a start point for when we should have 1st January price history.

I will work on an sql now. Lets see if it takes a while

 

[PNGBill]

Copy these two sql's into your table.
qrySellPriceHistory01

SELECT tblProducts.ProductID, tblProducts.ProductName, tblPriceHistory.UnitPrice, tblPriceHistory.BegDate, [BegDate]-DateSerial(Year([BegDate]),1,1)+1 AS JulianDate, IIf([JulianDate]>1,Format(DateAdd("yyyy",1,[BegDate]),"yyyy"),Format([BegDate],"yyyy")) AS ChartYear
FROM tblProducts INNER JOIN tblPriceHistory ON tblProducts.ProductID = tblPriceHistory.ProductID
ORDER BY tblProducts.ProductID, tblPriceHistory.BegDate;

qrySellPriceHistory02

SELECT tblProducts.ProductID, tblProducts.ProductName, qrySellPriceHistory01.UnitPrice, qrySellPriceHistory01.JulianDate, qrySellPriceHistory01.ChartYear
FROM tblProducts INNER JOIN qrySellPriceHistory01 ON tblProducts.ProductID = qrySellPriceHistory01.ProductID
ORDER BY tblProducts.ProductID, qrySellPriceHistory01.ChartYear;

Run the 02 qry and you will have data for your charts except... It has not yet addressed missing years between dates and does not cater for two price changes in one year, which is not an issue with your data but should be something to consider.

I achieved the Year by using Julian Date and if greater then 1 then add one year to the date and use the year only.
One of your dates is 1st Jan and that year was used. All other dates use the next year.

How to address the other two issues...
I beleive an easy way is to make the 2nd query a Make Table query and create a Temp Table.
Then have vba code to run through this temp table and create an additional record for any missed years with the same price as the last years price.
The code will start on the first year of each product and finish on the last year of each product ie, if no next record, stop.
First though, it will remove any duplicate records for a given year by keeping only the highest number JulianDate as this is the date furtherest from Jan 1st of the year before.

Have a look at what is done so far and consider your next step. May get other advice in the meantime.

I can assist further but you should keep up with this and try the next step first.

Trust the above assists

 

[ckell23]

PNGBill,
thank you again for your input. Unfortunately, we need to plot both the actual price change events that are stored in the tblPriceHistory Table AND the January 1 value for every year that the product has been in existence.

I will try playing around with your suggestion of making the qry 2 a Make Table and have this be a Temp table to be used for populating the graphic. Perhaps it's just a two step process of 1) Make the Temp table + 2) Append the Actual price history values to that same temp table...or something along those lines.

I had wanted that to be done in a Recordset that gets created on demand for the populating of the Chart and then dumped from memory.

 

[PNGBill]

PNGBill,
Unfortunately, we need to plot both the actual price change events that are stored in the tblPriceHistory Table AND the January 1 value for every year that the product has been in existence.

This should just be a variation to my queries to include the actual price data.

I will try playing around with your suggestion of making the qry 2 a Make Table and have this be a Temp table to be used for populating the graphic. Perhaps it's just a two step process of 1) Make the Temp table + 2) Append the Actual price history values to that same temp table...or something along those lines.

The Price History from your table should be able to be included in the tempTable from the satrt - see above.
Looks to me like a Form would be the controller. Have a command button to start the process which would be to run the Make Table query and then do the work on the new table and then produce your chart.
You wont see the different tasks being done.

Your form could have two buttons. One to create All Products charts and one to select a product and just chart that.

I had wanted that to be done in a Recordset that gets created on demand for the populating of the Chart and then dumped from memory.

When you create a Temp Table, the prev one is deleted. Or, you could include a line of code to delete the table when the chart is closed. This will keep your database from growing in size.

I am not sure you can just add records to a recordset without it being saved somehow.

 

[ckell23]

PNGBill,
thank you again for your willingness to help out. I was able to get everything to work through the use of several queries and dumping info into a Temp table.

I know that there are more efficient ways of achieving the same result through VBA code where the recordset is built on demand, but not stored and taking up space. That's just way beyond my capabilities to do.

One of the things I did to minimize the amount of bloating that would occur if I were to delete the contents and then re-append the entire data set every time the queries are run is to set up the Temp table with Key fields on ID & Date. Appends that try to add data that is already there just don't get added and only new values would go in.

I'm going to move on for now. If anyone else has suggestion for how to handle this more efficiently, I'm certainly open to learning more.