Results 1 to 5 of 5

Thread: Help me please

  1. #1
    Join Date
    Feb 2005

    Exclamation Unanswered: Help me please

    I am learning Access, and I am creating a database for my company.
    This database has to quote a process. A process is when we have to make a part, and we use several machines to perform this.
    I have a table with the price of the machines that we use in some process.
    The price of each machine is composed of the summation of 17 items.
    In order to make a quotation for a process I have to retrieve the information from the table Price.
    All of the 17 items, some of them are going to change, and some will remind the same.
    The problem that I am facing is that when I change the price in the quotation query, it
    changes in the Price table.
    How can I retrieve the information that I need to perform the quotation without changing the information?
    in the Price table?
    And save the information of the quotation in another table or query?
    Thanks for your help.

  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Just run a Select Query that wont change any of the values in your tables

    You must be running an Update Query which is changing the values in your tables
    but its hard to be sure
    Could you post your DB and someone will take a look at it for you

  3. #3
    Join Date
    Feb 2005

    I already did that

    I create and update query, but did not work.
    I am going to attach my database.

    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    it's many years since i made a quote/order system, but i do remember that there were many many obstacles!

    i didn't run your .mdb but i did take a look at it.

    your quotation table should NOT be related to the price table (as an extreme position i would say that your quote table should be related to as little as possible)

    when making a quotation, you use the price table to look up a price of course, but you copy the raw price (...maybe together with other stuff from your price table) from price table to your quotation line.

    any manipulation of price you do in the quote should NOT attack even the copy of the raw price (...or the maybe stuff from price table) in your quote line, but should have separate modifier-fields (discountPercent etc) that you apply to the raw price when you generate the report=quote. the copy of the raw price must remain intact in the quote line.

    (the argument for maximum unrelated copy data in the quote: you make a quote to "Smith Heavy Industries", they change name to "Smith Racing Motors". now you have the challenge of
    -- either updating your master customer record [so you can no longer reproduce your quote accurately]
    -- or creating a new master customer record [so you cannot tie your old quotes to the "new" customer]
    ...or having unrelated copies of customer, address, etc etc in the quote as well as customer id (name updated) [so your quote still prints accurately, and you can still tie it to the customer's new entity])

    with your relationships as they are, not only will manipulating the raw price in the quote line risk screwing up the pricelist, but a 5% price increase tomorrow will screw up your records of all the still-valid quotations you send to customers today! that quoted price must be completely divorced from the price list to protect both of them.

    additional comment:
    for audit purposes, you should probably date or version stamp each price table entry - this data needs to be in your quote line also so you can work backwards to where the raw price came from.
    when updating a price in the price table, either mark the record as replaced (boolean isReplaced) and lookup prices using
    WHERE isReplaced = False
    or move replaced prices to an archive table so you can still find them later to explain where the quote came from.

    best of luck!!!
    i can't even begin to mod your mdb since yours is in bound-world and i live in unbound-world.

    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2005

    Thumbs up Thank You !!!


    I am going to follow your instructions, thanks a lot

Posting Permissions

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