Results 1 to 4 of 4
  1. #1
    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. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    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 .
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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)
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by sirrip View Post
    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.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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