hudmon
asked on
How do I write an update query that will populate a field with a cross referenced text field
I have a table that has numerous activity codes ie. 90801HF, H0035, etc. I have a cross reference sheet that refers to the same codes with a different text string. For example code 90801hf is also = to 5510 in every instance and H0035 is = to 1400..... I want to create a new field that will be populated by an update query using the reference sheet codes. How?
ASKER
I have built a table with all activity codes and named it Crosswalk now I want to create a blank field in my master table called [ActivityCode2] and populate it by using an update query. How do I do it? Please be specific, I do not have a great deal of knowledge.
Hi hudmon,
This should work:
Public Sub CrossReference()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strSQL as String
Dim rstCross as New ADODB.Recordset
With rstCross
.Open "SELECT * FROM Crosswalk", cnn, adOpenKeyset, adLockReadOnly
Do While Not .EOF
' assuming that Crosswalk has two fields: the first corresponding to ActivityCode1
' and the second corresponding to ActivityCode2
strSQL = "UPDATE tblMyTable SET ActivityCode2 = '" & .Fields(1) & _
"' WHERE ActivityCode1 = '" & .Fields(0) & "'"
cnn.Execute strSQL
Loop
.Close
End With
Set rstCross = Nothing
End Sub
Just modify this code to use the actual table name and files, paste it into a module and run it (press F5).
I hope this helps.
Chuck
This should work:
Public Sub CrossReference()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim strSQL as String
Dim rstCross as New ADODB.Recordset
With rstCross
.Open "SELECT * FROM Crosswalk", cnn, adOpenKeyset, adLockReadOnly
Do While Not .EOF
' assuming that Crosswalk has two fields: the first corresponding to ActivityCode1
' and the second corresponding to ActivityCode2
strSQL = "UPDATE tblMyTable SET ActivityCode2 = '" & .Fields(1) & _
"' WHERE ActivityCode1 = '" & .Fields(0) & "'"
cnn.Execute strSQL
Loop
.Close
End With
Set rstCross = Nothing
End Sub
Just modify this code to use the actual table name and files, paste it into a module and run it (press F5).
I hope this helps.
Chuck
1. MANUAL -- "create a blank field in your table called activitycode2". go to the table view tab hilight the master table and click design. this should bring up a list of fields, type and description. on the first empty line after your fields add another name of activitycode2. then in the type column text should appear. (that's what you want) default length is 50 chars so you may want to shorten it but it isn't required at this time. save and close and you now have another field in your master table called activitycode2
2. QUERY -- "create a blank field in your table called activitycode2". Open a new query and do not select any tables. when you close the window you should see an SQL in toolbar. click it and you should have a text window. type in the following.
ALTER TABLE master ADD COLUMN activitycode2 text(50)
Crosswalk: the table of Cross Reference Information
Activitycode: string ; PRIMARY KEY
equivilant: string
Master: The table of data
{stuff} ; some key
ActivityCode: activity code string
activitycode2: blank activity code string
UPDATE crosswalk INNER JOIN [Master] ON master.[ActivityCode] = crosswalk.[ActivityCode] SET [master].[activitycode2] = [equivilant];
run the update query and it should fill in all records in master with the matched data crom crosswalk. the quivilant field should be the name of the new data in crosswalk.
2. QUERY -- "create a blank field in your table called activitycode2". Open a new query and do not select any tables. when you close the window you should see an SQL in toolbar. click it and you should have a text window. type in the following.
ALTER TABLE master ADD COLUMN activitycode2 text(50)
Crosswalk: the table of Cross Reference Information
Activitycode: string ; PRIMARY KEY
equivilant: string
Master: The table of data
{stuff} ; some key
ActivityCode: activity code string
activitycode2: blank activity code string
UPDATE crosswalk INNER JOIN [Master] ON master.[ActivityCode] = crosswalk.[ActivityCode] SET [master].[activitycode2] = [equivilant];
run the update query and it should fill in all records in master with the matched data crom crosswalk. the quivilant field should be the name of the new data in crosswalk.
ASKER
msroberts
I created blank field in MasterCode table called Activitycode2. The name of my table with the cross referenced information is called Crosswalk. I can get to the point in the query where I choose SQL and have a text window that has SELECT; already there. Again, you are working with a real novice, can you be "very" as to exactly what the code should be Thanks!!!
I created blank field in MasterCode table called Activitycode2. The name of my table with the cross referenced information is called Crosswalk. I can get to the point in the query where I choose SQL and have a text window that has SELECT; already there. Again, you are working with a real novice, can you be "very" as to exactly what the code should be Thanks!!!
we can do this one of two ways. the SQL method or the GUI method.
the SQL method is direct but requires some more information. ie. the field names and such.
GUI method.
from the query list select new query. then select the mastercode table and click add. next select the crosswalk table and click add. now click close.
there will be two table boxes in the top of the GUI query designer. scroll through them so the information that is matched is displayed in both master and crosswalk. (activitycode)
now click and drag activitycode from master to crosswalk. this creates a relationship between the two fields and basically says "Only when the two match" (yeah we aint done yet.) now we have to double click on the activityquery2. the information for that field will appear at the bottom half of the query window. now the english query would say. select the activitycode2 where activitycode from mastercode matches activitycode from crosswalk.
if you run this select statement you will see just a bunch of blank records. that's good. the number of records is the number of records that we are going to update with the activitycode2.
now for the tricky part. to change the select into an update. on the toolbar there is an icon that is two windows. its description is query type. click on the arrow next to it and a list of common query types appears. (note this list insn't comprehensive) it should read select, update, append
select the update query type.
now we have a change in the row names at the bottom of the query window. there is a new one for update to. this is the value that we want to put into the results of the select we were playing with. now we have to type in the table.field of the source of information. in this case it's crosswalk.equivilant (if there are spaces in your fieldnames you have to put the name in braces like this [table name].[field name])
now we hit the red exclamation mark on the toolbar and it goes. because this is an update query we don't even have to save the query. (unless we want to run it again later.)
the SQL method is direct but requires some more information. ie. the field names and such.
GUI method.
from the query list select new query. then select the mastercode table and click add. next select the crosswalk table and click add. now click close.
there will be two table boxes in the top of the GUI query designer. scroll through them so the information that is matched is displayed in both master and crosswalk. (activitycode)
now click and drag activitycode from master to crosswalk. this creates a relationship between the two fields and basically says "Only when the two match" (yeah we aint done yet.) now we have to double click on the activityquery2. the information for that field will appear at the bottom half of the query window. now the english query would say. select the activitycode2 where activitycode from mastercode matches activitycode from crosswalk.
if you run this select statement you will see just a bunch of blank records. that's good. the number of records is the number of records that we are going to update with the activitycode2.
now for the tricky part. to change the select into an update. on the toolbar there is an icon that is two windows. its description is query type. click on the arrow next to it and a list of common query types appears. (note this list insn't comprehensive) it should read select, update, append
select the update query type.
now we have a change in the row names at the bottom of the query window. there is a new one for update to. this is the value that we want to put into the results of the select we were playing with. now we have to type in the table.field of the source of information. in this case it's crosswalk.equivilant (if there are spaces in your fieldnames you have to put the name in braces like this [table name].[field name])
now we hit the red exclamation mark on the toolbar and it goes. because this is an update query we don't even have to save the query. (unless we want to run it again later.)
ASKER
msroberts,
Do I put the following statement in the criteria line of the query?
Select the activitycode2 where Procedure Code from MasterCode matches Procedure Code from Crosswalk
If I do this, I get an error of "Check the subquery's syntax and enclose the subquery in parentheses
Do I put the following statement in the criteria line of the query?
Select the activitycode2 where Procedure Code from MasterCode matches Procedure Code from Crosswalk
If I do this, I get an error of "Check the subquery's syntax and enclose the subquery in parentheses
This is an english statement telling us what the query is trying to do . Information only. the criteria should be empty. (or maybe 'is null')
ASKER
I have created an update query that has the two tables MasterCode and crosswalk that are linked by procedure code.
At the bottom of the query, I have Field: ActivityCode2 (which represents blank field in mastercode table)
Table: MasterCode
Update To: crosswalk.equivilant [MasterCode].[ActivityCode
Criteria:
Or:
Still need more help................sorry
Procedure code? is that the original activity code that can be found in both tables?
any-hoo
you only need 'crosswalk.equivilant' in the update to:
any-hoo
you only need 'crosswalk.equivilant' in the update to:
ASKER
Yes. What else is there to do
after you click on the red exclamation mark you should be done. it may give you a message waying you are about to update x records or it could just do it and not tell you anything. minimize the query and open the mastercode table and see if the activitycode2 column is still blank for all records. if it isn't then your query is done.
ASKER
It told me that I was about to update all my records, but the field is still blank
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ActivityCode: the table of Cross Reference Information
Activity code: string ; PRIMARY KEY
equivilant: string
Table: The table of data
{stuff} ; some key
AcCode: activity code string
new field: {blank}
UPDATE ActivityCode INNER JOIN [Table] ON ActivityCode.[Activity Code] = Table.AcCode SET [Table].[New Field] = [equivilant];