Need help with the SELECT query from hell !!!

[JonnyCab]

Hi all,

I've only ever had to use basic queries so might need some help from the query GURU's.

Senario: VB6 (ADO)

I have a table with the following fields
ContractID
EmployeeID
Date
Component#
Amount


1st
I need to SELECT all records from the table where the first character of the 'ContractID' = "2" or "M" and the 'Component#' is >50.

2nd
Then I need to check the 'EmployeeID' and 'Date' from each of the records in the query result recordset to see if there is a record in the original table that has the same 'EmployeeID' and 'Date', but has a Component# <50.

3rd
Any matching records that have a Component# <50 need to be deleted.

Lost yet?....I am!. I think I will need several Queries but I'm not sure how to feed the result of one query into another. Come on GURU's .... show me the money!

[JonnyCab]

OK..I am attempting to do this problem as it has been a whole 5 minutes and I have had no replys.

I've started with this code

Dim strSQL As String
Dim rstTemp As ADODB.Recordset

strSQL = " INSERT INTO tblTest (Contract, EntryDate, Employee, WComponent)" _
& "SELECT Contract, EntryDate, Employee, WComponent" _
& " FROM tblTemp" _
& " WHERE Mid(tblTemp.Contract,1,1) = '" & "2" & "'"
' Identifies which ADO connection is being used for SQL.
Set rstTemp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)


...but am getting the error "Operation is not aload when the object is closed"....what object is this refering to?
All the tables refered to are in the database connected to using Connection1.

Anybody help me here?

[JonnyCab]

I think I've got it going ok....used the following code.

Dim strSQL As String
Dim rstTemp As ADODB.Recordset
Dim rstTemp2 As ADODB.Recordset

strSQL = "SELECT Contract, EntryDate, Employee, WComponent" _
& " FROM tblTemp" _
& " WHERE (Mid(tblTemp.Contract,1,1) = '2' OR Mid(tblTemp.Contract,1,1) = 'M')" _
& " AND tblTemp!WComponent > 50"

Set rstTemp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)

Do While Not rstTemp.EOF
strSQL = "DELETE * FROM tblTemp" _
& " WHERE tblTemp.Employee = '" & rstTemp!Employee & "'" _
& " AND tblTemp.EntryDate = '" & rstTemp!EntryDate & "'" _
& " AND tblTemp.WComponent < 50"
Set rstTemp2 = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)
rstTemp.MoveNext
Loop

rstTemp.Close
Set rstTemp = Nothing


If anyone has a better way of doing this let me know.

Cheers