Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2010
    Posts
    5

    Unanswered: How to 'fix' a value of a record when using it to make new ones

    Dear all,

    I need some help on my database. It's used for administration of membership of my scout-group. Furthermore, invoicing of membership-fee is done with the db.

    My database has the following tables: members, products, orders.
    'Members' contain names and adresses of our member.
    'Products' contain the products we use over and over again (e.g. a record 'membership fee january' and 'summercamp' exist) and have a unique identifier and price attached to each record (within the table).
    'Orders' contain the order-ID (the unique identifier), member-ID,
    product-ID, a date and a field to mark if the order has been paid. This table has a relationship to Members and Products.

    By using a form I can add records to the 'Orders' table by selecting a member in combination with a product. Via a report an invoice is drawn up with the members adress, the product(s) and price.

    Now comes the problem: when after eg. a year I change the price of one of the products, all existing records in the 'Orders'-table also have a new price (they simple follow the relationship and take over the new price). I want to have existing recors in the orders table to keep using the original price of the product (the price on the moment the order was created).

    To make it more complex I want to use standard access functionalities (and not VBA). The db will not always be maintained by experienced users so the KISS (keep it simple & stupid) principle should be taken into account.

    Your help is highly appreciated!

  2. #2
    Join Date
    Oct 2010
    Posts
    51
    You may need to treat the new product price as a new product. Add a field to the product table that stores a yes/no value and name it OutDated or something to that affect. Then in your query(I'm assuming you use a query with a drop down box or list of some sort to choose which product) to only show the records where OutDated is 0.

    Something else that I thought of but am not sure if it will affect your outcome. Check the relationship designer and see if the Enforce Update, Enforce Delete checkboxes are checked when you look at the properties for the JOINs.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In this case the price is an attribute of the Order. To achieve what you want you should record the price of the product within the order (essentially preserving the price at the time of order).
    A more sophisticated method is similar to bdabaum is suggesting, however instead of a boolean column use an effective_to date date column (you can also have an effective_from date column too, that will make querying easier).

    bdabaum - the problem with using a boolean is that Sander still does not know the price of a product at time of order if there are any outdated prices.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2010
    Posts
    5
    Thanks to you both,

    The option where I preserve the price in the order at the date of creating is to me most convenient (due to it's dummy-proof-ness). I already created the field in the Orders table but cannot find out how to preserve the price into it by using the form I use to fil the orders table (which only has dropdown for member, product and checkmark field to indicate it's paid.)

    Your second option is indeed a viable one. A to-date or numeric indicator if the product is still valid will definately work but creates more work to maintain.

  5. #5
    Join Date
    Oct 2010
    Posts
    51
    Quote Originally Posted by pootle flump View Post
    In this case the price is an attribute of the Order. To achieve what you want you should record the price of the product within the order (essentially preserving the price at the time of order).
    A more sophisticated method is similar to bdabaum is suggesting, however instead of a boolean column use an effective_to date date column (you can also have an effective_from date column too, that will make querying easier).

    bdabaum - the problem with using a boolean is that Sander still does not know the price of a product at time of order if there are any outdated prices.
    Good point. Thanks for pointing that out.

    I do like your idea better. While a product may have a price, you are correct. It may change whereas the price paid for the order should never change. I paid 35.00 for gas yesterday and whether the price of gas goes up or down, I still paid 35.00 yesterday. So i believe storing it with the Order would be much more accurate.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I have not build an Access form for years, but I think you could simply put this in a hidden textbox on the form bound to the order price column:
    Code:
    =DLookup("Price", "Products", "ProductID = " & Me!txtProductID)
    Obviously change the names to your correct column, table and textbox names.

    EDIT - this expression should only appear on a form for creating orders, not for editing or viewing them, since the latest price would then be retrieved and overwrite the value.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2010
    Posts
    5
    I feel terribly sorry but cannot get your code into the field I've created in my form. I'm not used to enter code. This is what I do:

    Create a field, build an event -> expression. Copy in your code and replace the expression and domain with the correct names. Access keeps telling me there's a syntax error. Also when I cut away the criteria (so it becomes a very basic dlookup) the error remains.

    It seems I"m lacking the basics (which is true to be honest). Any clue what's going wrong?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Put it in the control source.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2010
    Posts
    5
    I did, but still get the same error.

    EDIT: replaced [,] with [;] which at least causes the error. Not sure why.

    EDIT2: my code is now:

    =DLookUp("Tarief";"Product"). The last part I don't understand. I guess it something like =DLookUp("Tarief";"Product";"Product# =" & me![????])
    "Tarief" is the field i'm looking for, the table is correct. The unique ID of the product-table is "Product#". after that it's not clear to me what to fill in.
    Last edited by Sander_Kaa; 11-03-10 at 10:50.

  10. #10
    Join Date
    Nov 2010
    Posts
    5
    Quote Originally Posted by pootle flump View Post

    EDIT - this expression should only appear on a form for creating orders, not for editing or viewing them, since the latest price would then be retrieved and overwrite the value.
    The form is first used to enter the order. In the same form also oders can be viewed and changed (marking it's paid). Is that a problem?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes.

    I suggest you start a new thread. I can help with database design (which is how this started) but I am not so good with forms. Others will be better placed to help.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Oct 2010
    Posts
    51
    Quote Originally Posted by Sander_Kaa View Post
    I did, but still get the same error.

    EDIT: replaced [,] with [;] which at least causes the error. Not sure why.
    WHY did you replace [,] with [;]? This will not work

    Quote Originally Posted by Sander_Kaa View Post
    EDIT2: my code is now:

    =DLookUp("Tarief";"Product"). The last part I don't understand. I guess it something like =DLookUp("Tarief";"Product";"Product# =" & me![????])
    "Tarief" is the field i'm looking for, the table is correct. The unique ID of the product-table is "Product#". after that it's not clear to me what to fill in.
    the Me![????] control value must also match the datatype of the fieldname you are filtering against. If your criteria is retrieved from a control named combobox1, then Me.combobox1.Column(x) where x is the order it appears in the drop down -1.

    ---
    ---Sorry third parameter is Optional. My bad.
    Last edited by bdabaum; 11-03-10 at 16:51. Reason: Cause I'm wrong....

Posting Permissions

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