Combine Two Queries 1

[knappm]

I was wondering if it would be possible to combine the following two queries given the scenario. I have a value list listbox that I want populated with certain data. So, this is what I do.

I select all records with a given criteria. Then for each result, I do another query and based on the results of the second query, I either add or don't add the data to the listbox. I was using the .AddItem method of the listbox, but that's new to Access 2002, and we need it to work in 2000. I was hoping to get it all into one query so I could change the listbox to use a Table/Query source.

I think the best thing to do is just post the code up. So, here it is:

Private Sub cmdRefresh_Click()
    Dim Conn As ADODB.Connection
    Dim Rs1, Rs2 As ADODB.Recordset
    Dim strSQL1, strSQL2 As String
    Dim nIndex As Integer
    
    Dim strName, strDescription, strNumber As String
    Dim dDate As Date
    Dim nDuration As Integer
    
    Set Conn = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset
    
    strSQL1 = "SELECT Employee.Name, Employee.[No], Training.Description, Training.Duration, Max(Training.dDate) AS MaxOfdDate FROM Employee INNER JOIN Training ON Employee.[No]=Training.[Employee No] WHERE (((Training.Duration)>0) And ((Training.Required)=-1) And ((Training.Completed)=-1) And ((Employee.[No])=[Employee No])) GROUP BY Employee.Name, Employee.[No], Training.Description, Training.Duration"

    Rs1.Open strSQL1, Conn, adOpenStatic, adLockReadOnly
    
    While Not Rs1.EOF
        strName = Rs1.Fields(0)
        strNumber = Rs1.Fields(1)
        strDescription = Rs1.Fields(2)
        nDuration = Rs1.Fields(3)
        dDate = Rs1.Fields(4)
        If CheckTraining(strNumber, dDate, nDuration) = 0 Then
            strName = Replace(strName, ",", " ")
                
            lstReqTraining.AddItem (strName & ";" & strNumber & ";" & strDescription & ";" & dDate & ";" & nDuration)
        End If
        Rs1.MoveNext
    Wend
    
    Rs1.Close
    Set Rs1 = Nothing
    Set Conn = Nothing
End Sub

That's the first section. The second query is in the CheckTraining function, which I'll post now:

'Returned value will be 0 or 1
Private Function CheckTraining(ByVal nEmployeeNo As String, ByVal dDate As Date, ByVal nDuration As Integer)
    Dim dOldDate, dNewDate As Date
    
    dOldDate = dDate
    dNewDate = DateAdd("m", nDuration, dOldDate)

    Dim Conn As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim strSQL As String
    Dim nCount As Integer
    
    Set Conn = CurrentProject.Connection
    Set Rs = New ADODB.Recordset
    
    strSQL = "SELECT T.ID FROM Employee E, Training T "
    strSQL = strSQL & "WHERE ((T.Duration>0) AND (T.Required=-1) AND (T.Completed=0) AND (E.No=T.[Employee No]) AND (E.No="
    strSQL = strSQL & nEmployeeNo
    strSQL = strSQL & ") AND (T.dDateRequired=#"
    strSQL = strSQL & dNewDate
    strSQL = strSQL & "#))"
    
    Rs.Open strSQL, Conn, adOpenStatic, adLockReadOnly
    nCount = Rs.RecordCount
    Rs.Close
                
    Set Rs = Nothing
    Set Conn = Nothing

    CheckTraining = nCount
End Function

I'm not even sure if you're able to tell anything or help from the given info, but any help would be appreciated GREATLY. I've been mulling over this for a while now. If you need any other background info or anything, please let me know. Thanks much!

 

[JerryDennison]

Maybe I'm missing something here, but wouldn't it be easier to simply set the listbox's Rowsource property to either a query based on the recordsets you need or the SQL itself, based on linked tables?

 

[knappm]

That's what I would like to do. Just simplify it all down to one query that I can set as the source of the listbox. But thus far, I have not been able to construct that query. Maybe it's simple, and I'm just missing it. I'm not an SQL expert by any means. I can't figure out how to select a record (A) based on whether a another record (B) exists with all the same data except a date that's a given amount ahead of the date in A. Does that make sense? That's what the code above is doing. It's pulling values from a record (A), incrementing the date based on a the value in the duration field and then querying the database again to see if a record (B) exists with the new date. If B does exist, then A is not printed. If B does not exist, then A is printed.

Does that make sense? How do I make the query I'm looking for? Please help.

 

[JerryDennison]

Create a query based on table A. Add a calculated field to get the forward looking date:

FutureDate: DateAdd("d", 5, [DateFieldName])

Close and save this query. Create another query based on table B and table A. Link the two tables using all the fields you want to insure are the same, including the forward looking date to the date field you want to match in table B. Double click each of the join lines and change them to include all records from table A and only those matching from table B. Now, create unbound fields on the QBE grid for each field you wish to display. Use the following syntax for each to get what you need:

FieldName: IIF(IsNull(ControlFieldFromTableB), TableAFieldName, TableBFieldName)

 

[knappm]

Thanks for the reply. After a bit of monkeying around, I was able to display the results I'm looking for based on your idea. Thank you.

However, there's still a little problem. I only want to display the value from table A or nothing at all. That is, if the matching record is not in table B, then display the record from table A. Otherwise, show nothing. I'm using this:
FieldName: IIF(IsNull(ControlFieldFromTableB), TableAFieldName)
But that gives me blank lines for every record that is in both table A and table B. I can't seem to find a way to not return those lines. Any ideas?

Thanks again.

 

[knappm]

Haha.. oops. I just figured it out. Thanks again for the help! Greatly appreciated.

 

[knappm]

In case someone's curious, I did manage to incorporate all of this into one query using the EXISTS predicate. The query is as follows:

SELECT Employee.Name, Employee.[No], touter.Description, Max(touter.dDate) AS MaxOfdDate, touter.Duration
FROM Training AS touter INNER JOIN Employee ON touter.[Employee No] = Employee.[No]
WHERE (((touter.Duration)>0) AND ((touter.Completed)=-1) AND ((touter.Required)=-1) AND ((Exists (SELECT ID 
FROM Training tinner 
WHERE tinner.dDateRequired=DateAdd(&quot;m&quot;,touter.Duration,touter.dDate) AND tinner.Duration>0 AND tinner.Completed=0 AND tinner.Required=-1 AND touter.Description=tinner.Description AND touter.[Employee No]=tinner.[Employee No] AND touter.ID<>tinner.ID))=False))
GROUP BY Employee.Name, Employee.[No], touter.Description, touter.Duration;

This query is slower, however, than the method suggested above. Does anyone have any recommendations on optimizing this query? Thanks.

 

[rajeessh]

Hope in the same query u can do by creating two alaises for
Training

SELECT Employee.Name, Employee.[No], touter.Description, Max(touter.dDate) AS MaxOfdDate, touter.Duration
FROM Training AS touter INNER JOIN Employee ON touter.[Employee No] = Employee.[No]
INNER JOIN Training tinner on
touter.Description=tinner.Description AND touter.[Employee No]=tinner.[Employee No]
WHERE (((touter.Duration)>0) AND ((touter.Completed)=-1) AND ((touter.Required)=-1) AND
tinner.dDateRequired=DateAdd(&quot;m&quot;,touter.Duration,touter.dDate) AND tinner.Duration>0 AND tinner.Completed=0 AND tinner.Required=-1 AND touter.ID<>tinner.ID))=False)
GROUP BY Employee.Name, Employee.[No], touter.Description, touter.Duration;


May have some bracketing errors.
Pl take a look and get back if i am wrong