how to create cascading query at run time

[dogdays]

I have a table tblFiscalGifts which contains an ID number, the amount of the gift and the source code for the gift. At run time a user may select n number of source codes from a listbox to select for a report. I would like to generate a series of queries or SELECT statements which would result in a record set of all ID numbers that gave to all the selected source codes e.g.
select the first code, then select the next code using the results of the previous select until the final set.

tia Jack

 

[boblarson]

I have a table tblFiscalGifts which contains an ID number, the amount of the gift and the source code for the gift. At run time a user may select n number of source codes from a listbox to select for a report. I would like to generate a series of queries or SELECT statements which would result in a record set of all ID numbers that gave to all the selected source codes e.g.
select the first code, then select the next code using the results of the previous select until the final set.

tia Jack

You don't need to create a bunch of queries like that. You just need to dynamically create the WHERE Clause for the report.

This is what you need:
http://www.baldyweb.com/multiselect.htm

 

[dogdays]

Bob: thanks for the response. I did look at that bit of code earlier and came to the conclusion that it would not work. The problem I am having is selecting the ID number of those entries that have ALL of the selected criteria present.
If I want to select source codes 1, 4 and 7 I think that I have to select the ID numbers source code 1, then from that group select those that also have source code 4, then from that group select those that also have source code 7. The final set would include all ID numbers that have all of the desired source codes.

Jack

 

[boblarson]

Bob: thanks for the response. I did look at that bit of code earlier and came to the conclusion that it would not work. The problem I am having is selecting the ID number of those entries that have ALL of the selected criteria present.
If I want to select source codes 1, 4 and 7 I think that I have to select the ID numbers source code 1, then from that group select those that also have source code 4, then from that group select those that also have source code 7. The final set would include all ID numbers that have all of the desired source codes.

Jack

How wrong you are. If you want all of the ID numbers which have those codes, you just have to have the right query along with the code I gave you. So, given that I cannot see your structure you will have to enlighten me on what your tables look like, what the data is in them and what it is you are trying to get as a final result (don't think about what you currently have thought about, just give what it should look like once you get it).

Or pop on over to Access World Forums (where I'm a moderator) and post the question along with your database (after you have used Compact and Repair and also zipped it into a .zip file). Then we can see what you have and where you should go (don't include real data, make it bogus data so that you don't post real stuff on the Internet).

 

[dogdays]

Bob: assume a table with 2 columns- ID and SourceCode, I wish to find the IDs that have given to source codes 1 AND 4 AND 7
[\code]
table

ID SourceCode
1 1
1 4
1 7
2 2
2 1
2 4
3 1
3 4
3 7
4 4
4 7
4 8
[/code]

The result should be ID 1 and ID 3.

Jack

 

[boblarson]

Bob: assume a table with 2 columns- ID and SourceCode, I wish to find the IDs that have given to source codes 1 AND 4 AND 7
[\code]
table

ID SourceCode
1 1
1 4
1 7
2 2
2 1
2 4
3 1
3 4
3 7
4 4
4 7
4 8
[/code]

The result should be ID 1 and ID 3.

Jack

Given your information, the code I pointed you to would work.

Your report should have a record source of

Select * From TableNameHere

And the code would build the IN part of the Where Clause so that when you opened it the where clause passed would be:

strWhere = "[SourceCode] In(1,3)"
 
DoCmd.OpenReport "ReportNameHere", acViewPreview, , strWhere

Simple and easy. So, implementing the code that Paul has would be like:

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.YourListBoxNameHere.ItemsSelected.Count = 0 Then
  MsgBox "Must select at least 1 employee"
  Exit Sub
End If

'add selected values to string
Set ctl = Me.YourListBoxNameHere
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"  
Next varItem 
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "ReportNameHere", acPreview, , "[SourceCode] IN(" & strWhere & ")"

 

[Kreszch68]

Bob, I don't think it's that simple.
What TS wants, is to return the ID values by selecting the Source code, and only the ID's that have all source codes (thus 1 and 4 and 7).
This requires a logaritm instead of SQL, SQL will only work for structured data. which this is not. Personally I would kill the developer who came up with this table structure .
But I'll take a shot at it just to see if it's possible.

 

[dogdays]

Bob:
If I implemented the code as you have put it the code would look like this:
[\code]
SELECT * FROM tblFiscalGifts WHERE SourceCode IN (1,4,7)
[/code]
The selected IDs would be 1,2,3,4. Each of the IDs has at least one of the desired sourcecodes, however only 2 (1,3) have ALL of the codes. The result I want is select 1 and 3.
I tried doing that by selecting using IN, then building an array of IDs then looping through the array to test each ID for all the codes. It worked but is very cumbersome and I believe slower and not pretty.

Jack

 

[Kreszch68]

This might be the solution.

Public Function SQL_GiftIDs(ByVal sInClause As String, _
                            ByVal iNumOfSourceCodes As Integer) As String
SQL_GiftIDs = "SELECT ID " _
            & "FROM tblFiscalGifts " _
            & "WHERE SourceCode In (" & sInClause & ") " _
            & "GROUP BY ID " _
            & "HAVING Count(ID) = " & iNumOfSourceCodes
End Function

You need to pass the sInclause and the number of selected items from the listbox. The SQL will return only the ID's having all the selected source codes. You can also use this statement as the in clause for the select statement to return from the tblFiscalGifts. Paste the next part in the query designer (in the SQL window of course) to get an idea how it will look like.

SELECT tblFiscalGifts.ID, tblFiscalGifts.SourceCode
FROM tblFiscalGifts
WHERE (((tblFiscalGifts.ID) In (SELECT tblFiscalGifts.ID
FROM tblFiscalGifts
WHERE (((tblFiscalGifts.SourceCode) In (1,4,7)))
GROUP BY tblFiscalGifts.ID
HAVING (((Count(tblFiscalGifts.ID))=3)))));

 

[Kreszch68]

Actually, you don't need to count the selected items
If you split the inclause, you can calculate it from there.

Public Function SQL_GiftIDs(ByVal sInclause As String) As String
Dim vCntItems As Variant
Dim iNumOfSourceCodes As Integer
vCntItems = Split(sInclause, ",", -1, vbTextCompare)
iNumOfSourceCodes = UBound(vCntItems) + 1
 
SQL_GiftIDs = "SELECT ID " _
            & "FROM tblFiscalGifts " _
            & "WHERE SourceCode In (" & sInclause & ") " _
            & "GROUP BY ID " _
            & "HAVING Count(ID) = " & iNumOfSourceCodes
End Function