Update main table from another table

[Steve Goode via AccessMonster.com]

I am fairly new to this so I am not sure of the approach that I should take. I have a table that has approximately 30,000 records in it. Each record has an automated key field, 3 unique codes that refer to the product, a description for the product, a price for the product, an effective date and an end date. The effective date and end date allow me to have a query so that customers can search on the unique code with a date in the past and see what the price was at that point in time.

I am now receiving a small table on a weekly basis that has the same fields but will contain a new price for the product and a new effective date. I need to search the main table for the the unique code, add an end date for the item, and then create a new record with the new price and the new effective date and all of the other descriptive information.

This is a system that I have inherited, how should I approach this?

Any help would be greatly appreciated.

Thanks
Steve

 

[Jeff Boyce]

Steve

It sounds like you've identified two steps. One involves "end-dating" an
existing row, where the "master" table matches the "import" data on the
three (unique) codes. This can be handled with an update query. You could
build a new query that joins the two tables on the three fields (codes).
Then you could convert this to an update query, updating only the end-date
of the matching "master" row(s) to today's value (you could use the Date()
function to get this).

The second step would be appending all the "import" rows into the "master"
table, using an append query. From your description, you could append the
codes and "begin" date.

I'm a little unclear about the "product description" though. If the product
description for a unique product (?3 unique codes) doesn't change, it
doesn't seem like you'd need to re-record that every time you update the
prices/date range. Instead, consider a "lookup" table that holds the
product description associated with each unique 3 code combination. That
way, you'd only need to update the price and begin/end dates...

--
Good luck

Jeff Boyce
<Access MVP>

I am fairly new to this so I am not sure of the approach that I should

take. I have a table that has approximately 30,000 records in it. Each
record has an automated key field, 3 unique codes that refer to the product,
a description for the product, a price for the product, an effective date
and an end date. The effective date and end date allow me to have a query so
that customers can search on the unique code with a date in the past and see
what the price was at that point in time.

I am now receiving a small table on a weekly basis that has the same

fields but will contain a new price for the product and a new effective
date. I need to search the main table for the the unique code, add an end
date for the item, and then create a new record with the new price and the
new effective date and all of the other descriptive information.

 

[Steve Goode via AccessMonster.com]

Jeff

Thanks for the quick reply. The three codes are all unique but are utilised by different government bodies to refer to a particular drug. The description is also unique to the codes. It does meant that I create a separate record for each occurrence of the drug (identified by the unique code) with different price end and effective dates. From what you have said, I guess it might have been more efficient to have the code and description in a table and linked to the price change records?

As I am fairly new to Access (I now have to go and work out how to build the queries that you have suggested ) I think that re-jigging the database structure will be a little difficult for me at the moment, but might be a worthwhile exercise in the future.

As often happens in companies, you end up being given tasks that you are not equipped to do, so a big thanks to support sites like these and helpful people like yourself Jeff.

Cheers

Steve