Delete Query of records in a table based on it's finding in two other tables

[Hendy88]

Howdy all,

I'm playing around in a test database I've created and am having a
difficult time setting up a Delete Query that checks for a field in
two other tables and if it's there, it deletes the record in the
original table. So for example, I've got a table I called "NEW DATA"
that has the following fields:

CLAIM MVA RA VIN

I've got two other tables (one called "2005 DATA" and one called "2006
DATA") and I want the delete query to check for the MVA that's in the
NEW DATA table, in these two tables and if it finds that it's in
either one of these, I then want the record deleted in the "NEW DATA"
table.

In my Design View for this Delete Query I have all THREE tables with
the following relationships:

NEW DATA (MVA) Field -> 2005 DATA (MVA) field
NEW DATA (MVA) Field -> 2006 DATA (MVA) field

And for my setup I have the following:

Field: NEW DATA.*
Table: NEW DATA
Delete: From

Field: MVA
Table: 2005 DATA
Delete: Where
Criteria: [NEW DATA]![MVA]

Field: MVA
Table: 2006 DATA
Delete: Where
Criteria: [NEW DATA]![MVA]

But when I run it, it says "Cannot delete from specified tables."

Even taking out the extra table and just doing, let's say for example,
NEW DATA and 2005 DATA tables, still produces the same result.

Have I designed this backwards?

 

[Guest]

consider an entirely different approach.

set up an Unmatched Duplicates query using the available Wizard in new
query.....

double check that it is returning the appropriate records correctly....

then you can take that query and while in Design View select it as a Make
Table query....

run it once and it will create a new table with the unmatched as you need -
you can then change that table name if you need to....

 

[John W. Vinson]

Howdy all,

I'm playing around in a test database I've created and am having a
difficult time setting up a Delete Query that checks for a field in
two other tables and if it's there, it deletes the record in the
original table. So for example, I've got a table I called "NEW DATA"
that has the following fields:

CLAIM MVA RA VIN

I've got two other tables (one called "2005 DATA" and one called "2006
DATA") and I want the delete query to check for the MVA that's in the
NEW DATA table, in these two tables and if it finds that it's in
either one of these, I then want the record deleted in the "NEW DATA"
table.

Well... you've got a flawed design. Storing data in tablenames is
NEVER a good choice. Could you consider adding a ClaimYear field (with
values 2005, 2006, 2007 and so on)? You can always use a Query
selecting on this field to recover the individual year values. If you
object that it will make your table "too big" I'll just say - if you
have the same data in three tables, it's already *even bigger* because
of the overhead of the three tables.

What are the Primary Keys of these tables? MVA? Or do they have a
primary key? Does the MVA field uniquely identify a record in the
table? If so, you can and should make it the Primary Key.

In my Design View for this Delete Query I have all THREE tables with
the following relationships:

NEW DATA (MVA) Field -> 2005 DATA (MVA) field
NEW DATA (MVA) Field -> 2006 DATA (MVA) field

Referential integrity checkbox checked or not?

And for my setup I have the following:

Field: NEW DATA.*
Table: NEW DATA
Delete: From

Field: MVA
Table: 2005 DATA
Delete: Where
Criteria: [NEW DATA]![MVA]

Field: MVA
Table: 2006 DATA
Delete: Where
Criteria: [NEW DATA]![MVA]

The simplest - and only really effective - way to post query designs
is to use SQL view. This doesn't show how the tables are joined; and
if you join New Data to both 2005 Data and 2006 Data, you'll only
delete records which exist in BOTH tables.

My first choice would be to create a new table, AllData, with a
ClaimYear field, and use two Append queries to fill it with the data
from your non-normalized tables:

INSERT INTO AllData(ClaimYear,CLAIM,MVA, RA, VIN)
SELECT (2005),CLAIM,MVA, RA, VIN
FROM [2005 Data];

and a similar query for 2006 Data. If MVA is in fact unique, you
should then be able to run

DELETE [New Data].*
FROM [New Data]
INNER JOIN [AllData]
ON NewData.MVA = AllData.MVA;

If you're unable or unwilling to do this, you can run TWO queries like
this; just substitute [2005 Data] for AllData, run it, and then do it
again for [2006 Data].

If your table doesn't have MVA as a primaryh key - or doesn't have a
primary key - please explain the table structure.

John W. Vinson [MVP]

 

[Hendy88]

Well I would say it's necessarily flawed. It works quite well
actually and I DON'T have the same information in three different
tables AND I've got a Claim's Date field in both all three tables.
The start of the process involves updating and then appending records
straight into 2005 DATA table or the 2006 DATA table based on the
Claim Date. The primary key in both tables is the actual CLAIM field
itself. Reason is because not every claim record that comes in off
that linked spreadsheet has VIN or RA data. Sometimes those fields
are blank (until they get filled in later). The only other field that
is sure to come in with every claim record is the MVA field, however
if an MVA number is recycled throughout the year, it's possible to
have duplicate MVA numbers. Actually the fields are as follows:

CLAIM CLAIMDATE MVA RA VIN

Based off the CLAIMDATE field, the first thing that happens is an
Update Query that updates any new information as pertains to the MVA,
RA, and VIN fields and either dumps the information into 2005 DATA or
2006 DATA where the CLAIM IS already present. After updating any
current records, it's an Append Query that ADDS any new records that
don't exist into the 2005 DATA and 2006 DATA where the CLAIM is NOT
present. So there are no duplicate records between the two tables as
every CLAIM is never recycled and has it's own particular date which
is used to sort into either DATA table.

The "flaw" is actually in the linked data (which cannot be altered as
it comes straight from an Oracle table from a Claims System). The
problem is that when a Claim is first brought in (or new), it actually
has a "temp claim number" that begins with a dollar sign. (i.e.
$DF343C). However, after the claim is "worked" it then becomes a real
claim number that is totally different from the temp claim number AND
doesn't start with a dollar sign. What's supposed to happen is after
a claim is worked, then the original dollar sign claim number is
supposed to disappear from the Oracle table, but it doesn't (that's a
whole other issue, but for now...).

The NEW DATA table is all the dollar sign ($) claim numbers from the
linked spreadsheet. The original question was how can I check for the
MVA field in the 2005 DATA and 2006 DATA tables and if it's there,
delete the record that contains that same MVA field in the NEW DATA.
This way it really only leaves the real and true NEW claim numbers
that have not been worked. The way it is now, is these temp dollar
sign claim numbers are showing in NEW DATA as if they need to be
worked, when in fact they HAVE been worked and are sitting in either
2005 DATA or 2006 DATA with a good claim number.

As said earlier, the MVA sometimes gets recycled through the year or
over several years. So it's possible to have the same MVA number in
BOTH 2005 DATA and 2006 DATA tables. However, the claim number
wouldn't be the same or the claim date.

I've created an unmatched query that says:

SELECT [NEW DATA].MVA, [NEW DATA].CLAIMDATE
FROM ([NEW DATA] LEFT JOIN [2005 DATA] ON [NEW DATA].MVA = [2005
DATA].MVA) LEFT JOIN [2006 DATA] ON [NEW DATA].MVA = [2006 DATA].MVA
WHERE ((([2005 DATA].MVA)=[NEW DATA]![MVA]) AND (([2005
DATA].CLAIMDATE)=[NEW DATA]!CLAIMDATE)) OR ((([2006 DATA].MVA)=[NEW
DATA]![MVA]) AND (([2006 DATA].CLAIMDATE)=[NEW DATA]!CLAIMDATE));

And this DOES NOT only show "records which exist in BOTH tables". It
shows me results if they're found in EITHER table. The problem NOW is
that I'm running a delete query up against the results shown in this
query but the results vary. In other words, the query says that there
are 1948 records, but the delete query wants to delete 2025.