SQL & Updating a group of records 1

[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

 

[jjlogan]

Consider rephrasing your question in terms of 1) this is what I'm trying to do (without referencing your current database tables and queries). 2) Any suggestions?
I assume you are familiar with normalizing tables; if not use Microsoft MSDN or search this site for directions.
Generally, an update query does the job of updating. You can use DoCmd.RunSQL "sql code" or just run the query from the Query section.
Jeff