Re: Delete data with criteria in different table

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 10/31/04 

Date: Sat, 30 Oct 2004 22:55:38 -0400

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" <DennisCheung@discussions.microsoft.com> wrote in message
news:4E4619C7-0D1C-42C1-94BC-1E0610AC3771@microsoft.com...
> 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 [MVP]" wrote:
>
> > Post the SQL statement that you're trying to use, and we'll show you how
to
> > rewrite it.
> >
> > -- 
> >
> >         Ken Snell
> > <MS ACCESS MVP>
> >
> > "Dennis Cheung" <DennisCheung@discussions.microsoft.com> wrote in
message
> > news:913CBD55-6B73-4CA3-8F07-D5F272699BC0@microsoft.com...
> > > 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.
> > > -- 
> > > Dennis Cheung
> >
> >
> >