Results differ between Select and Update Queries

AC5FF

Registered User.
Local time
Yesterday, 20:19
Joined
Apr 6, 2004
Messages
552
Not really sure why this happens, but I have an update query I would like to run to update a Y/N field. I initially wrote the query as a Select query to test the data results would be what I was expecting. Pretty easy, I just want to see the latest entry by date of an account number's reply. Here is that Select query:
Code:
SELECT Max([ZZZero Testing].Response_Date) AS MaxOfResponse_Date, [ZZZero Testing].[ACCT NUMBER]
FROM [ZZZero Testing]
GROUP BY [ZZZero Testing].[ACCT NUMBER]
HAVING (((Max([ZZZero Testing].Response_Date)) Is Not Null));

This gives me 7 results out of my test group of data. Exactly what I would expect to see. Yet when I add one more field to view in this query and make it an Update query I loose all of this previous query and all I have is:

Code:
UPDATE [ZZZero Testing] SET [ZZZero Testing].UseResponse = 1
WHERE ((([ZZZero Testing].Response_Date) Is Not Null));

Running this update will update 15 rows of my table - not correct.

So, I tried to run an update query off of my first query above that gives me the correct 7 rows. But doing this I get an error "Operation must use an updateable query"

I'm not sure how to get around all of this. Out of my table of 700+ lines I've gotten queries to give me the 7 results I would expect to see, but I cannot get a query to update those specific rows.

Any ideas?
 
First, you have 2 different queries. Where did the Max(ResponseDate) value go when you made it an update query? That's rhetorical--you didn't use it at all. The only criteria you have in the second is that the date not be null.

Second, and more importantly, why do you need to update the data? I've found updwards of 80% of all action queries are unnecessary and often the least efficient method to ascertain the data you need. Why can't you just use the select query that works?
 
plog...
I'm very bad at writing SQL queries; 95% of what I do is in design view. When I changed the query in design view from Select to Update the condition of Max(responsedate) disappeared. When I tried to re-add it myself in SQL I received an error of 'invalid characters'

As for why I need to update the data... The table is a list of accounts with zero activity. The data is entered weekly off another report. Lets say data from 1/10/13 had account #'s 5 and 8 with zero activity. The customer replied stating that apartment tied to these accounts were empty at the time and so the zero activity was valid. Now on 2/1/13 the same two accounts show up again with zero activity. This time I get a reply from the customer that the apartmets are now occupied, but the tennant does not wish to use his/her heaters.

Both responses are going to be annotated in the database, but I only need to see the latest response on my report, not all of them. Currently I would get two reports for each of the above accounts because there are two entries with replies. I don't want this to happen. To avoid it, the best method I've come up with is to update a 'reply' flag to use the last response only...

I'm game to hear other ideas to accomplish this; but for now this is the best method I have come across...
 
Just FYI - here is some sample data from the table:
Code:
ReportDate	ACCT NUMBER	DaysZ	Sent	Response_Date	Response	Notes	UseResponse
25-Jan-13	001-005-02-16	25.00	Yes	2/1/2013	Test A	Testing Data	No
25-Jan-13	002-041-01-05	25.00	Yes			Testing Data	No
25-Jan-13	002-041-01-12	25.00	Yes	2/1/2013	Test C	Testing Data	No
25-Jan-13	002-041-02-11	25.00	Yes	2/1/2013	Test D	Testing Data	No
25-Jan-13	002-044-06-13	25.00	Yes			Testing Data	No
25-Jan-13	002-045-02-20	25.00	Yes	2/1/2013	Test F	Testing Data	No
25-Jan-13	002-045-03-05	25.00	Yes			Testing Data	No
25-Jan-13	002-045-03-19	25.00	Yes				No
25-Jan-13	002-048-03-09	25.00	Yes				No
01-Jan-13	001-005-02-16	25.00	Yes	1/1/2013	Test A Jan	Testing Data	No
01-Jan-13	002-041-01-05	25.00	Yes	1/1/2013	Test B Jan	Testing Data	No
01-Jan-13	002-041-01-12	25.00	Yes	1/1/2013	Test C Jan	Testing Data	No
01-Jan-13	002-041-02-11	25.00	Yes			Testing Data	No
01-Jan-13	002-044-06-13	25.00	Yes			Testing Data	No
01-Jan-13	002-045-02-20	25.00	Yes	1/1/2013	Test F DEC	Testing Data	No
01-Jan-13	002-045-03-05	25.00	Yes	1/1/2013	Test G DEC	Testing Data	No
01-Dec-12	001-005-02-16	25.00	Yes			Testing Data	No
01-Dec-12	002-041-01-05	25.00	Yes	12/1/2012	Test B DEC	Testing Data	No
01-Dec-12	002-041-01-12	25.00	Yes	12/1/2012	Test C DEC	Testing Data	No
01-Dec-12	002-041-02-11	25.00	Yes	12/1/2012	Test D DEC	Testing Data	No
01-Dec-12	002-044-06-13	25.00	Yes	12/1/2012	Test E DEC	Testing Data	No
01-Dec-12	002-045-02-20	25.00	Yes	12/1/2012	Test F DEC	Testing Data	No
01-Dec-12	002-045-03-05	25.00	Yes	12/1/2012	Test G DEC	Testing Data	No
 
I would do this just with a SELECT query, most likely using the initial SELECT query you created as a sub-query.

That sample data is helpful, now I need to know what the final dataset is to look like. Based on the sample data you posted what records and data should be on the report?
 
I thought of doing just a select query as a sub query too, but if you add in the "Response" field into the select query above you get 13 lines back instead of the expected seven. I'm assuming that is because of the differences in the data within that column.

As for the final dataset from this table ... Let's first add in another 8 lines of data to make this more realistic as I would have it working here. If you look at the table of data given, there are 8 account numbers listed 3 different times. (One is missing from the 12/1/12 dataset.) We should add all 8 again, but this time just the account and ZeroDays filled in with - say today's date - as the report date.

I would run the report based off of today's date and I would get everything in the table with that date - just FYI - there could be another 20-100 entries of different account numbers with today's date for a report date. All would print out on the final report.

So the output would be like:
Code:
Report Date     ACCT NUMBER     DaysZ     Response_Date     Response
2/5/13            001-005-02-16      25             2/1/13            Test A
2/5/13            002-041-01-05      25             1/1/13            Test B Jan
2/5/13            002-041-01-12      25             2/1/13            Test C 
2/5/13            003-041-02-11      25             2/1/13            Test D
2/5/13            002-044-06-13      25             12/1/13          Test E Dec
2/5/13            002-045-02-20      25             2/1/13            Test F
2/5/13            002-045-03-05      25             1/1/13            Test G Jan
2/5/13            001-005-03-19      25
2/5/13            002-048-03-09      25
etc...
Basically giving me the last response date and response for an account number if there is one. If no response data is found then those fields are just blank on the output.

Hope this helps..
 
Not exactly the same issue, but I have a similar (but simpler) problem where Access produces different results depending on the mode in which a Query is run. In my case, calculated Field expressions do not work in Append mode. Please, anybody, help me on my thread:

showthread.php?p=1230863

Thanks!
 
You lost me, so try again.

Post sample data from your table and then post what should be returned based on that sample data.

I'd also prefer no more explanations.
 
Phog;

Lets try this again... I had to maniplulate a lot of stuff to get this; but here is what I would have...

SAMPLE DATASET:
Code:
ReportDate	ACCT NUMBER	DaysZ	Sent	Response_Date	Response	Notes	UseResponse
01-Dec-12	001-005-02-16	25.00	TRUE			Testing Data	FALSE
01-Jan-13	001-005-02-16	25.00	TRUE	1/1/2013	Test A Jan	Testing Data	FALSE
25-Jan-13	001-005-02-16	25.00	TRUE	2/1/2013	Test A	Testing Data	FALSE
05-Feb-13	001-005-02-16	25.00	TRUE				FALSE
01-Dec-12	002-041-01-05	25.00	TRUE	12/1/2012	Test B DEC	Testing Data	FALSE
01-Jan-13	002-041-01-05	25.00	TRUE	1/1/2013	Test B Jan	Testing Data	FALSE
25-Jan-13	002-041-01-05	25.00	TRUE			Testing Data	FALSE
05-Feb-13	002-041-01-05	25.00	TRUE				FALSE
01-Dec-12	002-041-01-12	25.00	TRUE	12/1/2012	Test C DEC	Testing Data	FALSE
01-Jan-13	002-041-01-12	25.00	TRUE	1/1/2013	Test C Jan	Testing Data	FALSE
25-Jan-13	002-041-01-12	25.00	TRUE	2/1/2013	Test C	Testing Data	FALSE
05-Feb-13	002-041-01-12	25.00	TRUE				FALSE
01-Dec-12	002-041-02-11	25.00	TRUE	12/1/2012	Test D DEC	Testing Data	FALSE
01-Jan-13	002-041-02-11	25.00	TRUE			Testing Data	FALSE
25-Jan-13	002-041-02-11	25.00	TRUE	2/1/2013	Test D	Testing Data	FALSE
05-Feb-13	002-041-02-11	25.00	TRUE				FALSE
01-Dec-12	002-044-06-13	25.00	TRUE	12/1/2012	Test E DEC	Testing Data	FALSE
01-Jan-13	002-044-06-13	25.00	TRUE			Testing Data	FALSE
25-Jan-13	002-044-06-13	25.00	TRUE			Testing Data	FALSE
05-Feb-13	002-044-06-13	25.00	TRUE				FALSE
01-Dec-12	002-045-02-20	25.00	TRUE	12/1/2012	Test F DEC	Testing Data	FALSE
01-Jan-13	002-045-02-20	25.00	TRUE	1/1/2013	Test F DEC	Testing Data	FALSE
25-Jan-13	002-045-02-20	25.00	TRUE	2/1/2013	Test F	Testing Data	FALSE
05-Feb-13	002-045-02-20	25.00	TRUE				FALSE
01-Dec-12	002-045-03-05	25.00	TRUE	12/1/2012	Test G DEC	Testing Data	FALSE
01-Jan-13	002-045-03-05	25.00	TRUE	1/1/2013	Test G DEC	Testing Data	FALSE
25-Jan-13	002-045-03-05	25.00	TRUE			Testing Data	FALSE
05-Feb-13	002-045-03-05	25.00	TRUE				FALSE
05-Feb-13	002-045-03-19	25.00	TRUE				FALSE
05-Feb-13	002-048-03-09	25.00	TRUE				FALSE
05-Feb-13	002-048-03-10	22.00	TRUE				FALSE
05-Feb-13	602-001-01-04	21.00	TRUE				FALSE
05-Feb-13	602-001-01-08	25.00	TRUE				FALSE

I would run my report based on 5 Feb 13, so I would expect to see:
Code:
ReportDate	ACCT NUMBER	DaysZ	Response_Date	Response
05-Feb-13	001-005-02-16	25.00	2/1/2013	Test A
05-Feb-13	002-041-01-05	25.00	1/1/2013	Test B Jan
05-Feb-13	002-041-01-12	25.00	2/1/2013	Test C
05-Feb-13	002-041-02-11	25.00	2/1/2013	Test D
05-Feb-13	002-044-06-13	25.00	12/1/2012	Test E DEC
05-Feb-13	002-045-02-20	25.00	2/1/2013	Test F
05-Feb-13	002-045-03-05	25.00	1/1/2013	Test G DEC
05-Feb-13	002-045-03-19	25.00		
05-Feb-13	002-048-03-09	25.00		
05-Feb-13	002-048-03-10	22.00		
05-Feb-13	602-001-01-04	21.00		
05-Feb-13	602-001-01-08	25.00

So basically, the first 8 lines here have previous entries in the table where I have a response logged and added the last response date/comments logged. But those accounts that are not listed prior - or listed prior with no response - are also included in the output, but obviously will not have any response date or comments...
 
CRAP
I think I just got it to work!!!

Code:
SELECT Max([ZZZero Testing].Response_Date) AS MaxOfResponse_Date, [ZZZero Testing].[ACCT NUMBER], [ZZZero Testing].UseResponse, Max([ZZZero Testing].Response) AS MaxOfResponse
FROM [ZZZero Testing]
GROUP BY [ZZZero Testing].[ACCT NUMBER], [ZZZero Testing].UseResponse
HAVING (((Max([ZZZero Testing].Response_Date)) Is Not Null));

When I would add in response to the query I would get 13 results, but only expected 7.
I just re-ran the first query above after adding that field but instead of using "Group By" I used "Max" on the Response field - and it worked as I expected.

I still have to now be able to merge this query into the larger report query - but I think that it will be doable...

I'm interested in if you come up w/something different - but in essence I've got it worked out I think! Sometimes I just need to step back and explain things out like I've done here and the results magically appear! :D
 
It may or may not give you what you want--I honestly can't tell what you want. So let me tell you what your query is giving you: For every unique account number/User Response permutation in your database it is giving you the highest valued Response Date value and the highest valued Response of that pairing.

Be careful, this means the result you get back in your query may not actually exist in your underlying table. Here's what I mean:

AccountNumber, UserResponse, Response, ResponseDate
001-005-02-16, FALSE, Testing A, 2/2/2013
001-005-02-16, FALSE, Testing C, 1/1/2013

Your query will return this:

AccountNumber, UserResponse, Response, ResponseDate
001-005-02-16, FALSE, Testing C, 2/2/2013

That record does not exist in the underlying data. This occurs because of the MAX() you are using in your query.

Is that what you want?
 
You bring up a good point. I'm working with minimal data here, and I think - no, i just confirmed - that you are correct.

While my query (in post #10) does give me the correct 7 responses - it does NOT give me the correct data. The date data is correct, but as you mentioned the "response: data is not; it is instead the 'max' value of all the responses.

And this is not what I want - and can/will cause problems if it were to run like this.


But this leads me back to my original question... Take the query run in post #10, but remove the field "Response" completely from the query. That data is now completely accurate. If I could then UPDATE the "UseResponse" field based upon this query, all would be fine. I could then use a sub-query to select just those responses.
 
Your select query is aggregating data by using Max() and group by so you are comparing apples and oranges. The update query is not aggregating anythig. If you run the actual update query as a select query, you will see that you get the same number of rows as the update query.

SELECT [ZZZero Testing].Response_Date
FROM
WHERE ((([ZZZero Testing].Response_Date) Is Not Null));
 
Pat ...
I agree; That's what got me to come here in the first place :D
 
Let me see if I am totally off track here, or just plane thinking wrong...
Here is the query that returns the right information that I want:
Code:
SELECT Max([ZZZero Testing].Response_Date) AS MaxOfResponse_Date, [ZZZero Testing].[ACCT NUMBER], [ZZZero Testing].UseResponse
FROM [ZZZero Testing]
GROUP BY [ZZZero Testing].[ACCT NUMBER], [ZZZero Testing].UseResponse
HAVING (((Max([ZZZero Testing].Response_Date)) Is Not Null));

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:
Code:
UPDATE [ZZZero Testing] SET [ZZZero Testing].UseResponse = 1
WHERE ((([ZZZero Testing].Response_Date) Is Not Null));

I'm very bad at writing SQL; but is there a way to combine these two queries?
 
So now we are back looking at post #9's data. I do not see how to determine DaysZ in the final output if there is no records with a ResponseDate value and ReportDate seems to be just the current date and not the value in the ReportDate field of the table.

1. Is that true for how to generate the ReportDate value in the final output? Its just the current date?

2. How do you know what value for DaysZ should be in the final output when none of the records for that account have a Response Date?
 
plog;

First; if it will make things easier; DaysZ does not have to be included in the output. I would like to be able to use the data on the report - but it is not critical in this situation.

Response date is the date the customer replied about his/her account with the information stored in the response field. The ReportDate is the date of the report generating the data. In essence, if I wanted to run a report for 1 Dec 2012 I could.
 
You're losing me again.

The ReportDate is the date of the report generating the data.


That means I was correct when I said the ReportDate value in the final output is just the current date.

But then you said:

In essence, if I wanted to run a report for 1 Dec 2012 I could.

I know what that means, but it also implies something different since you actually have 12/1/2012 data in your table. First, is 'wanted' used as future tense or past tense? For example 'If I wanted to run a report for 12/1/2012 tomorrow I could?' or 'If I wanted to run a report for 12/1/2012 on 12/1/2012 I could have?'. Based on the first sentence I quoted from you it can only mean the second.

You specifically chose an example date that actually has data in your table. That muddies the issue. Why did you choose that date as an example?
 
Phog;
If I want to run a report on 12/1/12 I could at any time I want because the data in the table is stored with the date it was input.

Maybe some of the confusion is coming from my field names. ReportDate is the date of the report that generated this list of accounts. The ReportDate is 'not' the date I want to run a report on this data.

The "ReportDate" data in the final output is the date of the particular account's information.
 
The "ReportDate" data in the final output is the date of the particular account's information.

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.
 

Users who are viewing this thread

Back
Top Bottom