Link to home
Start Free TrialLog in
Avatar of hudmon
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?
Avatar of msroberts
msroberts

I would get the sheet into a table of ActivityCodes then do something like this

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];
Avatar of hudmon

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
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.
Avatar of hudmon

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!!!
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.)  
Avatar of hudmon

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
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')
Avatar of hudmon

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].[ActivityCode2]
                                                   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:  
Avatar of hudmon

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.
Avatar of hudmon

ASKER

It told me that I was about to update all my records, but the field is still blank
ASKER CERTIFIED SOLUTION
Avatar of msroberts
msroberts

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial