SQL subqueries

Posted on 1998-04-03
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.

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 50 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

679 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