SQL subqueries

Now, I want to learn how to write a SQL subquery. In a standard module I have lots of functions to return SQL strings to make recordsets under different circumstances. Sometimes I want to delete some of the records after calling one of the functions. First I make a recordset wich is always correct:

Set RS_Search = DB_Search.OpenRecordset(SQL)

Here's the SQL string:

"SELECT * FROM " & Table & " ORDER BY [Year], [Num];"

And now, if AUTHORITY is 1 I want to extract the records that doesn't have Status = F. Something like this, I thought:

NewSQL = "SELECT * FROM " & Table & " WHERE [Status] <> 'F' IN (" & SQL & ");"
Set RS_Search = DB_Search.OpenRecordset(NewSQL)

Unfortunately it doesn't work. How can I make it work without having to write the second query in all the functions?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The right sentence I Think is:

dim sSQL as string
dim NewSQL as string
NewSQL = "SELECT * FROM " & Table & " WHERE [Status] <> 'F' IN (" & sSQL & ");"
set RS_Search = DB_Search.Openrecordset(NewSQL)

In any sub-query you must select only the field that you do the WHERE.

I hope this help you

liljegrenAuthor Commented:
Sorry, but this is not really what I asked for. I want to make a new query with the result of an existing query. The module with all the collected SQL strings may not be rewritten. I need a way to use the results of a query (i.e. a recordset) in another query(to filtrate the recordset once again under special circumstances). I thought subqueries would do this.


I think I understand what you are try to do.  However, I am not sure you can query a recordset with SQL.

I suggest you put your first query results into a temp. table, and then you query the temp table, When you finished, delete the temp table.

If You think this can work for you, and you want me to show you the code, let me know. Meanwhile, If I see anything easier that suit you, I will get back to you.

Good luck and best wishes in your project.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!


I think the first problem is that you are trying to use the wrong keyword.  The IN keyword specifies a table that is external to the JET database.

Another part of the problem is that the example that you give is ill-suited for a subquery.  Specifically,your query can be written as such:

SELECT * FROM Table WHERE Status <> 'F' ORDER BY Year, Num

However, since you want to learn about subqueries, allow me to offer up this scenario:

Let's say that you have a table of products named Catalog wherein you have the name, product code, and cost of each product.  If you wanted to produce a list of products (name & code) whose cost exceeded the average cost of all of the products, you would write it as such:

SELECT Name, ProductCode FROM Catalog WHERE Cost > (SELECT AVG(Cost) FROM Catalog)

A subquery can only return a single value and should be used in a comparison clause such as WHERE or HAVING.

This would allow you achieve your goal of combining the two queries into a single operation.  

Hope this helps!
I think liljegren wants to have a results of the first query, and then make additional queries (may not be one) based on some other conditions which may be related to the results of the first query. So it is not suited for the sub-query we are talking about, which excute the first query every time. When the database is small, that is OK, but if the database is huge, it is more desirable to put the first query results somewhere, and only go through the small set, especally if the 2nd query is repeated over and over again.
liljegrenAuthor Commented:
Thank you for this aspiring answer Zsi, but as Dalin said, this is not really what I want. I'm sorry that I used the title "SQL subqueries" for this question, it was misleading.

Thank you again Dalin. I tried your solution, but I think I do domething wrong. Here's my code:

' SQL is the pre-defined SQL string
Set RS_Search = DB_Search.OpenRecordset(SQL)

' Filter the recordset once again, to hide certain records
' from certain users.
    ' Make a copy of the RS_Search recordset
    Set RS_Temp = RS_Search.OpenRecordset()
    ' Filter the temporary recordset
    RS_Temp.Filter = "Status <> 'F'"
    ' Copy the records back to the original recordset
    Set RS_Search = RS_Temp.OpenRecordset()
    ' Close the temporary recordset
End If

I couldn't find any other way to do this, so I used the built in .Filter method in VB. But it doesn't do anything. The number of records is the same before and after the filtering.

I believe the filter method is not available to the recordset object.

I need some info, and then maybe I can help you.  First, do you need the data to be dynamic?(ie you can write back to it), or are you just trying to pull information for people.  If you are attempting to do this dynamically, then I am not sure how you could save time by excersizing this method over zsi's.  If you are specifying criteria in the original SQL statement, there are some things you could do to speed things up, but as the case stands, you are pulling in the entire database, and only ordering it(also time consuming), and then attempting to query.  Possibly you can create a tabledef if the info is from an outside source, but I don't think that will save any time.

If it is okay to do this statically, you may want to consider using code to create a table from the data you need to pull in.
(like DoCmd.RunSQL "SELECT * INTO [temptable] FROM " & Table & " ORDER BY [Year], [Num];" )  Base all your queries on this new table, and then delete the table(DoCmd.DeleteObject acTable, "temptable") when you are done.(This will save some time, but will use up hard drive space while it is running.)

I hope this is at least helpful.  I might be able to get you some better answers as the intent becomes more clear
The Filter property is certainly available to your DAO recordset.
You don't want to overwrite your first recodsetset if you intend to use it again, otherwise, it becomes equivalent to the subquery situation(when you need it again, you have to excute the first query for the entire database).

If you want to use the filter, Try the following:

1. add a general function:
Public Function FilterField(largeSet As Recordset, _
    FilField As String, strOperator As String, strFilter As String) As Recordset

    ' Set a filter on the specified Recordset object and then

' open a new Recordset object.
    largeSet.Filter = FilField & " " & strOperator & " '" & strFilter & "'"
    Set FilterField = largeSet.OpenRecordset

End Function

2. In your code you wanted to do the filter:
Set rstTemp =FilterField(rstSearch, "Status","<>", "F")
' Assume Status is a field name in your recordset.
' Check RecordCount before you do things with It;
      ' otherwise, error may result.
With rstTemp
      If .RecordCount <> 0 Then
             'Now do your stuff, such as getting number of Record
             ' close it when you don't need it
          End if
 End With

However, I personally preper using a temp table.
Your first query would be:

select * INTO [tempTable] FROM originalTable ...

Then you query the temptable:

If you have any questions, let me know.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sorry if I gave incorrect info.  I will research better before posting next time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.