Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Talking Unanswered: I know someone can help me. Data storage issues.

    Greetings everyone.

    I have a db that im working on to handle EPoS (Electronic Point of Sale) for a friend of mine. It is almost complete however i have a small hurdle to step over and can't find a solution.

    The set up is as follows.

    t_products (has fields; Name, Base Price, Markup)
    t_transactions (has fields; Transaction id, date)
    t_sub_transactions (has fields; item sold [linked with products] & transaction id [linked with above])
    q_RRP (calcualtes the RRP based on the markup and the base price)

    My problem is;

    As the price data is stored within the product profile and not with the transaction, when my client changes the price of the product all previous sales will inevitably change too to the new price and play havoc with accountancy.

    I see 2 solutions, but not sure how to go about either.

    1) t_sub_transactions pulls the price of an item from q_rrp (or t_products and runs the calcualtion) and then stores this figure permanantly, as it is at the time and does not re-calcualte it when the price is changed.

    2) The transaction (say at the end of the day) data is stored in a seperate table in an archive for accountacy purposes, and only new records are added to this table and no older data is overwritten.

    Any suggestions or ideas are welcome, just point me in the right direction....

    Thanks guys.

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    As someone who has designed several accounting packages, I like the idea of storing the price/summed value permanently. Others will tell you not to store the summed value but I VERY STRONGLY disagree with this phylosophy! I think it's just a wrong phylosophy to do this and only opens the door to problems in the future (after all, how much space does adding 1 more (currency) field take? We're not in the 1 megabyte harddisk world anymore.) I don't see a reason to store it in a separate table as this makes things a little more complicated but it wouldn't hurt to have a backup table.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    There are times to not store a calculated value, and there are times to store calculated values. In a sales transaction, the price must be stored as part of the transaction. That is because the price is very much a part of the transaction. It is something that has happened at a point in time, and the current price that is kept with the product is just that, the current price. Now, if 15 items are purchased as a part of this transaction, and the individual price and the quantity are part of the transaction, this is where some people say, don't store the total cost, and others say, no problem in storing the total cost. Because the transaction will have all the pieces it takes to calculate total cost, it does not need to be kept in the transaction. This is something that comes with the "art" side of design, rather than the "science" side of the design. Normalization rules would dictate not keeping the total of the sale in the transaction record. But, the "art" of design will sometimes say keep it in the transaction for this database, and sometimes for another database, it does not make sense. That is a database by database decision.

    One reason I have kept the total invoice price in one of my databases is because the calculation is very complicated, and it is not worth trying to duplicate all those decisions in every report or form that might show invoice totals.

    Plus, as Paul was refering to, within an accounting system, the pieces are usually not important, but the total is.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This is the third time today I've seen this question. I totally agree with both Paul and Vic! You have to look at storing a calculated value on a case by case basis. Sure, you could keep track of what the price of a widget was at this point in time, and that point in time and the other point in time, and use this to recreate the calculation at some point in the future, but that seems just seems like an awful lot of work to avoid adding one more field to a table! As Paul intimated, we no longer live in a world of 40 mb hard drives!
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    I think there is some mis-interpretation.

    My calcualted value (done in a query) calcualtes my RRP for each of my products. I do need to store this value. Permanately, as part of the transaction data.

    what i dont know is how to make my table t_sub_transactions pull the RRP from the query q_rrp and store this with the transaction. I don't need it to re-calculate itself when the price is altered either.

    Data storage in terms of size is not an issue, it's just how to go about storing the right data, andhow to get the table to save the price sold for at the time.

    thanks again

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Personally I think its pretty clear

    the price that the customer pays is a unique part of their transaction (heck its possible for someone to negotiate a special discount / price for an item). even if you are duplicating the price in all subsequent orders from the same or similar customers, in my view you must be able to accurately report actual sales value. that requiremnt is going to come ftom either the bean counters or the revenue when they want to inspect your systems for compliance with their rules.

    Storing a reference is not a flyer in my books (if you had say a table which stored price changes then you are complicating things (you need to do another join to extract the price that applied on that day and that time. You are creating a scenario where either malicious or inadvertant changes may compromise the whole basis of the system. and whatever else is done no compnay can afford to have a trading /acconting system which the users and suppliers OR customers have no faith. It also helps with the security model in as much as you can tie down writes to the invoice / statements / credit notes as write only.. ie no changes are allowed by anyone.

    The price a customer pays on their order is unique to that transaction. If you store the invoice total and not the line items then if the customer queries the invoice you are open to problems if the data has changed.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Sorry, i think the intention of the question was misunderstood.

    I'm asking fr technical help, I think i should have mentioned it form the start. Either some code or something that can help me achieve what i need.

    The [rrp] is calcualted from [base price] + ([base price]/100*[markup]) this is calculated in the query q_rrp.

    I need the value [rrp] to be stored with the item sales in the table t_sub_transactions maybe in a new field called [sold price] the only trouble isi need that value to remain the same once stored and not be affected by price changes.

    Any help would be much apprecitated.

    Thanks for the replies.

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..if you can extract the price from your products table you can place that in the sales table

    ...how you do that is up to you. In your position Id pull the price data at the same time the product data is either selected or grabbed from the products table. if you are using a combo or list box you could stuff the price data into the combo/list box. Possibly a smarter technique would be after the product ID is requested then place some code in what ever control you use to select a product. you could do that in a function supplying the priduct, the quantity and whatever (mebbe even the customer ID) and return the price.

    if you were using a combo box to selectg the product you cold put a call to that function in the combo boxes on click event, assign the returned value form the fucntion to the say bound text box for line item price
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Based on your first post, I'm thinking that you are RECEIVING at least two of these files during the transaction processing with the actual point of sale. Is this correct? Part of what you are asking for should be a little more detail of what is happening where. When we know that, then we can help you with how, because we well know where.

  10. #10
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Okay, I'll try and explain a little more clearly how i have set up the database.

    I have a table named products, this stores among things a base price, and a markup value (percent [but numeric value, the percentage is calcualted])

    Then in a query RRP the RRP is calcualted for that product, Base Price = (base price/100*markup).

    ===

    I then have 2 tables for my transaction data. Transactions (with fields ID and date) [this acts to group the transaction info.

    then a sub table, Transaction items. (with fields, transaction [linked to previous mentioned table], Item).

    =====

    In the form that handles the transactions, i have a sub form that handles the purchase items, these are lookup fields and form a ?many to one? relationship?

    =====

    As you can see the actual 'Sold For' price is not stored anywhere. I can't think of any way to make the value be stored as an at the time value. When it comes to accoutning, if the users have changed the base price then this would alter the price of all previous sales and make accounting a nightmare.

    Any advice would be great, as not sure how to solve this. Maybe im blinded by the problem??? Also if i am to grab the price and store it in a field in my sub table, I don't know how this would be achieved, I'm no coder.

    Thanks again, If this doesn't help I'll attach the DB.

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  11. #11
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520

  12. #12
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Post Thanks for taking a look

    Cheers,

    it is proably a huge mess, but it'll be be kinder on the eyes when finished.

    thanks

    dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

  13. #13
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    At what point in time is the sold for price known? As soon as the transaction is generated? Although I see no way to generate a transaction. So, if all this is a POS system, then where does this information actually come from? If it is coming from a POS device, then why does it not give you the actual sales price? Or, can you calculate the sales price the first time you get to see the transaction, then once it has been calculated, never do it again? I know you don't know how to do this, but if this is what you want, we can walk through the How-To. I just need to know what is needed and when you can know what.

  14. #14
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    when a new record in the form is started, eg, a new transaction, that i when the 'sale ' starts, and is stored. There currently is no POS device in the store im atempting to design this for (favors for firends and all that!!) so the items are pulled up from the drop down boxes in the transactions form. When finished the sale will be 'completed' when they click a button for a denomination or type one in (this is to be done).

    What he needs is to set the prices of all his items, based on the amount he pays for them (eg the base price) and then add his markup so he is always creaming a little off the top.

    I dont know if this helps you, thanks so far, i think i may be way off with this database!!!!

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

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