Delete Selected Records in a Table

J

John Baker

Hi:

I have a table with many months of data on it. Using the Access Query Interface (NOT the
SQL interface) I have matched this table with another table on the basis of dates. I wish
to remove all the records that match on the original table. The Query itself works fine,
and selects the records I want to get rid of, BUT when I try and change it to a delete
query and execute, it asks me for the table I wish to remove records from. I have no idea
how to tell it which table I want since the query process does not permit that, and the
drop down menu with the query wont allow me to select "From" to specify the file. This is
Access 2000!

Can someone point me in the right direction please?

Thanks

John Baker
 
L

Lynn Trapp

John,
Let me suggest that you open the query in SQL View, copy and paste the text
here, and someone should be able to help you sort it out.
 
J

John Baker

Lynn:

Thanks for the suggestion. Here is the SQL version of the query. The History file is the
one that should have the records removed.


DELETE History.weekupdated
FROM History INNER JOIN [History LatestUpdateDate] ON History.weekupdated = [History
LatestUpdateDate].MaxOfweekupdated;

Best

John
 
J

John Vinson

Lynn:

Thanks for the suggestion. Here is the SQL version of the query. The History file is the
one that should have the records removed.


DELETE History.weekupdated
FROM History INNER JOIN [History LatestUpdateDate] ON History.weekupdated = [History
LatestUpdateDate].MaxOfweekupdated;
Click to expand...

Try changing this to

DELETE History.*
FROM History INNER JOIN [History LatestUpdateDate] ON
History.weekupdated = [History LatestUpdateDate].MaxOfweekupdated;

However, if [History LatestUpdateDate] is - as it appears - a Totals
query, the entire query will almost surely not be updateable. You may
need to use the DMax() function rather than a query. If you need help
doing this, and if it *is* a totals query, please post its SQL too.

John W. Vinson[MVP]
 
J

John Baker

John:

Thank you. Yes the query was a totals querry. I didnt realize thhat would be a problem.
Using the DMAX function, it all works fine.

FYI the SQL looks like this now:

DELETE History.*, History.weekupdated
FROM History
WHERE (((History.weekupdated)=DMax("[weekupdated]","[History]")));


Thanks again

JOhn Baker

John Vinson said:
Lynn:

Thanks for the suggestion. Here is the SQL version of the query. The History file is the
one that should have the records removed.


DELETE History.weekupdated
FROM History INNER JOIN [History LatestUpdateDate] ON History.weekupdated = [History
LatestUpdateDate].MaxOfweekupdated;
Click to expand...

Try changing this to

DELETE History.*
FROM History INNER JOIN [History LatestUpdateDate] ON
History.weekupdated = [History LatestUpdateDate].MaxOfweekupdated;

However, if [History LatestUpdateDate] is - as it appears - a Totals
query, the entire query will almost surely not be updateable. You may
need to use the DMax() function rather than a query. If you need help
doing this, and if it *is* a totals query, please post its SQL too.

John W. Vinson[MVP]
Click to expand...
 
Top