2 table Delete Query?

[Jay]

Hi,

If I have two tables.

One (A) detailing product details, including a 'Model' field.

And another (B), detailing discontinued products. The only field in
this table is the same 'Model' field.

I want a query which will delete all records from (A) containing a
'Model' listed in (B), to leave me with a 'current products' table.

I've got a funny feeling this will be relatively easy but as a newbie
I'd appreciate any help.

At the moment the data isn't even imported so I can set any primary keys
you suggest if this will help.

Any help greatly appreciated.

TIA,

Jason

 

[John Vinson]

Hi,

If I have two tables.

One (A) detailing product details, including a 'Model' field.

And another (B), detailing discontinued products. The only field in
this table is the same 'Model' field.

I want a query which will delete all records from (A) containing a
'Model' listed in (B), to leave me with a 'current products' table.

Do you really want to permanently, irrevokably delete everything
you've ever recorded about a Model? This would need to include all
data in all related tables (sales, invoices, etc. etc.) - might not
some of that data be useful, at least at tax time? Perhaps it would be
better to simply have a Yes/No field in the Products table, Active,
which is True for all current products; you could set it to False when
a product is discontinued (using a very simple Update query joining
the two tables).

If you really really do want to delete all data about discontinued
models, make Model the Primary Key of the discontinued products table.
Create a Query joining the two tables by model. Change it to a Delete
query and select "*" from the Products table as the "Delete From"
(don't select any field from the Discontinued Products table).

Run the query with the ! icon. If you want any information about a
discontinued product in the future.... get out your backup disk,
'cause it's GONE from your database.

John W. Vinson[MVP]

 

[Jay]

Thanks for the reply John.

There are no worries about deleting any data. The two tables are simply
CSV files extracted from the main company Oracle DB (at least I think
it's Oracle.

And I'm only importing into Access for some one-off querying & sorting
etc. Actually, one table details the amount of research we have
received for ALL Models in the main DB, and the other includes a list of
all models in our customer version of the DB (much less than the full,
main DB)

So the analysis is to determine which models aren't in the main DB but
on which we have enough research to enter them in the customer-product
DB. So you can see why I took some liberties with my description I
was just using an analagous situation so as to not go into confusing
details re main & customer DBs.

And the temporary MDB with the CSV tables in will be used to build the
exception reports and that's all.

Many thanks for your help,

Jason