Delete Records found on another table via Delete Query (1 Viewer)

[Wolf]

Hi,
Trying to delete records on table SBSales where the date on the record match a date found on a record on table SBSLSPR30.

For example delete records when SBSales.SaleDate is found on SBSLSPR30.[Date].

Whats the simplest way to accomplish this.

 

[theDBguy]

Hi. What happened when you tried using a DELETE query where you JOINed the two tables on the Date fields?

 

[plog]

Use an INNER JOIN:

DELETE SBSales.*
FROM SBSales INNER JOIN SBSLSPR30 ON SBSales.SalesDate = SBSLSPR30.Date;

2 notes:

1. [Date] is a poor choice for a field name because its a reserved word. It can cause issues with coding and querying.

2. Deleting records is often a bad idea. If you have a list of dates to exclude (SBSLSPR30.Date) then you just create a regular SELECT query to omit them.

 

[arnelgp]

For existing objects with names that contain reserved words, you can avoid errors by surrounding the object name with brackets ([ ])
-source: https://docs.microsoft.com/en-us/office/troubleshoot/access/reserved-words

Delete * From SBSales Where SalesDate In (Select [Date] From  SBSLSPR30);

 

[Wolf]

For existing objects with names that contain reserved words, you can avoid errors by surrounding the object name with brackets ([ ])
-source: https://docs.microsoft.com/en-us/office/troubleshoot/access/reserved-words

Delete * From SBSales Where SalesDate In (Select [Date] From  SBSLSPR30);

This worked great. Thank you
I ended up changing it to this;

DELETE SBSales.*, SBSales.SaleDate, *
FROM SBSales
WHERE (((SBSales.SaleDate) In (Select [Date] From SBSLSPR30)));

 

[theDBguy]

This worked great. Thank you
I ended up changing it to this;

DELETE SBSales.*, SBSales.SaleDate, *
FROM SBSales
WHERE (((SBSales.SaleDate) In (Select [Date] From SBSLSPR30)));

Hi. Glad to hear you got it sorted out. Good luck with your project.