Preserving price history in a DB

[Jaafar Tribak]

Hi,

I have a table as part of a database. this table contains fields with info about products to be sold ( includingcurrent unit price).

As part of a query/report I am able to get the Total amount sold ($) to date for each product. The problem arises when I have to update the unit price for a certain product. When I run the report to get the Total amount sold to date for that product, I get an erroneus figure because it is based on the new updated current unit price and ignores the impact of any previous unit price(s).

Any idea how to overcome this problem ?

I hope I explained this clearly.

Regards.
Jaafar.

 

[Andrew Fergus]

Hi Jafaar

It sounds like the total value sold is based on the calculation of total units sold mutlipled by the current unit price. This is ok if the price never changes, but as you have found out, when the price changes then the total value sold becomes meaningless.

If you have access to the total units sold, do you also have access to the original values that were sold? It depends on your data structures but I expect you would get this from one of the transaction tables. If you can access the historical transactions, the total value sold will be the sum of the extended values for each transaction, grouped by product.

If this is not possible then there will be a way around the problem - a little clunky (and against the principle of a relational database) but it would involve storing the total value sold against each item. You would initially populate this field based on the old price multiplied by the units sold, and then update this field (i.e. add to it) each time an additional item was sold. It will require a bit of set-up with queries and macros etc and is a solution I am not 100% comfortable with. There would be better integrity if you have access to all of the previous transactions.

HTH, Andrew.

 

[XLGibbs]

Create a separate table that has the necessary primary key and product code information and price information, and include a data field.

When you create the table, include a new column for a date record
Date:Format(TODAY(),"mm-dd-yy")

Once that table is in place, you could run "append" queries to add the values in the price column to this table you have created (obviously there will be less total columns)

The append query would append all records where the date field in the destination table (the one you created) is NULL. This would prevent overwriting any prior information.

Then when you run your query, you would include this new table with your links, but you would need to link on date and have the criteria do a Iff statement to determine which Price to bring in....

I think you get the idea...I can help more if you think this will fly for what you intend to do......essentially the idea is to link a date field to the price field (and store the old prices) so that new checks can factor in the price based on the date. The date fields would have to be formatted the same....or it won't work.

 

[agilder]

Jaafar,

You need to develop a history in your unit pricing so that you distinguish between product 'Boomerang' sold today at price B and product 'Boomerang' sold yesterday at price A. However you know this.

How I did it:

1. Create three tables SALES, PRODUCTS, PRICES with the following columns

SALES table: ID (Key), PRODID (Link), QUANT, DATE
PRODUCTS table: ID (Key/Link), PRODName
PRICING table: ID(Key), PRODID(Link), BEGIN_DATE, END_DATE, PRICE

2. Link the three tables by PRODID

3. Create a query either using

SQL:

SELECT SALES.SALES_DATE, SALES.SALES_PRODID, SALES.SALES_QUANT, PRICES.PRICES_VALUE, [SALES_QUANT]*[PRICES_VALUE] AS Expr1
FROM (PRODUCTS LEFT JOIN PRICES ON PRODUCTS.PRODS_ID = PRICES.PRICES_PRODID) INNER JOIN SALES ON PRODUCTS.PRODS_ID = SALES.SALES_PRODID
WHERE (((PRICES.PRICES_DATE_BEGIN)<=[SALES_DATE]) AND ((PRICES.PRICES_DATE_END)>=[SALES_DATE]));

Design View:

Field Table Show Criteria
DATE SALES Y <None>
PRODID SALES Y <None>
QUANT SALES Y <None>
VALUE PRICES Y <None>
DATE_BEGIN PRICES N <=[SALES_DATE]
DATE_END PRICES N >=[SALES_DATE]
Expr1: [SALES_QUANT]*[PRICES_VALUE]

That should do the trick. It is all in the data model. Hope this helps

Kind regards

Andrew

 

[Jaafar Tribak]

Thank you very much for the various alternatives guys .

So far,I have only focused on andrew's solution (3rd) as it contains more specific steps/code.

Actually,it took me quite a while to understand the logic behind the whole design specially the relationship between the Products & Pricing tables( One to Many). Most confusing is the fact that within this relatiionship, the ProductID in the Products table is also a Foreign key !

By the way Andrew, don't you think that the Princing ID Key is redundant and should be eliminated ?

I am new to Access & I must say that the Design stage of Database building is probably the bit I find most difficult specially when there are many tables involved.I now realise how much easier & intuitive it is to work with Excel as you normally deal with fewer tables/relationships.

Regards.

Jaafar.

 

[Norie]

Jaafar

Why is the ProductID in the products table a foreign key?

 

[Jaafar Tribak]

Jaafar

Why is the ProductID in the products table a foreign key?

As Andrew suggested : PRODUCTS table: ID (Key/Link), PRODName

What I meant is that the linking flow goes from the Products table(The One table) towards the pricing table( The Many table) through the Product ID and not the other way around. Maybe I should have called it a Link key instead .

Thanks.

Jaafar.

 

[Aladin Akyurek]

A minimal Orders DB...

PRODUCTS(P#,Description)
CUSTOMERS(C#,Cname,Caddress)
ORDERS(O#,C#,Odate)
ORDER DETAILS(O#,P#,Qty,Price)

Keys are bolded.

The table ORDER DETAILS specifies the price that is applied at the time O# recorded.

If so desired, PRODUCTS can be extented with an additional field Suggested Price. Also, as frequently asked for by accountants, ORDERS can be extended with an additional field OrderTotal.

 

[Jaafar Tribak]

A minimal Orders DB...

PRODUCTS(P#,Description)
CUSTOMERS(C#,Cname,Caddress)
ORDERS(O#,C#,Odate)
ORDER DETAILS(O#,P#,Qty,Price)

Keys are bolded.

The table ORDER DETAILS specifies the price that is applied at the time O# recorded.

If so desired, PRODUCTS can be extented with an additional field Suggested Price. Also, as frequently asked for by accountants, ORDERS can be extended with an additional field OrderTotal.

Thanks Aladin for the input,

The main problem with this appraoch is when the user wants to enter the current unit price in the PO. Because this unit price changes over time , the user will need to first retrieve the correct uptodate price each time for each product every time a Purchase order is issued. That can add alot of retrieving work & increase user input errors.

Andrew's approach however ,requires the input of the current price in a Pricing parent table only once and that is when the price changes.


Regards.

 

[Aladin Akyurek]

A minimal Orders DB...

PRODUCTS(P#,Description)
CUSTOMERS(C#,Cname,Caddress)
ORDERS(O#,C#,Odate)
ORDER DETAILS(O#,P#,Qty,Price)

Keys are bolded.

The table ORDER DETAILS specifies the price that is applied at the time O# recorded.

If so desired, PRODUCTS can be extented with an additional field Suggested Price. Also, as frequently asked for by accountants, ORDERS can be extended with an additional field OrderTotal.

Thanks Aladin for the input,

The main problem with this appraoch is when the user wants to enter the current unit price in the PO. Because this unit price changes over time , the user will need to first retrieve the correct uptodate price each time for each product every time a Purchase order is issued. That can add alot of retrieving work & increase user input errors.

Andrew's approach however ,requires the input of the current price in a Pricing parent table only once and that is when the price changes.


Regards.

That shouldn't be an issue. If you want to, you can expand the db with a pricing table. The advantages is that it enables you to reconstruct the price history per product and allows you to quote a different price per customer. Note also that the db is in 3NF.