SQL & Updating a group of records

[leonepaolo]

Hi,

I want to update a group of (not necessarily all) records from one table with query results. I don't know how to write the SQL code. If it can be done, I expect it to look something like:
"SELECT * FROM qry WHERE qrySymbol = tblSymbol UPDATE DestinationField WITH SourceField"

Background: (may not be necessary to answer my question above)

TableMax: is full of duplicates for "FieldSymbol" - has 1020 records.

TableUnique: has only one record for each unique "FieldSymbol" - has 127 records

The following Queries count the duplicates in TableMax within a specific Date range
Query_Count_RangeA - 22
Query_Count_RangeB - 37
Query_Count_RangeC - 58
Query_Count_RangeD - 89
Query_Count_RangeE - 127

Query_SortCount_ForAllQueryRanges - This Query starts with TableUnique and has a join with each Query_Count_... . This query sorts the records from Query_Count_RangeA through to Query_Count_RangeE. This query takes quite a while to open, about to 3.5 minutes.

To see if it could be done faster, I decided to create a new table, with 6 fields in it, the "FieldSymbol" and "A" through to "E" represinting the count value from each of the Query_Count_... . I then created a query that sorts the table according to the counts as I did in Query_SortCount_ForAllQueryRanges. Much to my amazement the query opened instantly - I like it!

Any comments and/or help are greatly appreciated.

Thanks in advance,
Paolo

 

[Troyston]

set rst = openrecordset (QueryResults)
with rst
.movelast
.movefirst
do while not.eof
strsql="Update OtherTable " & _
"Set Field = " & !QueryResultsField & " & _
"where Field = " & SomeOtherValue & ";"
.movenext
loop
end with
rst.close

Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk