VBA Loop Update Query -Normalization

[jfer]

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

 

[randy700]

Why would you want to store the same company name in both tables? That sort of defeats the purpose, doesn't it? If you want the tables to be related, you should keep the CID field as Primary Key in your company table and add it to the public table as a foreign key. The company names should only be in the company table.


Randy

 

[jfer]

Yes, but i only want to store the company [CID] in my [Public] table

my [Public] table is already populated with Company Names which i want to replace for [CID]

 

[randy700]

I think you need to use an UPDATE query.


Randy

 

[jfer]

Definitely,

th update query i use is

UPDATE [Public] SET [Public].Name = "AC Industrial"
WHERE Public.Name="7";

i want to macro this or use VBA

 

[randy700]

SELECT Public.Name, CNAMES.CID, CNAMES.CNAME
FROM Public, CNAMES
WHERE [red]Public.Name=CNAME[/red]

SET [Public].Name = "[red]AC Industrial[/red]"
WHERE Public.Name="7";

If your query produces results such as
P.Name CID C.Name
CO1 1 CO1
CO2 2 CO2

Where does the "AC Industrial" come from?

Randy

 

[jfer]

Thank you for helping me with my code

the "AC INDISTRIAL" is a company name , whose value under normalization is 7

it comes from my [public] table

MY [CNAME] table already has a company name and cid

i want my update query to change the company names in [public]

to the cid value from my [cname] table, is a match between both tables exist

 

[randy700]

How about...

UPDATE tblCNames INNER JOIN tblPublic ON tblCNames.CName = tblPublic.Name SET tblPublic.Name = [tblCNames].[CID]

Randy

 

[monbois]

I've done this a hundred times. It'll work as long as the CID is sequential from 1 (or some other starting point) to whatever without any missing numbers.

If that's the case, then what you need to do is set up your Company Name and ID values as VARIABLES so the actual value can change as you LOOP through the records. You want to look up the Company Name and new ID Number in the Public table (you should really adopt the Hungarian naming method for your database elements, by the way, such as starting all tables with t_* and all queries with q_* and so on). The loop will hook onto the ID number (which I imagine you created from an AutoNumber field?) and find the matching Company Name as the number increases. Here's what it looks like (copy/past to the On Click even on a button in a form):

' SET VARIABLES
'---------------
Dim i as Integer ' Loop Number Value
Dim CoName as String ' Company Name as Text
Dim CoID as Long ' Company Number as Long Value
Dim CoRexMax as Long ' Maximum Number of Company Records

CoRexMax = DCount("*","CNAMES")

i = 0

' Loop thru all company records
' -------------------------------------------------
Do Until (i = CoRexMax)

i = (i + 1)

' Get Company Name
' -------------------------------------------------
CoName = DLookup("[CoName]", "CNAMES","[CID]=" & i)

' Replace Co. Company Name with new Co. ID #
' -------------------------------------------------
DoCmd.RunSQL "UPDATE [Public] "
"SET [Public].Name = '" & CoName & "' " & _
"WHERE Public.Name='" & i & "';"

Loop

'UPDATE [Public] SET [Public].Name = "AC Industrial"
'WHERE Public.Name="7";

I do not have Access open right now so you may need to check on the formatting of the DCount and DLookup functions (just highlight the entire word in VBA and hit F1 and Help will open to that topic with examples). The code should loop through each Company Record and replace the Company Names with the Company IDs in a flash. Let me know if it works. If not, I can help you tweek the code to fine-tune it in no time. GOOD LUCK!

 

[monbois]

I saw some goofs after I submitted the sample code. Use this instead:

' SET VARIABLES
'---------------
Dim i as Integer ' Loop Number Value
Dim CoName as String ' Company Name as Text
Dim CoRexMax as Long ' Maximum Number of Company Records

CoRexMax = DCount("*","CNAMES")

i = 0

' Loop thru all company records
' -------------------------------------------------
Do Until (i = CoRexMax)

i = (i + 1)

' Get Company Name
' -------------------------------------------------
CoName = DLookup("[CNAME]", "CNAMES","[CID]=" & i)

' Replace Co. Company Name with new Co. ID #
' -------------------------------------------------
DoCmd.RunSQL "UPDATE [Public] "
"SET [Public].Name = '" & CoName & "' " & _
"WHERE Public.Name='" & i & "';"

Loop

 

[jfer]

Thank you both for your help

i tried randy700 approach, and it worked just fine

i sure as hell didn't know it would be so easy

i just saw your cod today monbois

you created an accumulator, which counts the number of records, which then iterates and performs and update query when the values match

Cool

I appreciate the post frmo both of you

JFER