New Database/New User/Question

S

Shrubman

Hi all,

I am building a new database and some of the table information has been
imported from Excel. I have not yet set up my relationships, so that may be
my problem, but I need help with the following:

I have a list of clients, imported from Excel by name only, and I have set
up a table in Access which applied an Autonumber ClientIDto each listing
using a new column. I have a second table that will becalling upon the Client
ID, but right now has the clients listed by name.

How do I tell access to look up the new ClientID for each name in the one
table and apply it to the field that has an actual name in the second table?

Thanks in advance,

Evan
 
J

John Vinson

Hi all,

I am building a new database and some of the table information has been
imported from Excel. I have not yet set up my relationships, so that may be
my problem, but I need help with the following:

I have a list of clients, imported from Excel by name only, and I have set
up a table in Access which applied an Autonumber ClientIDto each listing
using a new column. I have a second table that will becalling upon the Client
ID, but right now has the clients listed by name.

How do I tell access to look up the new ClientID for each name in the one
table and apply it to the field that has an actual name in the second table?
Click to expand...

An Update query will do this... with some hassles. Can you be QUITE
CERTAIN that you don't have two different people who happen to have
the same name? I know three gentlemen named Fred Brown, right here in
little Parma.

If not, what you can do is create a Query joining your table with the
ClientID (I'll call it Clients) to the other table, joining *by the
name field or fields*. Change the query to an Update query, and update
the other-table ClientID to

=[Clients].[ClientID]

The tablename and square brackets are essential.

This does assume that the names match between the tables - if you have
"Bill Jones" in one table and "William Jones" in the other, of course
it won't match.

You may need to (temporarily) create a unique Index on the name field
(or fields, ideally you should have FirstName and LastName fields) in
the Clients table for the query to be updateable.

John W. Vinson[MVP]
 
Top