Access Query Delete with Inner Join

ACCESS 2013 Query
I want to delete records from table 1 with inner join.  My query is

Delete Table1.* from Table1
Inner Join Table2 on Table1.invoice=Table2.Invoice

The above query works but it deletes records from Table2.  I switched Table1 with Table2 but it still deletes records from Table2.  How can I delete records from Table1?

Thank you very much in advance.
kg6lfzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
Try this:  Delete  from Table1 where Table1.Invoice in (Select Table2.Invoice from Table2)

And a note regarding field names:  you shouldn't have fields with the same name in different tables.  If you're using Invoice to as the key in Table1, and as a foreign key (the linking field, in Access' terminology) in Table2, it should be named FKInvoice.  :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Paul's suggestion of a subquery should solve the problem so give him the points.

When you join multiple tables in a query and the tables are related hierarchically and then delete, you ALWAYS ONLY delete rows from the lowest level child table.  That means if your join is tblCustomer==> tblOrders==>tblOrderDetails  -- the delete would delete only from tblOrderDetails.   However, if the tables are joined PK to PK, you will delete from both tables.  But if you join non-pk to non-pk, you can't delete at all.    Deletes in queries with joins are very confusing and the rules may vary from RDBMS to RDBMS.  Experiment on tables with junk data that allows you to easily identify what will happen.

I disagree on Paul's advice regarding naming.  What are you going to call Invoice in the third table?  If you prefer to specifically identify foreign keys, that's fine but in that case Invoice would be FKInvoice in all tables where it appears.
Paul Cook-GilesSenior Application DeveloperCommented:
Pat, thanks for the info about hierarchic priority in deletion...  I didn't know that.  :)

And Pat is correct;  if the same data is a foreign key in multiple tables, I would call it FKInvoice in all of the tables.
PatHartmanCommented:
I use "ID" as the suffix for ALL autonumbers and "CD" for user assigned unique IDs.  Some primary keys that are common may have no suffix.  State is one example since the common use of State is the two digit code rather than the long name.  The PK names relate to the table they control and are NEVER named simply ID.  The foreign keys always use the name of the PK unless there are multiple relationships or a self referencing situation such as SupervisorID in the Employee table.  EmployeeID has to be the PK of the individual record but SupervisorID points to the EmployeeID in a different row.  in other cases, we may end up two references to state from the same record as in BillingST and ShippingST.  These are the exception though rather than the rule since self-referencing and multiple references just don't occur very often.

The bottom line is - pick a standard and stick with it.
Paul Cook-GilesSenior Application DeveloperCommented:
Glad we could help!  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.