Results differ between Select and Update Queries

[liddlem]

When I run into this sort of issue, I generally try a two-step approach.
I would make turn the SELECT into a MAKE Table query.
Then I would run the update query based on the table that I have just made.
Remember to delete the temporary table afterwards.

 

[AC5FF]

This doesnt sound like a bad idea... but sounds like a lot of excess work. Making the table, then you would have to delete the rows from the main table that were copied, then update the temp table, move the temp table data back, ans finally deleting the temp table.

It might work... I will have to try tomorrow.......

 

[liddlem]

Not as much work as you think.
1. Get the SELECT query working properly.
2. Click the "MAKE Table" icon - it will ask you what u want to name the table. Call it "TEMP"
- When you run it, this will simply take a copy of the records that match the criteria of your select and put them into a new table.
3. Now create an UPDATE query that finds all records in the master table that match the records in the TEMP table - and update the appropriate fields. (No need to delete the records in the master - Just UPDATE them)
4. Now delete the TEMP table.

TIP: If you use a macro to run this sequence of events, then you could do ....
1. Set Warnings Off - This will allow the MAKE query to overwrite the TEMP table without giving you any annoying warnings.
2. MAKE Table TEMP
3. Set Warning On - This will give tell you how many records will be updated when you rund the next query. (When you trust the UPDATE query, then move this step the AFTER the UPDATE query)
4. UPDATE the Master table.

 

[AC5FF]

Liddlem

Again, thanks for the suggestion! I was looking at running through this and realized I had attempted this a week or so ago. Or at least a similar approach. I could not get this method to work for me. The problem I ran into was that while I could run a query to get the data I wanted to update - in this case 7 records from my sample data table - I could not get the right information to update in the final steps. In this situation with my sample data it would want to update 15 rows.

I believe that this may be due to how I am recording all this data.
The only way I can pull just the records I need to update is to query using just the Account Number and ResponseDate Fields. Adding any other field will give me incorrect results.

I know there has to be a way around all of that - just can't come up with the solution...
I have even tried putting responses into a different table, but end up with the same type of problems going that route as well.