Delete Query with Join

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I am trying to run a delete query where I have two tables, one to many
relationship, and I would like to delete all of the records in both tables
where the criteria field is in my "one" of the one to many relationship.
For example I am tracking invoices with an invoice header table, which has
invoice#, customer#, date and time and I have an invoice detail table with
my invoice#, product#, quantity and price. I am joined on the invoice# but
I want to delete all records from both tables where the customer# = 123.

Any ideas?
Thanks,
Steve
 
I am trying to run a delete query where I have two tables, one to many
relationship, and I would like to delete all of the records in both tables
where the criteria field is in my "one" of the one to many relationship.
For example I am tracking invoices with an invoice header table, which has
invoice#, customer#, date and time and I have an invoice detail table with
my invoice#, product#, quantity and price. I am joined on the invoice# but
I want to delete all records from both tables where the customer# = 123.

Any ideas?
Thanks,
Steve
Click to expand...

The simplest way to do this is to open the Relationships window and
select the join line between these tables, and check the Cascade
Deletes checkbox. Deleting an invoice number will now delete that
record and all the associated invoice detail records.

Note that this can be risky - you can lose your history of actually
valid invoices pretty easily! For invoicing applications, you might
want to instead "logically" delete: have a yes/no field in the header
table, Yes meaning "deleted". Adjust your forms, reports, and summary
queries to select only undeleted invoices. This gives you the option
to undelete, an option which a true Access delete operation lacks!

John W. Vinson[MVP]
 
Back
Top