kg6lfz
asked on
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.Invo
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.
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.Invo
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.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
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.
The bottom line is - pick a standard and stick with it.
Glad we could help! :)
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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==>tblOrderDetail
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.