Solved

delete records with SQL/VBA ACCESS

Posted on 2009-07-01
7
528 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
[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
  • 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

730 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