Solved

SQL subqueries

Posted on 1998-04-03
9
473 Views
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?
0
Comment
Question by:liljegren
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 3

Expert Comment

by:guillems
Comment Utility
The right sentence I Think is:

dim sSQL as string
dim NewSQL as string
sSQL = "SELECT STATUS FROM " & Table
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

0
 

Author Comment

by:liljegren
Comment Utility
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.

0
 
LVL 9

Expert Comment

by:Dalin
Comment Utility
liljegren,

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.
Dalin

0
 
LVL 4

Expert Comment

by:zsi
Comment Utility

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!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Expert Comment

by:Dalin
Comment Utility
zsi,
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.
Regards
Dalin
0
 

Author Comment

by:liljegren
Comment Utility
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.
If AUTHORITY = 1 Then
    ' 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
    RS_Temp.Close
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.

0
 
LVL 2

Expert Comment

by:FalconMaster
Comment Utility
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
0
 
LVL 9

Accepted Solution

by:
Dalin earned 50 total points
Comment Utility
liljegren,
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
             .MoveLast
             ' close it when you don't need it
           .Close
          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:
SELECT * FROM TempTable WHERE STATUS = 'F'

If you have any questions, let me know.
Regards
dalin

      

0
 
LVL 2

Expert Comment

by:FalconMaster
Comment Utility
Sorry if I gave incorrect info.  I will research better before posting next time.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now