Solved

MS Access - Delete Records In A Table

Posted on 2014-03-11
7
320 Views
Last Modified: 2014-03-12
Hi Experts -

Been a while since I wrote any delete queries - and its definitely causing me problems.

I have two tables:
Table 20 contains the universe of all of my records -
Table 15 contains those records I want to delete in Table 20 -

My select query works fine -
When I convert the query to a delete query - I get an error "Could not delete from specified tables".

SQL is attached - however I think this is pretty simple and I should just be able to do this by connecting the two tables, right?

Thanks for your help!
SQL.docx
0
Comment
Question by:Meritex
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:Joe Jenkins
ID: 39921944
You should be able to accomplish this fairly easily.  Assuming the data is the same in each table and column1 is an index between the two tables:

DELETE FROM 020
WHERE EXISTS
   (SELECT 1 FROM 015
        WHERE020.column1 = t=015.column1)

Open in new window

You will need to know an ID column that matches the same records on both tables and adjust the query to a unique column that matches.

Always take a backup before running  a delete query like this to make sure you're good to go.
0
 

Author Comment

by:Meritex
ID: 39921991
I definitely have a backup table.

However, there's no unique identifier ID field to both tables.

The records are unique - and match between tables - but there's no identifier.
0
 
LVL 5

Expert Comment

by:Joe Jenkins
ID: 39922007
So is there no reference point between the two tables that you could use to reference?

Example:

[Company table] (you want to delete from)
company_id
company_name
company_address1
company_address2
company_city
company_state

[table with list of records to delete]
company_id
some_other_column1
some_other_column2
some_other_column3

Both tables share that company_id column.  You need some reference between the tables to make this work.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Meritex
ID: 39922079
The unique records are combinations of various columns - not just one column.

Its the combination of columns 1-10 that create a unique record.

Using your example -
Company table could have company_id 1,2,3,4,5,6,7 etc -
Delete table could also have company id 1,2,3,4,5,6,7 etc -

Its when the combination of:
company_id
company_name
company_address1
company_address2
company_city
company_state
are identical in both tables that I want to delete the record in the company table.
0
 
LVL 5

Accepted Solution

by:
Joe Jenkins earned 500 total points
ID: 39922088
OK, then using the logic in my previous query:

DELETE FROM 020
WHERE EXISTS
   (SELECT 1 FROM 015
        WHERE 
            020.column1 = t=015.column1
            AND 020.column7 = t=015.column7
            AND 020.column2 = t=015.column2
            AND 020.column5 = t=015.column9)

Open in new window

As long as you are matching up the correct columns, you should be good to go!
0
 

Author Comment

by:Meritex
ID: 39923256
Took some syntax guesswork - but got it!

I had my tables flipped in my SQL (015 = 020 instead of 020 = 015) -

Thanks for your help - GREAT walk thru!
0
 
LVL 5

Expert Comment

by:Joe Jenkins
ID: 39923980
Glad to help!  Have a great week!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now