Join to user-selected table

[ptaylorc]

Database structure: I have a large dataset, a query which filters those data records, and a form that displays the data (and also allows the user to adjust the query filter parameters).

I need to create an additional filter which simply selects a pre-determined subset of the records. I.e. a combo box where the user can select "Group A" or "Group B" or "All" etc.

The only way I can think of doing this is to create a separate table which lists each group (that's ok), then INNER JOIN the desired group table to the main dataset to filter those records only. But how do I let the user select which group list to join?

Is it possible to for a query to create a join based on a variable? E.g. MainTable INNER JOIN Iif(case A, "TableA", else case B, "TableB"...) etc.

Thanks!

 

[vbaInet]

What you're talking about can be accomplished using a listbox (not a combobox) for selecting mutiple values and the IN() operator. Please investigate those.

 

[pbaldy]

Don't think you can do that. Why not a query criteria on the combo?

 

[vbaInet]

Don't think you can do that. Why not a query criteria on the combo?

Right on pbaldy! I thought that the poster was talking about multiple criteria.

I guess the only way to achieve this is by building the SQL in code but you will have form binding issues, i.e. binding fields to controls. Can you elaborate why you need to select a particular table? It's sounding like a possible problem with your table structure.