CODEWITHSUNDEEP
sqlms-access
Chris2015
Chris2015

Reputation: 1050

Update query updates table and not query results

I am trying to use an UPDATE query in SQL (Access). What I would like to do is update the results of the query without affecting the underlying table. So, keep the original table intact, but update my query results. When I attempt the following code I update my query, but also the table as well. Any ideas?

UPDATE [HRBI Query] 
SET [HRBI Query].PaySegmentMultiplier = IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'Above top segment', 0,
   IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'Below segment 1', 1.35,
      IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S1', 1.25,
        IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S2', 1.15,
           IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S3', .90, 
              IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S4', .60, 
                 IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S5', .40, 
                     PaySegmentMultiplier.PaySegmentMultiplier
                 )
              )
           )
        )
     )
  )
);

Upvotes: 0

Views: 46

Answers (2)

Parfait
Parfait

Reputation: 107687

Very interesting you used my earlier answer!

Consider using a temp table which you can generate from query results with a Make-Table query. 

SELECT * INTO tmpTable FROM qry

Then, routinely run the make-table (overwriting previous version) and update query on temp table. Finally, base earlier query off new temp table or just use the temp table. Altogether, you retain original and have an amenable table.

Upvotes: 1

Gustav
Gustav

Reputation: 55906

In Access, that is not possible using a query, neither open a recordset based on the query.

You can read the date into an array an manipulate that in code. Or use another programming language like C# and open a dataset, then use this as source for whatever you are trying to do.

Upvotes: 0

Related Questions