SQL WHERE = Query Results

[auujxa2 via AccessMonster.com]

This is in conjunction with my previous thread. I'll try and simplify my
request.

How do I write this SQL:

" WHERE [Department #] = .... The values from [DeptQry]![Department #]?

Thank you in advance

 

[KARL DEWEY]

Post the rest of the quey SQL.

 

[auujxa2 via AccessMonster.com]

The code below deletes the departments from the MasterTbl in another database,
based on the departments selected from the list. I'd like to change that, so
it deletes the records from a query NOT the selected items.

Dim strSQL As String
Dim varItem As Variant
Dim frm As Form, ctl As Control
Set frm = Forms!frmSetup
Set ctl = frm!lstMyDepartments
Set db = OpenDatabase("F:\Vendor Matrix\Data\Source\Maintenance Database -
F09\MENS Fall FY09 Vendor Maintenance.mdb")
Set rstDate = db.OpenRecordset("MasterTbl")

answer = MsgBox("Did you already export your changes?", vbYesNo, "WAIT!")

If answer = vbNo Then

DoCmd.SetWarnings False

For Each varItem In ctl.ItemsSelected

strSQL = "DELETE * FROM [MasterTbl] IN 'F:\Vendor Matrix\Data\Source\
Maintenance Database - F09\MENS Fall FY09 Vendor Maintenance.mdb'" & _
" WHERE [Department #] = " & ctl.ItemData(varItem) & ""

DoCmd.RunSQL strSQL

Post the rest of the quey SQL.

This is in conjunction with my previous thread. I'll try and simplify my
request.

[quoted text clipped - 4 lines]

Thank you in advance

 

[Marshall Barton]

This is in conjunction with my previous thread. I'll try and simplify my
request.

How do I write this SQL:

" WHERE [Department #] = .... The values from [DeptQry]![Department #]?

Just guessing what you want, but try

SELECT ...
FROM sometable INNER JOIN DeptQry
ON sometable.[Department #] = DeptQry.[Department #]

 

[auujxa2 via AccessMonster.com]

In a nutshell, that is basically what I want. But I'm not SQL savy enough to
write the full code, since I have an "IN" statement too.

I need to say:

delete department # from MasterTbl in the other database, if it equals
Department # from DeptQry in the current database.

This is in conjunction with my previous thread. I'll try and simplify my
request.

How do I write this SQL:

" WHERE [Department #] = .... The values from [DeptQry]![Department #]?

Just guessing what you want, but try

SELECT ...
FROM sometable INNER JOIN DeptQry
ON sometable.[Department #] = DeptQry.[Department #]

 

[Marshall Barton]

In a nutshell, that is basically what I want. But I'm not SQL savy enough to
write the full code, since I have an "IN" statement too.

I need to say:

delete department # from MasterTbl in the other database, if it equals
Department # from DeptQry in the current database.

This is in conjunction with my previous thread. I'll try and simplify my
request.

How do I write this SQL:

" WHERE [Department #] = .... The values from [DeptQry]![Department #]?

Just guessing what you want, but try

SELECT ...
FROM sometable INNER JOIN DeptQry
ON sometable.[Department #] = DeptQry.[Department #]

I think this should do that:

strSQL = "DELETE MasterTbl.* " & _
"FROM MasterTbl " & _
"IN 'F:\Vendor Matrix\Data\Source\Maintenance Database
- F09\MENS Fall FY09 Vendor Maintenance.mdb'" & _
" INNER JOIN DeptQry
"ON MasterTbl.[Department #] = DeptQry.[Department #]

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

PERHAPS

DELETE
FROM SomeTable
WHERE [Department #] IN
(SELECT [Department #]
FROM [DeptQry])


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County