update data in a table with with reference to another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have two table in one table
name group
AA 1
BB 2
CC 3
DD 2

another

ID nameinID groupinID
1 AA
2 BB
3 AA
4 CC


and like this, now i have to update my second table such that in groupinID
field it will get 1,2,1,3 respectively according to first table

I used following code.

public Sub Command1_Click()
Dim hello As String
abcd = "select * from table2"
ssr.Form_Form1.RecordSource = abcd
If Not ssr.Form_Form1.Recordset.BOF Then
ssr.Form_Form1.Recordset.MoveFirst
Else
ssr.Form_Form1.Recordset.MoveNext
End If
While Not ssr.Form_Form1.Recordset.EOF
seqnm = ssr.Form_Form1.Recordset.Fields("nameinID")
ssr.Form_Form1.Refresh
ananta = "select * from table1 where name='" + seqnm + "'"
ssr.Form_Form1.RecordSource = ananta
hello = ssr.Form_Form1.Recordset.Fields("group")
ssr.Form_Form1.RecordSource = abcd
If Not ssr.Form_Form1.Recordset.EOF Then
ssr.Form_Form1.Recordset.Edit
ssr.Form_Form1.Recordset.Fields("groupinID") = hello
ssr.Form_Form1.Recordset.Update
ssr.Form_Form1.Refresh
ssr.Form_Form1.Recordset.MoveNext
End If
Wend
End Sub


it goes to the first record, then 2nd and always 2nd, so how can i update
for all rows with one command.

I appreciate the help
thank you
 
Even better don't update TableTwo at all. Just join TableOne to tableTwo
a query when ever you need the group Value.


SELECT TableTwo.ID, TableTwo.NameInID, TableOne.Group
FROM TableTwo Inner Join TableOne
ON TableTwo.NameInId = TableOne.Name


If for some reason you really have to store the data in tableTwo then
use an update query to do so. But there is almost no case where this is
a good solution. The best solution is to join the two tables and get
the data when needed.

If you store the information in tabletwo and change the NameInID in a
record in tableTwo then you have to remember to update the GroupInID
value every time. If you miss updateing GroupInID once, your data is no
longer accurate and you will not be aware of that fact.


UPDATE TableTwo Inner Join TableOne
ON TableTwo.NameInId = TableOne.Name
SET GroupInID = TableOne.Group


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top