Delete data with criteria in different table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I tried to use a query to delete data in a table in specified date. The date
criteria is in different table. I built a link between 2 fields. When I run
the query, Access said " Could delete from specified table." I tried to run a
query with same situiation in example from Microsoft Press. The same dialog
box was returned. The Accees help said I may not have authority to do so. I
am administrator of my PC and database. And I didn't do any changes of
setting in ACCESS.
 
Post the SQL statement that you're trying to use, and we'll show you how to
rewrite it.
 
The SQL statement is
DELETE [Hourly sales].*, [Hourly sales].dst_date, [Date of data].Date
FROM [Hourly sales] INNER JOIN [Date of data] ON [Hourly sales].dst_date =
[Date of data].Date;
 
I am assuming that you only want to delete data from the Hourly sales table?

DELETE [Hourly sales].*
FROM [Hourly sales]
WHERE [Hourly sales].dst_date IN
(SELECT [Date of data].[Date]
FROM [Date of data]);


or this should also work:

DELETE DISTINCTROW [Hourly sales].*
FROM [Hourly sales] INNER JOIN [Date of data]
ON [Hourly sales].dst_date = [Date of data].[Date];


Note that you shouldn't use Date and other similar words as field names.
They are reserved words in ACCESS, and you can create great confusion for
ACCESS by using them, especially if you use them without [ ] delimiters. For
more info on which words should not be used as field names, control names,
etc., see this Knowledge Base article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Ken Snell
<MS ACCESS MVP>


--

Ken Snell
<MS ACCESS MVP>



Dennis Cheung said:
The SQL statement is
DELETE [Hourly sales].*, [Hourly sales].dst_date, [Date of data].Date
FROM [Hourly sales] INNER JOIN [Date of data] ON [Hourly sales].dst_date =
[Date of data].Date;


Ken Snell said:
Post the SQL statement that you're trying to use, and we'll show you how to
rewrite it.

--

Ken Snell
<MS ACCESS MVP>

The
date I
run to
run a so.
I
Click to expand...
Click to expand...
 
Back
Top