HOWTO: Save 1 Recordset back into 2-3 Tables it was pulled from?

[Thom]

My problem is: When I use a SQL query to pull ALL the data from 2 tables; HOW do I then turn around and save the relevant info BACK to each specific table??
i.e. Fields 1,3,5 are pulled from Table 1,
Fields 2,4,6 are pulled from Table 2 in the SQL query;

When the user is done, how do I code to get the results of Fields 1,3,5 back INTO table 1, and 2,4,6 INTO table 2 in the Database??
I'm figuring I have to code all this(that is, no easy way out <g> ). Also, what if I want to save the query as a .ADTG persisted file-can we do that with 2 recordsets going into 1 file?? Thanks for any help!!

[JHausmann]

>When the user is done, how do I code to get the results of >Fields 1,3,5 back INTO table 1, and 2,4,6 INTO table 2 in >the Database??

Two sql commands.

"Update table1 set field1='', set field3='', set field5='' where condition"


"Update table2 set field2='', set field4='', set field6='' where condition"

[Thom]

Wow! It CAN'T be that easy! <g>
I'm stuck with Access2000, btw; I'm using as many SQL commands as I can to improve performance, so I want to make SURE I understand you!

So the Update command allows me to FILL any previously blank(default value)fields with the NEW info provided by the user, AND OVERWRITE any previous info with the new info provided by the user?

ex: Update table1 set field1 = "John Doe" when it was previously "Jane Doe" will work OK, right? And then I'd need to do a While Not EOF to cycle thru the whole recordset to update all the changes, correct? That's COOL! Thanks for the help!

[JHausmann]

Originally posted by Thom
Wow! It CAN'T be that easy! <g>
I'm stuck with Access2000, btw; I'm using as many SQL commands as I can to improve performance, so I want to make SURE I understand you!

So the Update command allows me to FILL any previously blank(default value)fields with the NEW info provided by the user, AND OVERWRITE any previous info with the new info provided by the user?

Yep. And, BTW, if you use views you must update by discrete tablename (an update statement only works on one table at a time)

Originally posted by Thom

ex: Update table1 set field1 = "John Doe" when it was previously "Jane Doe" will work OK, right? And then I'd need to do a While Not EOF to cycle thru the whole recordset to update all the changes, correct? That's COOL! Thanks for the help!

update set field='something' replaces the contents of what was there with what you specify. An update will work against as many records as the where condition retrieves. To update every record in the database, don't use a where condition. There is no need to loop, if you can figure out the correct where condition.

[Thom]

All Right! Thanks a bunch for your help! Your advice has helped me rework the database to solve some tricky problems AND gotten me out of this sticky problem trying to avoid using multiple recordsets. I REALLY appreciate it!