reading from one table to populate a column in another table

[TomC]

Table A has customer code and customer name fields
Table B has customer name and phone number fields

I want to read through table B, look up the customer name in table A,
if it is there, copy customer code to a customer code column in Table
B that I will create. I could use a select statement to do this but
sometimes the entire name may not match but the first 15 or 20
characters will.

by the way, I have bought lots of Access books and have yet to see
code similar to this request where you read through one table and
possibly populate another. I have programmed in Visual Foxpro and
this was fairly easy to do.

Any recommendations for advanced access/vba books will be much
appreciated.

Thanks in advance

Tom

 

[Guest]

Lets assume that you can guarantee a match on the first 15 characters, or the
whole name if the name is shorter, and that this will uniquely identify each
name. You can execute an 'update query', e.g.

UPDATE TableB INNER JOIN TableA
ON LEFT(TableB.CustomerName,15) = LEFT(TableA.CustomerName,15)
SET TableB.CustomerCode = TableA.CustomerCode;

Remember that table or column names which contain spaces or other special
characters must be wrapped in brackets [like this].

Set operations like the above are better than iterating through the table in
code, but as far as books on advanced VBA programming in Access is concerned
then take a look at The Access Developer's Handbook by Litwin, Getz and
Gunderloy (published by Sybex). I think the last edition was for Access
2002, but that's no real drawback.

Ken Sheridan
Stafford, England

 

[Al Campagna]

Tom,
Your customer table should have a unique key field value for each record, that
identifies just that one customer. For ex. a CustID (ex. an autonumber)
Your Phone table should also have a a CustID field too (numeric, long) used to relate
One Customer to Many Phones
When you establish a One to Many relationship between the two tables, via the CustID,
the CustID updating in the Phone table is automatically handled by Access.

Say you have a Main form, based on Customers, with all the "One" Customer info, and a
continuous subform on that same form, based on Phones with the "Many" info.
The link between the two forms (Parent/Child) would be CustID.
Whenever a phone number is added to say... Bob Smith's phone list, Access
automagically applies his CustID to each and every Phone record. That's how Smith and his
phone numbers are stay "connected"

You do not need to handle the integrity between the One table vs. the Many table...
Access does that.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[TomC]

Lets assume that you can guarantee a match on the first 15 characters, or the
whole name if the name is shorter, and that this will uniquely identify each
name. You can execute an 'update query', e.g.

UPDATE TableB INNER JOIN TableA
ON LEFT(TableB.CustomerName,15) = LEFT(TableA.CustomerName,15)
SET TableB.CustomerCode = TableA.CustomerCode;

Remember that table or column names which contain spaces or other special
characters must be wrapped in brackets [like this].

Set operations like the above are better than iterating through the table in
code, but as far as books on advanced VBA programming in Access is concerned
then take a look at The Access Developer's Handbook by Litwin, Getz and
Gunderloy (published by Sybex). I think the last edition was for Access
2002, but that's no real drawback.

Ken Sheridan
Stafford, England

Table A has customer code and customer name fields
Table B has customer name and phone number fields

I want to read through table B, look up the customer name in table A,
if it is there, copy customer code to a customer code column in Table
B that I will create. I could use a select statement to do this but
sometimes the entire name may not match but the first 15 or 20
characters will.

by the way, I have bought lots of Access books and have yet to see
code similar to this request where you read through one table and
possibly populate another. I have programmed in Visual Foxpro and
this was fairly easy to do.

Any recommendations for advanced access/vba books will be much
appreciated.

Thanks in advance

Tom- Hide quoted text -

- Show quoted text -

Your suggestion worked perfectly.

Thank you!

 

[i_takeuti]

Table A has customer code and customer name fields
Table B has customer name and phone number fields

I want to read through table B, look up the customer name in table A,
if it is there, copy customer code to a customer code column in Table
B that I will create. I could use a select statement to do this but
sometimes the entire name may not match but the first 15 or 20
characters will.

by the way, I have bought lots of Access books and have yet to see
code similar to this request where you read through one table and
possibly populate another. I have programmed in Visual Foxpro and
this was fairly easy to do.

Any recommendations for advanced access/vba books will be much
appreciated.

Thanks in advance

Tom