Results differ between Select and Update Queries

[Pat Hartman]

and here is the same query (again I am working in design view and all I did was change the query type to update and put a "1" in to update the 'UseResponse' field:

Let me try again - the two queries are different and will NOT return the same set of records. As long as aggregation actually takes place the first query will return fewer rows than the second query. That is why I rewrote the update query as a select query so you could see the records that would be updated. Due to the way SQL works, you can't use aggregation in an update query because tecnically when you aggregate data, the database engine has no way of keeping individual record identifiers and so it has no way of identifying individual records to update.

 

[AC5FF]

That doesn't entirely jive with what you listed as your expected result from your sample data. This is the first line of your expected result:


Report Date, ACCT NUMBER, DaysZ, Response_Date, Response
05-Feb-13, 001-005-02-16, 25.00, 2/1/2013, Test A

That record doesn't exist in your sample data.

Plog;
I think we've been going about things the same but looking at them differently...

I disagree with you; in so far as the data for my expected result IS in my sample data table. But I believe I may see things like you are ...

Post #9, sample data - line #4 of data does contain an entry for 2/5/13 for this specific account #. I realize that it does not have the response date/response data - but that is what I am trying to achieve in my output.

Lines 2 and 3 respectively have data for the same account number, just different dates. For this example - I recieved a reply from the customer on 1/1/13 for data sent to them on 1/1/13 with their reply Test A Jan. I then received a reply from the customer on 2/1/13 for data sent to them on 1/25/13 saying Test A.

My output for the 2/5/13 data should say what you listed; using the data from row #4 and finding the last reply from them (in this case on 2/1/13) and using that 2/1/13 date and corresponding response (Test A).

I hope that makes sense!!

 

[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.