DELETE Query Headache!

[Avatar]

Greetings,

I have the following DELETE query called qry_DELETEOldRecords:

DELETE tbl_RenewalImport.RenewalImport_ID, *
FROM tbl_RenewalImport
WHERE (((tbl_RenewalImport.RenewalImport_ID) In (SELECT RenewalImport_ID FROM qry_TEMPSelectQuery )));

qry_TEMPSelectQuery is:

SELECT tbl_RenewalImport.RenewalImport_ID
FROM (tbl_RenewalImport LEFT JOIN tbl_FormUpdate ON tbl_RenewalImport.RenewalImport_ID = tbl_FormUpdate.RenewalImport_ID) LEFT JOIN tbl_SubRtdUpdate ON tbl_RenewalImport.RenewalImport_ID = tbl_SubRtdUpdate.RenewalImport_ID
WHERE (((tbl_FormUpdate.[FormRecieved?]) Is Not Null)) OR (((tbl_SubRtdUpdate.[SubReturned?]) Is Not Null));

qry_TEMPSelectQuery returns 185175 records.

When i run or view qry_DELETEOldRecords, it is returning 278753 records - which is the entire contents of the table...

Copying the SELECT statement out of qry_DELETEOldRecords (marked in green) and createing a new query returns the correct 185175 records.

Making this new query into a make table query, returns the full 278753 records again...

An ideas whats going on here?

 

[Joe4]

It sounds like maybe your RenewalImport_ID may not be unique within your tbl_RenewalImport table. Is that the case?

Also, in your delete query, it is unnecessary to specify individual fields, as you are deleting the whole record, i.e.

DELETE *
FROM tbl_RenewalImport
...

 

[Avatar]

Greetings,

RenewalImport_ID is unique in [tbl_RenewalImport].

I had also tried amending the DELETE statement to "DELETE *" but with no affect.

...Is there a limit to the number of records that can be used in a nested query within the Jet DB engine perhaps..?

 

[Joe4]

That makes no sense to me. If your delete query is using your Temp query, and the field used in your criteria is unique, there is no way your final query should contain more records than your subquery.

What if you try replacing the "IN" part of the criteria in your delete query with the SQL code of your Temp query (so it is all one query, instead of one query calling another)?

 

[xenou]

WHERE (((tbl_RenewalImport.RenewalImport_ID) In 
(SELECT RenewalImport_ID FROM qry_TEMPSelectQuery )))

One can always wonder about the parenthesis...
Maybe try:

WHERE tbl_RenewalImport.RenewalImport_ID IN 
(SELECT RenewalImport_ID FROM qry_TEMPSelectQuery)

Though I am not sure why you have two levels of abstraction above qry_TEMPSelectQuery. Can you just do it like this:

DELETE tbl_RenewalImport.RenewalImport_ID
FROM (tbl_RenewalImport LEFT JOIN tbl_FormUpdate ON tbl_RenewalImport.RenewalImport_ID = tbl_FormUpdate.RenewalImport_ID) LEFT JOIN tbl_SubRtdUpdate ON tbl_RenewalImport.RenewalImport_ID = tbl_SubRtdUpdate.RenewalImport_ID
WHERE (((tbl_FormUpdate.[FormRecieved?]) Is Not Null)) OR (((tbl_SubRtdUpdate.[SubReturned?]) Is Not Null));

Or perhaps simpler with two queries:

DELETE tbl_RenewalImport.RenewalImport_ID
FROM tbl_RenewalImport 
INNER JOIN tbl_FormUpdate 
ON tbl_RenewalImport.RenewalImport_ID = tbl_FormUpdate.RenewalImport_ID
WHERE tbl_FormUpdate.[FormRecieved?] Is Not Null

DELETE tbl_RenewalImport.RenewalImport_ID
FROM tbl_RenewalImport
INNER JOIN tbl_SubRtdUpdate 
tbl_RenewalImport.RenewalImport_ID = tbl_SubRtdUpdate.RenewalImport_ID
WHERE tbl_SubRtdUpdate.[SubReturned?]) Is Not Null

I really would try to keep question marks out of field names...and misspelled words aren't going to help [FormRecieved?]

 

[Avatar]

Joe4:
Makes no sense to me either.
Replacing the SELECT within the IN() statement with the full SQL from the temp query returns the full 278753 records as well
Thanks for the constructive and useful help Joe4.

xenou:

One can always wonder about the parenthesis...
Maybe try:

WHERE tbl_RenewalImport.RenewalImport_ID IN 
(SELECT RenewalImport_ID FROM qry_TEMPSelectQuery)

Made no difference - nor would I expect it to...

Though I am not sure why you have two levels of abstraction above qry_TEMPSelectQuery. Can you just do it like this:

DELETE tbl_RenewalImport.RenewalImport_ID
FROM (tbl_RenewalImport LEFT JOIN tbl_FormUpdate ON tbl_RenewalImport.RenewalImport_ID = tbl_FormUpdate.RenewalImport_ID) LEFT JOIN tbl_SubRtdUpdate ON tbl_RenewalImport.RenewalImport_ID = tbl_SubRtdUpdate.RenewalImport_ID
WHERE (((tbl_FormUpdate.[FormRecieved?]) Is Not Null)) OR (((tbl_SubRtdUpdate.[SubReturned?]) Is Not Null));

Because MS Access won't allow you to do that. "Specify the table containing the records you want to delete."

Or perhaps simpler with two queries:

DELETE tbl_RenewalImport.RenewalImport_ID
FROM tbl_RenewalImport 
INNER JOIN tbl_FormUpdate 
ON tbl_RenewalImport.RenewalImport_ID = tbl_FormUpdate.RenewalImport_ID
WHERE tbl_FormUpdate.[FormRecieved?] Is Not Null

DELETE tbl_RenewalImport.RenewalImport_ID
FROM tbl_RenewalImport
INNER JOIN tbl_SubRtdUpdate 
tbl_RenewalImport.RenewalImport_ID = tbl_SubRtdUpdate.RenewalImport_ID
WHERE tbl_SubRtdUpdate.[SubReturned?]) Is Not Null

Because you get exactly the same result as above… An error: "Specify the table containing the records you want to delete."

I really would try to keep question marks out of field names...and misspelled words aren't going to help [FormRecieved?]

Unless you are suggesting that a spelling mistake in the original field name would cause and error, I’m not sure what your point is? ...Or perhaps you are just trying to make yourself sound superior without actually providing any accurate advice whatsoever..?

The only useful thing you said in that whole exchange was to point out the spelling mistake, and you managed to do that in such a way as to be condescending.

Perhaps I’ve misconstrued your intentions? I hope so, although I doubt it.

 

[Joe4]

Unless you are suggesting that a spelling mistake in the original field name would cause and error, I’m not sure what your point is? ...Or perhaps you are just trying to make yourself sound superior without actually providing any accurate advice whatsoever..?

The only useful thing you said in that whole exchange was to point out the spelling mistake, and you managed to do that in such a way as to be condescending.

Perhaps I’ve misconstrued your intentions? I hope so, although I doubt it.

Avatar,

You probably do not want to jump on people who are trying to help you. xenou is truly trying to help you, and trying to give you good programming advice. Things like where parenthesis are placed can have a BIG impact on how the query will process. Also, the point he makes about fields with question marks in it is good. Question marks are often interpretted by programming languages as wild-cards, so it can really lead to some unexpected results.

Granted, in your case, I think it SHOULD be OK because you have enclosed the field names in quotes. However, standard programming practice is to avoid using punctuation or spaces in field or object names, just avoid these unintended consequences.

What makes figuring out these issues so difficult is that we do not have your database to work with, where we can see all the table structures and data.

You had mentioned that RenewalImport_ID is unique. Is it blank in any instances?

Just for kicks, can you run this query and tell me if it returns anything at all? I expect it probably won't return any records.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

[COLOR=black][FONT=Courier New]SELECT[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]      tbl_RenewalImport.RenewalImport_ID<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]      , Count(tbl_RenewalImport.RenewalImport_ID)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]FROM<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]      tbl_RenewalImport<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]GROUP BY<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]      tbl_RenewalImport.RenewalImport_ID<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]HAVING<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]      Count(tbl_RenewalImport.RenewalImport_ID)>1;[/FONT][/COLOR]

 

[Avatar]

Yeah you're right. I apologise. The phrasing of his response just put my nose out of joint.

By the way - following some further testing, MS Access 2003 appears to add the additional parenthesis back in even if you remove it.

The query returned no results.

I think I’m going to have to do this the long way and write some VBA to do it record by record instead.

EDIT:
Oh and no, it is never blank. - Probably should have mentioned this before - Standard auto number format.

 

[SydneyGeek]

Just for the heck of it, what happens if you remove the subquery from the WHERE clause, create a new query from it, and use a join?
On a large recordset I'm assuming it will be much faster. It *may* also select the correct records.

EDIT: Just join qry_TEMPSelectQuery to your delete query (without the subquery).

Denis

 

[Joe4]

That really is strange...
It has me intrigued, I wish I had access to the db to see what is going on.

Tell us a little bit about the other tables involved, tbl_FormUpdate and tbl_SubRtdUpdate . Maybe something is going on there.
1. What do these tables represent?
2. How/when are they populated?
3. How many records are in each database?

Also, let us know the timing of when these macros are run. I have had issues in the past where I have a criteria selection form with a command button to run my queries/VBA code, and if I didn't refresh/requery before running, it would give me bad results.