Tables Update

[arthurbr]

I regularly receive an excel spreadsheet which is updated each time ( records changed or added).
Some fields have duplicate values.
I have designed the original table using a make table query ( based on the linked spreadsheet) and then applied the Table Analyzer to the fields with duplicate values.
So I end up with a Main Table related to " Subtables " with the duplicated values.

But what do I do when I receive the updated XL sheet ?
Do I have to do it all aover again , or is there a way to have everything done automatically.
Many Thx for your help

 

[jksmi]

Hi,

rather than use a make table query each time try this...

1. either...
create a new table in design view with a primary key
- or -
copy an existing table, paste as structure only and then edit in design view to add a primary key.

2. create an append query to append data from your spreadsheet to your new table. Any duplications will not be appended (say there are 3 duplications, the first record will be appended, the next 2 will be discarded as they are duplications of the first)

3. if necessary create a delete query (DELETE * FROM MY_TABLE) to clear out data from the last before running the append query.

HTH, Jamie
FAQ219-2884

 

[dawnd3]

Hi Arthurbr,

You can set up a macro or VBA modual that imports the spreadsheet in to the database and then runs a series of update or append queries to get the data updated properly into the tables and subtables. You just have to really think out each step to make sure that the end results are what you want. If you are only appending the new data and no updating, you can have the existing tables delete all records (with a delete query) before adding the new records. You can do all of this with queries and then tie it up neatly with a button that runs a macro or VBA code.

Dawn

http://www.calliopeconsulting.net

 

[willir]

ArthurBr

I guess the question is what do you want to do if you have a duplicate? Ignore it, or use the contents to update the existing data? What is being duplicated - the entire record or just key?

If you are not concerned about the "version" of the data, then import the data into a temp table and then use an append query. Provided you have your indexes set correctly to prevent duplication, then the duplicated data will generate an error.

You have not provided much on details, so I will use a hypothetical example:

Suppose you have a customer database of products. You don't need to duplicate the products - you already have the product codes. Your product table has a unique index set for the CustomerCode + ProductCode. The append query will fail on duplicate records.

Now suppose you have a prices for the products - this time you want to update the new prices. Now you want to insert the new products + prices, or update the prices for existing products. I guess one approach would be to use an append query and then an update query.

I would personally perfer to use VBA code.
- does the import record exist ?
-- if yes, update the price
-- if no, insert the new record

Richard