Complex query problem

[John]

Hi

I have a form with a number of multi-select lists and drop downs that user
can select to define a search criteria. The result then needs to be shown in
another list on the same form. Normally I would use a number of query
working in series (i.e. one query is source to the other and so on) with
initial queries using more exact criteria that works out fast and reduces
the number of records that following less exact queries need to search
through (speed is an issue).

The problem is that some query stages may not be required if user has not
selected anything from relevant dropdown/list. In this case how can I
achieve the flexibility of skipping certain query stages when needed? Should
I do everything in code to achieve the flexibility? In which case how can
one query be used as a source to another?

Thanks

Regards

 

[Ron Weiner]

Here is a VERY simple example of a subquery embedded in a Sql Statement


SELECT HighRank.id, HighRank.Name, HighRank.Score
FROM (SELECT id, Name, Score FROM tblRank WHERE tblRank.Score >= 98) AS
HighRank
WHERE HighRank.Score=100

Obviously this could have been written as a simple query without using a
Select statement as the source of the main query, but this should give you
the general Idea of how to use one query as the source of another in a
single sql statement.