Re: Compare 2 files and delete
From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 03/31/04
- Next message: Tom Ellison: "Re: How to select row numbers?"
- Previous message: Mark Burns: "Re: Saving complex query blows up Access 2002!"
- In reply to: Dennis: "Re: Compare 2 files and delete"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 31 Mar 2004 08:12:13 -0800
If you are working with the query builder, once the query
is changed to a delete query there will be a row
titled "Delete". The options on this row are "From"
and "Where". Select "From" below a field from the table
that you want to delete the records from, and
select "Where" beneath any fields from other tables that
you want to use to specify criteria to restrict the
deletions from the "From" table.
Hope this helps.
-Ted Allen
>-----Original Message-----
>John,
>
> Once I change the below query (Which seems to work
>Beautifully) to a delete query, and I run the delete
query
>Access asks to "specify the table containing the records
>you want to delete". 1)How can I do that? and 2)I can
>uncheck the show box in a select query then convert it
to
>a delete query(Which does not offer the option)but the
>field still shows up in the query result, is that a
>problem?
>
>Thanks,
>
>Dennis
>
>>-----Original Message-----
>>On Tue, 30 Mar 2004 19:21:55 -0800, "Dennis"
>><anonymous@discussions.microsoft.com> wrote:
>>
>>>I need to compare two files then delete records in one
>>>file that do not have a matching records in the other.
I
>>>have tried a couple of ideas but I have not been able
to
>>>figure out how to do it. I can compare the two files
in
>>>append wizard but not in the delete wizard. It seems I
>>>need to combine the two functions? Does anyone have
any
>>>examples of how I can do this?
>>>
>>>Thanks Dennis
>>
>>First off - they're not "files", they're Tables. Jargon
>can be
>>confusing!
>>
>>You'll need to build the query yourself; the wizards
are
>pretty
>>limited. I assume that both tables have the same
Primary
>Key field,
>>and that matching primary keys is sufficient to
>identify "matching
>>records".
>>
>>First, back up your database - any time you do a mass
>delete there's a
>>risk you'll delete too much!
>>
>>If so, create a Query joining the two tables by that
>field. Select the
>>Join line and choose option 2 or 3 - "Show all records
in
><the table
>>you want to delete from> and matching records in <the
>other table>".
>>
>>Select only the joining field from the second table.
Put
>a criterion
>>on it of IS NULL.
>>
>>Open the query to make sure that these are in fact the
>records you
>>want to delete. If so, change the query to a DELETE
>query, and uncheck
>>the Show box under the field with the "is null"
criterion.
>>
>>The SQL will be something like
>>
>>DELETE * FROM tableA LEFT JOIN tableB
>>ON tableA.ID = tableB.ID
>>WHERE TableB.ID IS NULL;
>>
>> John W. Vinson[MVP]
>> Come for live chats every Tuesday and Thursday
>>http://go.compuserve.com/msdevapps?loc=us&access=public
>>.
>>
>.
>
- Next message: Tom Ellison: "Re: How to select row numbers?"
- Previous message: Mark Burns: "Re: Saving complex query blows up Access 2002!"
- In reply to: Dennis: "Re: Compare 2 files and delete"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
