Results 1 to 10 of 10
  1. #1
    shme is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9

    Copying Data Across Tables? (Best Practices)

    Hey guys, I have a couple of questions about my design choices in a database that I developed a while back. It is a quoting database for our company that takes allows the user to create quotes that contain a combination of our stocked products and products that we would source from elsewhere and sell only once. Our stocked products are in there own table (tbl_Products) and that table contains all the pricing info and descriptions. I have a "tbl_Quotes" table and a "tbl_Quote_items" table. On the quote creating form I have a subform that references the "tbl_Quote_items" table. The user can hit a button that allows them to search and add a product form the "tbl_Products" table. It does this by just copying the data from the "tbl_Products" table to the "tbl_Quote_items" table.



    This allowed the following features:

    1) We can manually change the price for a certain customer on any quote or add something to the description without changing the values in the "tbl_Products" table
    2) Old quotes are unaffected by any changes to the "tbl_Products" table after the quote has been completed
    3) We can manually enter in a one off item into a quote that we would not want to appear in the "tbl_Products" table as it would just clutter it up and we will very likely never sell it again.

    Is this the best way to go about this? I am being tasked to create another database for our rental products/invoices and want to find out before starting.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,845
    Based on the provided info, the only thing I wonder about is the decision to have different product tables. I get the point but would offer that your products table could also be one that had a field to identify non stocked items. Where I worked, RM's were stocked items; DR were direct buys (in your case you might purchase a non stocked item and re-sell it); PH's were phantom parts (a unit made up of many other parts but not stocked per se) and maybe others that I don't recall. I'm not suggesting you would use any of these, just that such a field would allow you to easily filter from tblProducts as necessary. Then you don't have to repeat common fields such as QTY or UOM (unit of measure) and so on between tables. Normalization dictates that an entity should have one table, be that product you stock or otherwise, because it's all product.

    The rest of it sounds OK as long as I understand what you wrote. There's plenty that you haven't asked about, such as whether or not you should store calculations such as total cost on a quote or invoice. That is usually ill advised.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    shme is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    If I were to link the "tbl_Quote_items" directly to "tbl_Products" how would I preserve the original details. For example, we sold a item in 2010 and the selling price was $10 but now in 2020 we sell it for $40. When I go and load up that quote or invoice from 2010 I would want it to show the original price of $10 rather then the new current price of $40.

    For the calculations, totals are not stored in the tables, they are calculated in the forms and reports from the line prices and quantities.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,845
    Sales isn't my area of expertise so this is just an educated opinion based on database principles.

    When you build a quote, you always use the current price (and apply any discounts or surcharges). The price you sold it at 10 years ago or tomorrow ought to be part of the item detail records. I didn't mean to imply that you should not store the selling price if that's what you're asking because obviously if you stored a reference to your catalogue price instead it would alter all past invoices or quotes. The price isn't the calculation - it is part of the calculation. The line, invoice or quote total is the calculation. References to prices, surcharges or discounts would require you to archive older data and be able to look up that data based on matching the date of sale vs the date of a price. I don't see the point in that.

  5. #5
    shme is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    Quote Originally Posted by Micron View Post
    Sales isn't my area of expertise so this is just an educated opinion based on database principles.

    When you build a quote, you always use the current price (and apply any discounts or surcharges). The price you sold it at 10 years ago or tomorrow ought to be part of the item detail records. I didn't mean to imply that you should not store the selling price if that's what you're asking because obviously if you stored a reference to your catalogue price instead it would alter all past invoices or quotes. The price isn't the calculation - it is part of the calculation. The line, invoice or quote total is the calculation. References to prices, surcharges or discounts would require you to archive older data and be able to look up that data based on matching the date of sale vs the date of a price. I don't see the point in that.
    Thanks for your input. I have some other questions but I think I will post them separately as they are not really related.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,845
    You're welcome. Hope it helped somewhat.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,071
    Quote Originally Posted by shme View Post
    If I were to link the "tbl_Quote_items" directly to "tbl_Products" how would I preserve the original details.
    Easiest way is to have all sales registered in log table (e.g. tblInvoiceLog: InvoiceLogID; InvoiceID, ProductID, InvoicePrice, InvoiceQty, ...). Every invoice has one or several entries in tblInvoiceLog (an entry for every invoice detail row). Your query reads info from invoice log instead of products table.

  8. #8
    shme is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    Quote Originally Posted by ArviLaanemets View Post
    Easiest way is to have all sales registered in log table (e.g. tblInvoiceLog: InvoiceLogID; InvoiceID, ProductID, InvoicePrice, InvoiceQty, ...). Every invoice has one or several entries in tblInvoiceLog (an entry for every invoice detail row). Your query reads info from invoice log instead of products table.
    Okay, I think this is basically how I do it now. See the attached picture of my relationships:


    All the relationships with just dots at each end are where I copy all the data over rather then link it. This ensures that the copied data can be edited and it will not edit the source. Is this the best way of going about it? You can see the quote has 3 sections, one for purchases, one for rentals, and one for calibrations. I also copy over the customer data and the info on who prepared the quote.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,845
    No, all primary keys that need to be related should be foreign key values in another table - e.g. Customer.CustomerID links to QuoteT.CustomerID
    Tables that are only used to look up values are not always related this way. Sometimes they stand alone, sometimes the PK for (e.g. a unit of weight) is what gets stored, sometimes just the unit value.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  10. #10
    shme is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    Quote Originally Posted by Micron View Post
    No, all primary keys that need to be related should be foreign key values in another table - e.g. Customer.CustomerID links to QuoteT.CustomerID
    Tables that are only used to look up values are not always related this way. Sometimes they stand alone, sometimes the PK for (e.g. a unit of weight) is what gets stored, sometimes just the unit value.
    I think these relationships that you are talking about are only there because I use the primary keys as lookup values. On the QuoteT form, QuoteT.PreparedBy is a combobox with
    Code:
    SELECT [PreparedByT].PreparedByID, [PreparedByT].PreparedName, [PreparedByT].[PreparedNumber] FROM PreparedByT ORDER BY [PreparedName];
    as its row source. The controls afterupdate event copies PreparedName, PreparedNumber, PreparedEmail, and QuoteYear from PreparedByT to the same controls in QuoteT.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-08-2018, 02:35 PM
  2. VBA Data Validation And Best Practices
    By kd2017 in forum Programming
    Replies: 11
    Last Post: 09-08-2017, 09:35 AM
  3. Replies: 8
    Last Post: 10-13-2014, 12:19 PM
  4. Replies: 5
    Last Post: 11-26-2013, 11:11 PM
  5. Copying Data in tables
    By Hannah in forum Forms
    Replies: 4
    Last Post: 06-22-2011, 06:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums