Solved

delete records with SQL/VBA ACCESS

Posted on 2009-07-01
7
522 Views
Last Modified: 2013-11-27
Hi,
I would like to know the SQL to delete all records in
TBL_CONTACT where TBL_USER.done = true

Structure of tables/query vs fields:

TBL_CONTACT             QRY_USER                TBL_USER
  userName   -------->  userName
                                       userId        -------->     userId
                                                                            done

             

DELETE *
FROM tbl_Contact
WHERE tbl_Contact.userName 
IN(select userName  from qry_User  ?????
     ??? where (qry_User.userId = tbl_User.userId,   tbl_User.Done=true);

Open in new window

0
Comment
Question by:Karl001
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24757215
I think this should do what you want. As always, test new code on a COPY of your database.

chuck
DELETE * FROM tbl_Contact WHERE tblContact.userName IN (SELECT userName FROM qry_User JOIN tblUser ON qry_User.userID = tbl_User.userID WHERE tbl_User.Done=True)

Open in new window

0
 

Author Comment

by:Karl001
ID: 24765536
It's doesn't work.
I have an syntax error message. I can't find where.

DELETE * FROM tbl_Contact
WHERE tbl_Contact.userName IN (SELECT userName FROM qry_User JOIN tbl_User ON qry_User.userID = tbl_User.userID WHERE tbl_User.Done=True)
0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 500 total points
ID: 24765692
Try the subquery on its own.

chuck
SELECT userName FROM qry_User JOIN tbl_User ON qry_User.userID = tbl_User.userID WHERE tbl_User.Done=True

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24765863
Something doesn't seem quite right.

Why is there a query between the two tables?
Are you saying that TBL_CONTACT and TBL_USER are not directly related?
Why not?

Perhaps I am not understanding something
This seems like a design issue.

Can you post the fields in these tables?

JeffCoachman
 
0
 

Author Comment

by:Karl001
ID: 24766729
Hi Jeff,

You are right, it's not a good design. But it's temporary, it is for merging data from old database to a new one, with a new structure.

Thanks,
Carol
0
 

Author Closing Comment

by:Karl001
ID: 31598883
I did the subquery. Now it's working,  I had to replace JOIN by INNER JOIN
Thanks,
Carol
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24770124
OK,

Looks like cwood-wm-com's got you covered.
;-)

JeffCoachman
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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