dynamic querydef

Hi. I'm creating a database which will store data for a number of areas. I've created a table called dataset which will describe each dataset and allocate it an ID; a table called geography which will describe each area and allocate it an ID; then a third table which will hold the value for each dataset at each geographical level (DataID, GeographyID, DatasetID, value).

I need to enable users to select any number of datasets and display them for any number of geographies. I thought the best way to do this is to guide them with a couple of forms which will display the available datasets, allowing them to select which ones they want to display, then a form displaying the available geographies and they can select the ones they want to display. I'll then use the selected records to run a query and produce a report.

My problem is how do I allow them to select multiple records from each form and then transfer this info into a query. If it was just one record to select I'd use record selectors and pass the ID over to the query but don't know how to approach this for multiple selections from two tables.

Mile-O-Phile suggested I use the In() Keyword in a dynamic query (i.e.
WHERE MyField In(thisvalue, thatvalue, othervalue, etc)) but I cant find any guidance on this in Help files or on here anywhere.

Put a listbox on your form and fill it with the appropriate recorset
Set it's MultiSelect property to Simple.

This example should demonstrate:

What Does This Line of Code Mean?

Thank you.

Can you explain this line to me: strSQL = Left(strSQL, Len(strSQL) - 1) & ") "

I've had to adjust it to a crosstab query which it doesn't seem to like much:

Else
strSQL = "TRANSFORM Sum(tblData.Value) AS SumOfValue "
strSQL = strSQL & "SELECT tblDataset.Title "
strSQL = strSQL & "FROM tblDataset INNER JOIN (tblGeography INNER JOIN tblData ON tblGeography.GeographyID = tblData.GeographyID) ON tblDataset.DatasetID = tblData.DatasetID "
strSQL = strSQL & "WHERE (((tblDataset.DatasetID) IN ("
For Each var In Me.lstDatasets.ItemsSelected
strSQL = strSQL & Me.lstDatasets.Column(0, var) & ","
Next
strSQL = Left(strSQL, Len(strSQL) - 1) & ") "
strSQL = strSQL & "GROUP BY tblDataset.Title "
strSQL = strSQL & "PIVOT tblGeography.Name;"
End If

It just removes the last comma as In(1, 2, 3,) would cause an error.

It Worked!!!

Thanks very much! That was SO helpful... now I just need to learn how to create a form that outputs the results of the crosstab query (can't predefine the fields on the form so assume I'll have to do this dynamically???)

Thanks again!