I have Table 1 as my main table, I want to update Table 1 with ALL the values of Table 2.  The key for the tables is StudentID.
Can I put this in a single UPDATE statement or do I need to have SELECT query first then inside the LOOP is my UPDATE statement to
update table 1?

rs.open = "SELECT . . . "

Do while not rs.EOF
   strSQL = "UPDATE .... = rs.fields("name")....."
   db.execute (strSQL)
rs.movenext
Loop

OR I can put this on a single UPDATE statement?  Please advice.

Thanks,

Enzo
YahooID: onestepcloser2insanity

Create two queries.

Query 1 - Create an Update query to update the records in Table1 with
the same records in Table2.  
Query 2 - Create an Append query and append Table1 with those records in
Table2.

If Table1 and Table2 have the same student, the update query will update
all the fields in Table1 from those in Table2.

If Table1 does not have records for any students in Table2, the Append
query will add those records from Table2 to Table1.

NOTE:  Make sure that the StudentID field in Table1 is either the
PrimaryKey or does not allow duplicates.  This will insure that a
student will not be added to Table1 more than once.


John V. Ruff - The Eternal Optimist :-)
Always Looking for Contract Opportunities

www.noclassroom.com

Home:  xxx.xxx.xxxx
Cell:  xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498 
 
"Commit to the Lord whatever you do,
    and your plans will succeed."  Proverbs 16:3


-----Original Message-----
From: enZo :-) [mailto:enzaux@g...] 
Sent: Friday, June 21, 2002 8:31 PM
To: Access
Subject: [access] Updating table1 with table2



	I have Table 1 as my main table, I want to update Table 1 with
ALL the values of Table 2.  The key for the tables is StudentID. Can I
put this in a single UPDATE statement or do I need to have SELECT query
first then inside the LOOP is my UPDATE statement to update table 1?

rs.open = "SELECT . . . "

Do while not rs.EOF
   strSQL = "UPDATE .... = rs.fields("name")....."
   db.execute (strSQL)
rs.movenext
Loop

OR I can put this on a single UPDATE statement?  Please advice.

Thanks,

Enzo
YahooID: onestepcloser2insanity

Thanks John.  I have solve my problem I have done it one a single UPDATE statement.  I've used INNER JOIN.  Below is my real code.
Just an info for others:

UPDATE tblTRANXRemit INNER JOIN [tblRemit-Import] ON tblTRANXRemit.TranNo = [tblRemit-Import].TranNo
SET tblTRANXRemit.DeliveryStatus = [tblRemit-Import].[DeliveryStatus], tblTRANXRemit.StatusMsg = [tblRemit-Import].[StatusMsg],
tblTRANXRemit.DateDel = [tblRemit-Import].[DateDel], tblTRANXRemit.DelBy = [tblRemit-Import].[DelBy], tblTRANXRemit.DateEnc 
[tblRemit-Import].[DateEnc], tblTRANXRemit.Receivedby = [tblRemit-Import].[Receivedby], tblTRANXRemit.PHPTeller 
[tblRemit-Import].[PHPTeller], tblTRANXRemit.DateReceived = [tblRemit-Import].[DateReceived];

Enzo :)

-----Original Message-----
From: John Ruff [mailto:papparuff@c...]
Sent: Saturday, June 22, 2002 1:45 PM
To: Access
Subject: [access] RE: Updating table1 with table2


Create two queries.

Query 1 - Create an Update query to update the records in Table1 with
the same records in Table2.
Query 2 - Create an Append query and append Table1 with those records in
Table2.

If Table1 and Table2 have the same student, the update query will update
all the fields in Table1 from those in Table2.

If Table1 does not have records for any students in Table2, the Append
query will add those records from Table2 to Table1.

NOTE:  Make sure that the StudentID field in Table1 is either the
PrimaryKey or does not allow duplicates.  This will insure that a
student will not be added to Table1 more than once.


John V. Ruff - The Eternal Optimist :-)
Always Looking for Contract Opportunities

www.noclassroom.com

Home:  xxx.xxx.xxxx
Cell:  xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
    and your plans will succeed."  Proverbs 16:3


-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Friday, June 21, 2002 8:31 PM
To: Access
Subject: [access] Updating table1 with table2



	I have Table 1 as my main table, I want to update Table 1 with
ALL the values of Table 2.  The key for the tables is StudentID. Can I
put this in a single UPDATE statement or do I need to have SELECT query
first then inside the LOOP is my UPDATE statement to update table 1?

rs.open = "SELECT . . . "

Do while not rs.EOF
   strSQL = "UPDATE .... = rs.fields("name")....."
   db.execute (strSQL)
rs.movenext
Loop

OR I can put this on a single UPDATE statement?  Please advice.

Thanks,

Enzo
YahooID: onestepcloser2insanity