Hello,

i have begun to normalize various excel tables into an Access Database

i have a normalized company name table named

[CNAME] which is made of PK[CID] and [Name]

i also have another table which is [Public] with a [Name] field

The [Name] field has Company Names would be received from my [CNAME] table

i made an sql query "Public Names" that states

SELECT Public.Name, CNAMES.CID, CNAMES.CNAME
FROM Public, CNAMES
WHERE Public.Name=CNAME

in order to sync which customer names match from both tables, and the [CID] from my [CNAME] table

i have been using a manual update query to change the [Name] in [Public] by the [CID] that matched from my "Public Names" query

i would like to develop a VBA sequence that updates the [Name] field if there was a match on the "Public Names" query

any pointers?

Thank You

Jfer

Any one?

a sequenced update ?

Why would you want a VBA sequence when it can be done with a single query statement?
And why do you want to put redundancy in your normalised database?

Excellent question

my normalization began with existing data from an MS Excel File with vartous worksheets

the information from one sheet to another is independent

the only dependent field between both are company names

which i want to change for numbers, which lookup up the company name from my [CNAME] table

You need to use an explicit join when working with Access. The old style non-explicit join that you used will result in a cartesian product being created. The criteria will then winnow that down to the result set you would have gotten with a normal inner join. The difference is that the result set created by the cartesian product will not be updatable.

1. Create an inner join that joins the two tables on the name field. BTW - Name is a poor choice since it will cause conflicts when working with VBA given that all objects have a Name property. Avoind any name that could possibly be the name of a property, method, or function. You can find lists of reserved words in help.
2. Turn that query into an update query and update the reference key field in one table with the value of the primary key from the other.

Thank your for all your help

the code i needed to solve my problem is here

http://www.tek-tips.com/viewthread.cfm?qid=1397422

i appreciate all your efforts

JFER