Delete All But...

[marion34s]

Hello Access Braintrust,

I have a table [Project Costs] which will be regularly updated with the most current information from our accounting files. I have included a Report Date with each entry so that I can tell what is the most up-to-date info. I have been able to create a query that will show only the most recently reported entries:

SELECT
[Project Costs].ProjectID,
Max([Project Costs].[Report Date]) AS [Date_Reported]
FROM [Project Costs]
GROUP BY ProjectID

What I have not been able to figure out is how to DELETE all the records except for the most recent that is returned by that query.

I am most certainly open to any and all solutions to remove the older entries from the table. I do not need to "archive" the old data since it is no longer relevant once there are updated numbers and since the db is backed up before entering any of the updated information.

Thank you in advance once again!

 

[Minty]

If you can successfully query what you need, why bother trying to delete the older data?
Are you running out of space or is something else making the removal of the data necessary?

If it really is a requirement to only keep the latest data then air code something like

DELETE  * FROM  yourTable WHERE [Report Date] NOT IN (SELECT

Max([Project Costs].[Report Date]) AS [Date_Reported]
FROM [Project Costs]
GROUP BY ProjectID)

Test it using a select version first to make sure it's finding the right records to delete.

Syntax guide and examples here https://www.fmsinc.com/MicrosoftAccess/query/snytax/delete-query.html#Delete_Query_SQL_Syntax

 

[The_Doc_Man]

Minty beat me to it by a couple of minutes. Agree also with his advice to test it first by making the DELETE a SELECT. Then if it looks good, change the SELECT back to a DELETE.

 

[marion34s]

If you can successfully query what you need, why bother trying to delete the older data?
Are you running out of space or is something else making the removal of the data necessary?

If it really is a requirement to only keep the latest data then air code something like

DELETE  * FROM  yourTable WHERE [Report Date] NOT IN (SELECT

Max([Project Costs].[Report Date]) AS [Date_Reported]
FROM [Project Costs]
GROUP BY ProjectID)

Test it using a select version first to make sure it's finding the right records to delete.

Syntax guide and examples here https://www.fmsinc.com/MicrosoftAccess/query/snytax/delete-query.html#Delete_Query_SQL_Syntax

Thank you Minty! I Tried the code that you suggested and received the error message pictured below.

"You have written a subquery that can return more than one field without using the EXISTS reserved word int he main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

 

[theDBguy]

Hi. I would second the idea of keeping the older records in case you need to review them later. But, another approach to only keep new data is to simply "update" the table with the new data rather than keep adding to it.

 

[marion34s]

Hi. I would second the idea of keeping the older records in case you need to review them later. But, another approach to only keep new data is to simply "update" the table with the new data rather than keep adding to it.

Hi DBguy,
Do you have a suggestion on how to make my entry form update or add to the table? That was actually my first idea but was not able to accomplish it after many frustrating hours of trial and error (lots of errors).
Thanks!

 

[Minty]

Thank you Minty! I Tried the code that you suggested and received the error message pictured below.
View attachment 79528
"You have written a subquery that can return more than one field without using the EXISTS reserved word int he main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

You have left the project ID in the subquery. Look carefully at what I posted. Try this first

SELECT * FROM [Project Costs] WHERE [Report Date] NOT IN
(SELECT Max([Project Costs].[Report Date]) AS [Date_Reported]
FROM [Project Costs]
GROUP BY ProjectID)

 

[marion34s]

You have left the project ID in the subquery. Look carefully at what I posted. Try this first

SELECT * FROM [Project Costs] WHERE [Report Date] NOT IN
(SELECT Max([Project Costs].[Report Date]) AS [Date_Reported]
FROM [Project Costs]
GROUP BY ProjectID)

Thank you for the clarification and the help so far! The SELECT did not return anything...

 

[The_Doc_Man]

I'm thinking that there is another way if you wanted to do the deletions. Surely you have a project ID of some sort. Create two queries. By the way, I'm removing spaces from names because that way you don't need brackets. Just a suggesting in passing to save you complexity in typing.

Q1:  
SELECT  ProjectID, MAX(ReportDate) AS MaxDate FROM ProjectCosts GROUP BY ProjectID ;

Q2:  
DELETE * FROM ProjectCosts INNER JOIN Q1 ON  Q1.ProjectID = ProjectCosts.ProjectID WHERE Q1.MaxDate <> ProjectCosts.ReportDate ;

This will join up all records from a given project to the query showing the max date for each project. Then if the dates DON'T match (and for each project, only one WILL match), delete the record.

HOWEVER, unless you have tens of thousands of such records accumulating each day, keeping older records around for historical purposes isn't wrong.

 

[marion34s]

I'm thinking that there is another way if you wanted to do the deletions. Surely you have a project ID of some sort. Create two queries. By the way, I'm removing spaces from names because that way you don't need brackets. Just a suggesting in passing to save you complexity in typing.

Q1: 
SELECT  ProjectID, MAX(ReportDate) AS MaxDate FROM ProjectCosts GROUP BY ProjectID ;

Q2: 
DELETE * FROM ProjectCosts INNER JOIN Q1 ON  Q1.ProjectID = ProjectCosts.ProjectID WHERE Q1.MaxDate <> ProjectCosts.ReportDate ;

This will join up all records from a given project to the query showing the max date for each project. Then if the dates DON'T match (and for each project, only one WILL match), delete the record.

HOWEVER, unless you have tens of thousands of such records accumulating each day, keeping older records around for historical purposes isn't wrong.

Thank you Doc_Man! I'm so close and please excuse my SQL/Access ignorance...

I am not receiving an error "Specify the table containing the records you want to delete." (image below) I thought it did - it looks like it does to me...

 

[ebs17]

DELETE ProjectCosts.* FROM ProjectCosts INNER JOIN ...

Possibly also:

DELETE DISTINCTROW ProjectCosts.* FROM ProjectCosts INNER JOIN ...

My suggestion for a one-step query:

DELETE
FROM
   [Project Costs] AS P
WHERE
   EXISTS
      (
         SELECT
            NULL
         FROM
            [Project Costs] AS X
         WHERE
            X.ProjectID = P.ProjectID
               AND
            X.[Report Date] > P.[Report Date]
      )

 

[theDBguy]

Hi DBguy,
Do you have a suggestion on how to make my entry form update or add to the table? That was actually my first idea but was not able to accomplish it after many frustrating hours of trial and error (lots of errors).
Thanks!

Hi. I don't mean to confuse the topic; but since you asked, see if this article gives you any ideas on how to do that. Hope it helps...

Update Query with Append

I can't say how often this situation comes up in real life, but an UtterAccess member wanted to find out how to run a single query that will...

thedbguy.blogspot.com

 

[marion34s]

Hi. I don't mean to confuse the topic; but since you asked, see if this article gives you any ideas on how to do that. Hope it helps...

Update Query with Append

I can't say how often this situation comes up in real life, but an UtterAccess member wanted to find out how to run a single query that will...

thedbguy.blogspot.com

Thanks DB_Guy! This was a good article to read - what I was struggling with was the settings for a form that would allow me to either append or update entries (that was my first thought as how to solve the problem from the OP which would have made the desire to delete old data moot). I just was not able to figure out how to get it to work so I started to explore other options to achieve the end product (of course I ran into difficulties with the alternatives too).
Thanks!

 

[The_Doc_Man]

As EBS noted:

DELETE ProjectCosts.* FROM ProjectCosts INNER JOIN Q1 ON  Q1.ProjectID = ProjectCosts.ProjectID WHERE Q1.MaxDate <> ProjectCosts.ReportDate ;

 

[theDBguy]

Thanks DB_Guy! This was a good article to read - what I was struggling with was the settings for a form that would allow me to either append or update entries (that was my first thought as how to solve the problem from the OP which would have made the desire to delete old data moot). I just was not able to figure out how to get it to work so I started to explore other options to achieve the end product (of course I ran into difficulties with the alternatives too).
Thanks!

Hi. Thanks! Again, to avoid confusing the topic, I would suggest trying out all the other suggestions first until you can get something to work. Then later, if you want, we can go back to this topic to give it a try. I had the impression you "import" all the new data in a batch, which this approach would fit well, I think. But if you're simply adding one record at a time, we can also discuss that later. For example, rather than blindly entering the new data, you could try searching the table first for any existing record. If there is, update it; otherwise, feel free to add the new one. Cheers!