Delete record from table based on query (1 Viewer)

chineloogbonna

Registered User.
Local time
Today, 07:27
Joined
Jul 30, 2018
Messages
65
Afternoon,
I've been trying to figure this out for days. I feel like its simple but I just can't get it.

I have two tables, lets say Table1 and Table2.

I am trying to delete duplicate records in Table 2 that are already in Table1. My query, "query1" finds the duplicate records with joined tables but I cannot figure out how to delete the records(s) from Table2 based on the query?

Thanks in advance for any help you can provide!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:27
Joined
Oct 29, 2018
Messages
21,357
Hi. Let's back up a bit... Let's say Table 1 has records 1, 2, and 3 in it and Table 2 has records 2, 3, and 4. Are you saying you simply want to delete records 2 and 3 from Table 2? If so, you could try this query:
Code:
DELETE FROM Table2 INNER JOIN Table1 ON Table1.FieldName=Table2.FieldName
Make sure you have a backup copy in case it doesn't do what you wanted.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:27
Joined
Jan 23, 2006
Messages
15,362
Why are there similar records in 2 tables? Need more info on table design and what your database is about.
 

chineloogbonna

Registered User.
Local time
Today, 07:27
Joined
Jul 30, 2018
Messages
65
I have a PickUp table (Table1) and Completed table (Table2).

On button click a record from listbox_pickup gets:
- added to the ReadyForPickUp table (Table3)
- removed from the PickUp table (Table1)
- added to the Completed table (Table2).

However, there is an option on the form that houses the ReadyForPickup table as a subform that allows a user to remove the record and place back onto the PickUp table in case of accidental clicking.

What I need to be able to accomplish is when the record is moved back to the PickUp table do to an error, it also gets removed from the Completed table.

Hope that made sense!

Thank You!
 

chineloogbonna

Registered User.
Local time
Today, 07:27
Joined
Jul 30, 2018
Messages
65
After filling in my table and field names with,
DELETE FROM Table2 INNER JOIN Table1 ON Table1.FieldName=Table2.FieldName. I get an "Specify the table you containing the records you want to delete" error message.
 

Mark_

Longboard on the internet
Local time
Today, 06:27
Joined
Sep 12, 2017
Messages
2,111
Pickup, Completed, and ReadyForPickUp sound like statuses that should be IN the record, not the names of tables.

If you have a status field you can avoid having these separate tables.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
Code:
DELETE table2.*, table2.[commonFieldName]
FROM table2
WHERE 
(((table2.[commonFieldname]) In (SELECT [commonFieldname] FROM table1)));
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:27
Joined
Jan 23, 2006
Messages
15,362
I agree with Mark. Sounds like you may have a design issue. If the only difference in the purpose of the three tables is to identify STATUS, then 1 table with Status seems appropriate.
Delete queries can be dangerous if you don't get your criteria set up properly. Many have removed/deleted data unintentionally because of improper scope in the WHERE clause.
Good luck with your project.
 

Mark_

Longboard on the internet
Local time
Today, 06:27
Joined
Sep 12, 2017
Messages
2,111
A question from a design standpoint, is this part of an order fulfillment system? More specifically, do you have the following situation?

1) Customer places order for one or more items
2) Some or all of the order can be shipped at any time, based on available stock
 

chineloogbonna

Registered User.
Local time
Today, 07:27
Joined
Jul 30, 2018
Messages
65
Thank you guys for all your replies. I am going to try adding a STATUS column and then remove the duplicate record using just the one table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:27
Joined
Oct 29, 2018
Messages
21,357
Thank you guys for all your replies. I am going to try adding a STATUS column and then remove the duplicate record using just the one table.
Hi. Good luck with your project.
 

chineloogbonna

Registered User.
Local time
Today, 07:27
Joined
Jul 30, 2018
Messages
65
I ended up using one table and doing a duplicate delete query. Works perfect. Thank you for the advise. Not sure if I need to click anything to mark as resolved so?

Thanks again!
 

isladogs

MVP / VIP
Local time
Today, 13:27
Joined
Jan 14, 2017
Messages
18,186
I've marked this as SOLVED for you.
For future reference, you do this from the Thread Tools menu in the blue bar at the top of the thread
 

Users who are viewing this thread

Top Bottom