Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access - Delete Records In A Table

Posted on 2014-03-11
7
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

715 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