Go Premium for a chance to win a PS4. Enter to Win


SQL subqueries

Posted on 1998-04-03
Medium Priority
Last Modified: 2011-10-03
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?
Question by:liljegren
  • 3
  • 2
  • 2
  • +2

Expert Comment

ID: 1460293
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


Author Comment

ID: 1460294
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.


Expert Comment

ID: 1460295

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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Expert Comment

ID: 1460296

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!

Expert Comment

ID: 1460297
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.

Author Comment

ID: 1460298
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.


Expert Comment

ID: 1460299
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

Accepted Solution

Dalin earned 150 total points
ID: 1460300
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.



Expert Comment

ID: 1460301
Sorry if I gave incorrect info.  I will research better before posting next time.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question