Append Query for Dummies? But maybe I shouldn't use append? 1

[rdy4trvl]

I have two tables I and need to "update" certain fields…

OwnerTbl has Name, Address (currenlty blank field), City and lots of other good data – however, no address information. This table feeds many queries and forms.

NewInfoTbl has Name, and Address

I need to take the Address from NewInfoTbl and insert it into OwnerTbl where the Names match. Is this possible without SQL…I tried a Append Query but I end up with a mismatch error (both address fields are text). Any suggestions?
Thanks
Doug

 

[Blorf]

You want an update query. Try this:

UPDATE OwnerTbl INNER JOIN NewInfoTbl ON OwnerTbl.Name = NewInfoTbl.Name SET OwnerTbl.Address = [NewInfoTbl].[address];


ChaZ

Ascii dumb question, get a dumb Ansi

 

[OhioSteve]

Do the name fields contain the same information in the same format? If so, you can use an update query for this job. Create a new query and add the owner table. Then make its type "update" (use the button about three buttons to the left of the sum button). Add the fields from owner that you want to update (2x click it to add it). Now, find the "update to" row in the query design grid. Put your cursor in that space. Then, click the magic wand icon labeled with you mouse. Use the interface that appears to choose the appropriate field from the other table (some wierd code will appear in the "update to" space). Now, add a SECOND field to your query. On that field, add the appropriate criteria (that should probably be the 'name' field & and you can add the criteria using the wand).

I realize that I am feeding you alot of instructions. When you get lost, post your specific problem. Someone should be able to get you on track again. After you complete the query you can run it and integrate the data.

 

[grtammi]

Try using an UPDATE query instead ... an append query will just add the data to the end of your table ... you want to UPDATE OwnerTbl SET Address='<value>' WHERE OwnerTbl.Name='<value>' ;

Use this in a subroutine/function such as ...

Sub UpdateAddresses()

' variable declarations
Dim rs As DAO.Recordset (or ADO, but modify as needed)
Set rs = CurrentDb.OpenRecordset(&quot;NewInfoTbl&quot
dim sSQL as string

If Not rs.EOF Then

rs.MoveFirst

While Not rs.EOF
' define update statement
sSQL = &quot;UPDATE OwnerTbl SET Address='&quot; & rs(&quot;Address&quot _
& &quot;' WHERE OwnerTbl.Name='&quot; & rs(&quot;Name&quot & &quot;';&quot;
' execute SQL update command to update address
DoCmd.RunSQL sSQL
' move to next record
rs.MoveNext
End If

' variable cleanup
sSQL = &quot;&quot;
rs.Close : set rs = nothing

End Sub

HTH


Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[rdy4trvl]

OhioSteve - thanks!!! Thanks to the others too. I did add one step. I added a second table to the query and linked the names.