Update Table 1

[Maine]

I'm using Access 97. I have a table which is actually a list that we purchase of potential customers. We add a field to the table and fill that field with data as it pertains to the records in the table. We get an updated list each quarter reflecting changes, additions and deletions. I want to update our current list with this new data and I want these new records to have the added field and the data in the field if it was in the previous version and then we will just have to fill in that data for any new records. I'm sure this is a basic question but for the life of me I can't figure it out - it's probably too basic. I think it is done with an update query but how?

 

[Tim1]

Hiya,

Assuming that your new information is Table 1, and your old information is Table 2:

You need to add your field to Table 1 first

Then, create a query where you join Table 1 to Table 2 using the company name, or reference number...anything that is unique

Then put the new column you have added in table 1 is as the update field (same as to select, just make sure that you select query type update) and then in the Update To box, put the table 2 name,".", then the field name.

Then run the query.

HTH

 

[Maine]

The second part is what I am having the trouble understanding. Do you mean create an update query, add both tables, then pull the same unique field from both tables down to the query? (Slight problem I think: No field has unique data - there are instances of multiple data in every field.) Comprehension was never a problem before Access - honest.

 

[Tim1]

No, you don't need to pull both fields down to the query, to create a join, just click on one field on the first table, and drag and drop it to the field in the second table you want it joined to, Access will do the rest.

Alternatively, if your confident with SQL, you could program it:

UPDATE Table1
SET Table1.NewField = Table2.Field
FROM Table1,
Table2
WHERE Table1.JoinField = Table2.JoinField;

You would, obviously, need to replace the table names and fields with those you have in your database.

As for the fact that there is no unique field, that makes the update very difficult to do automatically, is there no combination that is unique? You can join on as many fields as you need to in order to make the whole join unique.