delete records with SQL/VBA ACCESS

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

Karl001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chuck WoodCommented:
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
Karl001Author Commented:
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
Chuck WoodCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
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
Karl001Author Commented:
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
Karl001Author Commented:
I did the subquery. Now it's working,  I had to replace JOIN by INNER JOIN
Thanks,
Carol
0
Jeffrey CoachmanMIS LiasonCommented:
OK,

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

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.