Update query required?

[klr]

I have an existing database with table containing information for
approx 400 customers as follows:-

Customer ID; Contact; Company; Address and so on

I have been given a spreadsheet that contains a list of about 60
customers already existing in my database with following information:-

Customer ID; Company; Type

The type field doesn't already exist in my table.

Should I leave the spreadsheet in a separate table in my database, or
is there a way, using an update query, to add in a new Type field in
existing table, what the Customer type is?

 

[John Spencer]

You can't add a field to a table with an update query.

Can you get into design view and add the field to the customer table? Do
you have permissions/rights to do so? That is the simplest method. Once
the field is added then you can use an update query to populate it with the
value.

 

[klr]

I have now added the field required in the table design view, but am
unsure how to populate it, as not all records need to contain a value,
and I can't see how (in the update query design view) to match the
Customer ID fields and then draw through the Type.

I guess the best way to explain how I want this to work is like a
VLOOKUP in Excel!

Kim

 

[John Vinson]

I have now added the field required in the table design view, but am
unsure how to populate it, as not all records need to contain a value,
and I can't see how (in the update query design view) to match the
Customer ID fields and then draw through the Type.

I guess the best way to explain how I want this to work is like a
VLOOKUP in Excel!

The CustomerID should be the Primary Key of both tables.

Create a new Query. Add both tables. Join them by CustomerID.

Change it to an Update query; on the "Update To" line under the Type
field that you want to update type

[Table2].[Type]

using your own table and fieldnames of course.

Run the query.

Access can DO this... easily!... but it *is not Excel and it does not
work like Excel*; you may need to "unlearn" some things to make the
best use of Access!

John W. Vinson[MVP]

 

[klr]

Thanks, that worked brilliantly.

I have now added the field required in the table design view, but am
unsure how to populate it, as not all records need to contain a value,
and I can't see how (in the update query design view) to match the
Customer ID fields and then draw through the Type.

I guess the best way to explain how I want this to work is like a
VLOOKUP in Excel!

The CustomerID should be the Primary Key of both tables.

Create a new Query. Add both tables. Join them by CustomerID.

Change it to an Update query; on the "Update To" line under the Type
field that you want to update type

[Table2].[Type]

using your own table and fieldnames of course.

Run the query.

Access can DO this... easily!... but it *is not Excel and it does not
work like Excel*; you may need to "unlearn" some things to make the
best use of Access!

John W. Vinson[MVP]