# Thread: Quick Question: Preserving Data Integrity

1. Registered User
Join Date
May 2010
Posts
23

## Unanswered: Quick Question: Preserving Data Integrity

Hey all.

So I have asked a variation of this question here before but never got a good explanation. I wanted to clear the slate and try again.

Hypothetical Question:
I am creating an Access database for a local shop that sells bubble gum. I have no problem creating a M:M relationship between my PRODUCTS table with my ORDERS table.

One flavor of bubble gum, Bubble Tape, costs a customer \$5, so I enter \$5 in to the PRICE column of the PRODUCTS table.

At the end of the month I run a query to create a report for total Bubble Tape sales. The query creates does the job of multiplying PRICE x QUANTITY to come up with the total sales figures. Let's say for the month of January I had 20 orders, totaling \$100

Now... let's say I decide to lower the price of Bubble tape to \$2.50 in February. Let's say I again have 20 units sold in Feb.

The problem is here: when I run the same query now to find total sales across both months (at the end of the February) my total Quantity sold for both combined months is 40 and and the price is \$2.50 totaling net sales of \$100 when in reality - I brought in \$200 of sales over the two months.

My question is: How do I keep the integrity between a product and the price at which it was sold.

Only thing I can think of is to create a new PRODUCT record (Say, "Bubble Tape2") when there is a price change.

2. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
You have two options here
1. Create a separate table to hold prices with a start date field and an end date field, indicating when the given price went into effect and when the given price became obsolete
2. Actually adding a field to the Orders table to hold the price of an item at the time the order was placed, and do the calculation based on this

Because of having to query the price table every time you need to do the calculation, as in # 1 above, and the time involved, most Access developers I know use the 2nd option and store the unit cost .

3. (Making Your Life Easy)
Join Date
Feb 2004
Location
New Zealand
Posts
1,482
Snap to Missingling
had the same problem with a database that was written by and X emp

my fix was

add soldprice to the orderline
them I created a event afterupdate of the QTY/productcode feild do a
Me.soldprice = dlookup("Price","Product","Productid=" Me.productid)

4. Registered User
Join Date
May 2010
Posts
601
Originally Posted by sirrip
Hey all.

So I have asked a variation of this question here before but never got a good explanation. I wanted to clear the slate and try again.

Hypothetical Question:
I am creating an Access database for a local shop that sells bubble gum. I have no problem creating a M:M relationship between my PRODUCTS table with my ORDERS table.

One flavor of bubble gum, Bubble Tape, costs a customer \$5, so I enter \$5 in to the PRICE column of the PRODUCTS table.

At the end of the month I run a query to create a report for total Bubble Tape sales. The query creates does the job of multiplying PRICE x QUANTITY to come up with the total sales figures. Let's say for the month of January I had 20 orders, totaling \$100

Now... let's say I decide to lower the price of Bubble tape to \$2.50 in February. Let's say I again have 20 units sold in Feb.

The problem is here: when I run the same query now to find total sales across both months (at the end of the February) my total Quantity sold for both combined months is 40 and and the price is \$2.50 totaling net sales of \$100 when in reality - I brought in \$200 of sales over the two months.

My question is: How do I keep the integrity between a product and the price at which it was sold.
I actually use both the methods that Missinglinq suggested together. I used a price history table for the product. I also store the sale price with the quantity sold.

I think what myle is trying to say is they use Missinglinq's option #2 (store the price sold with the quantity sold.

*** Note: This will also be true for Sales Tax/VAT rates. ***
Last edited by HiTechCoach; 05-20-10 at 00:45.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•