SQL WHERE = Query Results

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1

Post the rest of the quey SQL.
--
KARL DEWEY
Build a little - Test a little


"auujxa2 via AccessMonster.com" wrote:

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1

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

KARL DEWEY wrote:
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

--
Laser

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1

auujxa2 via AccessMonster.com wrote:

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 #]

--
Marsh
MVP [MS Access]

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.

Marshall Barton wrote:
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 #]


--
Laser

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1

auujxa2 via AccessMonster.com wrote:

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.

Marshall Barton wrote:
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 #]

--
Marsh
MVP [MS Access]

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

auujxa2 via AccessMonster.com wrote:
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