Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Table after Updating Another Table (2000)

    Okay, I'm working on another database for someone, and it seems to work fine except that they need to change a bunch of prices and they have entered several purchases already. What I want to know is...can you update all the entries in the Entries table after you change the prices in the Modules table?

    Each entry (in the Entries table) is made up of Date, School Name, Student Name, P.O., Grade Level, Course Name, Module Name, Quantity, Unit Price, and Course Type. The Modules table is where the Module Name and Price (in the Entries table) come from. Now they want to update a whole bunch of prices in the Modules table. But they have entered a whole lot of entries already with all the old prices. Can they somehow update the prices in the Entries table from the Modules table without having to go into the Entries table and change them one-by-one???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Table after Updating Another Table (2000)

    Should the Unit Price in the Entries table always be equal to the Price for the module in the Modules table? Or can the price in the Entries table be modified, for example as a result of a special discount, or something like that?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Table after Updating Another Table (2000)

    They should be always equal to the Price in the Modules table.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Table after Updating Another Table (2000)

    Hi Jennifer,

    In that case, there is no need to have a UnitPrice field in the Entries table, since you can look it up in the Modules table. Having a price field in both tables is superfluous, and can lead to errors - as you found out, the price in the Entries table doesn't get updated automatically if the price in the Modules table changes.

    To display the price in forms and reports, you can create a query based on both tables, with a join on the appropriate fields. There should be a unique index (for example the primary key) on the linking field in the Modules table, otherwise the query won't be updateable.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Table after Updating Another Table (2000)

    But they might update their prices several times a year. They have a Billing Report that they run three times a year. I have set up three Billing Report Queries--one for each billing period. If they update the prices, I just want it to effect the entries made in that billing period, not any periods previous. How could I do this?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Table after Updating Another Table (2000)

    Ah, new information that changes the whole situation!

    There are several approaches you could take:

    - Store the price in the Entries table after all. When prices are updated, run an update query with a where-condition on the Date field in the Entries table to select only those records that are recent enough to need the price update. This is probably the easiest solution to implement, but it won't win a price for normalized relational design.

    - Add a date field to the Modules table, and set it to the date a price becomes effective. There would be multiple records for each course: each time the price for a course gets updated, add a new record. You would get the price for an entry in the Entries record in a query that matches the date in the Entries table with the date in the Modules table. More normalized, more work.

    - Create an extra table with price and date info for each course. Similar to the second option, but even more normalized, and unfortunately also more work.

Posting Permissions

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