selecting main record from multiple subtables (1 Viewer)

[David R]

So I got my form working for data entry, it only required 7 tables. One main table (Resources), three lookup tables for the categories (Council District, Patrol Division, Program Focus), and three two-field tables to combine them many-to-many.

My problem now comes in trying to query them for useful data.
Proposition: A user should be able to come along and use three listboxes to specify what Council Districts, what Patrol Divisions, and what Program Focus they want to see. When run the query should bring up just those Resources that match the specified criteria.

I can use simple queries on the join tables to get all records that match any one of these three categories. How do I get SQL to tell me the record numbers for the main table that match something in all three queries? Some sort of DISTINCTROW query?

 

[Jack Cowley]

David -

I think this article is what you are looking for....hopefully!
http://support.microsoft.com/support/kb/articles/Q210/2/42.ASP?LN=EN-US&SD=gn&FR=0&qry=QBF&rnk=2&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

Jack

 

[David R]

I think that's sort of along the lines of what I want, but I'm not sure how to write it in VBA.

See, the subqueries will be written dynamically by stepping through the listboxes (as we did in http://www.access-programmers.co.uk/ubb/Forum3/HTML/003428.html). However this time each listbox refers to a different table, and then I have to take the results of those three queries together to get the results:

SELECT Query1.ResourceID
FROM (Query1 INNER JOIN Query2 ON Query1.ResourceID = Query2.ResourceID) 
INNER JOIN Query3 ON Query2.ResourceID = Query3.ResourceID;

And I'm concerned about running over the limit for hand-written SQL strings, because I've never done this before. Access doesn't do 'nested' Select statements does it?

If I can write the queries and save them then it's trivial to combine them to see the intersection. However I need to be able to do so dynamically for categories/list boxes of 5, 6, and 16 values respectively. Is there a way, albeit inefficient, to store these dynamic SQL statements and then run the above to filter them down to unique records?

[This message has been edited by David R (edited 04-19-2002).]

 

[Jack Cowley]

The code I suggested saves the query it creates. So if you need to combine the queries you can do that. Hope I did not miss your point...

 

[David R]

Thanks Jack, sorry about not reading closer.
I've got that feet-not-touching-the-ocean-floor feeling so I'm not tracking as well as usual. I gave it a try and it works well.

My only 'criticism' (and it is of MS, not you!) is that the example uses DAO which isn't the default for Acc2k, even though the article is for that version. Is there a way to convert this so it works without having to make sure Tools>References is OK?

Thanks,
David R


[This message has been edited by David R (edited 04-19-2002).]

 

[David R]

Here's the code I used, if anyone's curious:

Private Sub buttonFindResources_Click()
'Borrowed from [url="http://support.microsoft.com/default.aspx?scid=kb;EN-US;q210242"]http://support.microsoft.com/default.aspx?scid=kb;EN-US;q210242[/url] 


DoCmd.Hourglass True


Dim db As DAO.Database
Dim QD As QueryDef
Dim strWhere As String
Dim varItem As Variant


Set db = CurrentDb()


' Delete the existing dynamic query; trap the error if the query does
' not exist.


On Error Resume Next
db.querydefs.Delete ("Query1")
On Error Resume Next
db.querydefs.Delete ("Query2")
On Error Resume Next
db.querydefs.Delete ("Query3")
On Error GoTo 0


    strWhere = "("
    If Me.listDistricts.ItemsSelected.Count > 0 Then
        For Each varItem In Me.listDistricts.ItemsSelected
            strWhere = strWhere & "[DistrictID] = " & Me.listDistricts.Column(0, varItem) & " Or "
        Next varItem


        strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "
        

        strWhere = "SELECT ResourceID, DistrictID FROM tableResourceDistricts " _
        & "WHERE " & strWhere & ")"
    Else
        strWhere = "SELECT ResourceID, DistrictID FROM tableResourceDistricts;"
    End If
    Set QD = db.CreateQueryDef("Query1", strWhere)

    
    strWhere = "("
    If Me.listDivisions.ItemsSelected.Count > 0 Then
        For Each varItem In Me.listDivisions.ItemsSelected
            strWhere = strWhere & "[DivisionID] = " & Me.listDivisions.Column(0, varItem) & " Or "
        Next varItem


        strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "
        

        strWhere = "SELECT ResourceID, DivisionID FROM tableResourceDivisions " _
        & "WHERE " & strWhere & ")"
    Else
        strWhere = "SELECT ResourceID, DivisionID FROM tableResourceDivisions;"
    End If
    Set QD = db.CreateQueryDef("Query2", strWhere)


    strWhere = "("
    If Me.listPrograms.ItemsSelected.Count > 0 Then
        For Each varItem In Me.listPrograms.ItemsSelected
            strWhere = strWhere & "[ProgramID] = " & Me.listPrograms.Column(0, varItem) & " Or "
        Next varItem


        strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "
        

        strWhere = "SELECT ResourceID, ProgramID FROM tableResourcePrograms " _
        & "WHERE " & strWhere & ")"
    Else
        strWhere = "SELECT ResourceID, ProgramID FROM tableResourcePrograms;"
    End If
    Set QD = db.CreateQueryDef("Query3", strWhere)


    Me.RecordSource = "SearchQuery"
    DoCmd.Hourglass False


End Sub

SearchQuery simply finds the records in common between the three, and opens all fields to be the RecordSource for the form.

[This message has been edited by David R (edited 04-24-2002).]

 

[Jack Cowley]

MS does NOT make it east to convert your code to ADO. I have done some conversions but I do not know what the ADO equivalent of QueryDef or QueryDefs is. Of course MS had thousands of bits of code like that in the Article and they haven't gotten around to changing it as it is easier to do the DAO thingo and let you do the same.

 

[David R]

I am Jack's complete lack of surprise.

Thanks, Jack.