Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using Sub-Query's

Posted on 2002-06-24
5
Medium Priority
?
312 Views
Last Modified: 2010-05-02
I have query that used a sub query written in access...

ive extracted the sql code of the sub query and made an ADO recordset...

I could use a temporary table, but this isnt the most efficient way. How do i use this recordset in the main query?

Thanks in advance
0
Comment
Question by:djdidge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 7104107
You should look at the SHAPE syntax for a hierarchical way to access data. Have a look here for a starter: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefmsdatashape.asp
0
 
LVL 38

Accepted Solution

by:
PaulHews earned 260 total points
ID: 7104990
You can rewrite your access query (including sub-query) in SQL.  That is the most efficient approach.  Then make your recordset from that instead.

For example if I have a query: qryUno
Select * From Table;

And a query that uses that query: qryDos
Select * from qryUno Where qryUno.ID = 4;

This is a pretty trivial example, but it will demonstrate what I mean.  The full query re-written in SQL is:

Select * Form Table Where ID in (Select * From Table Where ID = 4)

There may be other ways to rewrite such a trivial example.

If you post the details of the queries we may be able to help further.
Here is the documentation regarding SQL subqueries:
-------------------
A subquery is a SELECT statement nested inside a SELECT, SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside another subquery.

Syntax

You can use three forms of syntax to create a subquery:

comparison [ANY | ALL | SOME] (sqlstatement)
expression [NOT] IN (sqlstatement)
[NOT] EXISTS (sqlstatement)

A subquery has these parts:

Part     Description
comparison     An expression and a comparison operator that compares the expression with the results of the subquery.
expression     An expression for which the result set of the subquery is searched.
sqlstatement     A SELECT statement, following the same format and rules as any other SELECT statement. It must be enclosed in parentheses.
Remarks

You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. In a subquery, you use a SELECT statement to provide a set of one or more specific values to evaluate in the WHERE or HAVING clause expression.
Use the ANY or SOME predicate, which are synonymous, to retrieve records in the main query that satisfy the comparison with any records retrieved in the subquery. The following example returns all products whose unit price is greater than that of any product sold at a discount of 25 percent or more:

SELECT * FROM Products
WHERE UnitPrice > ANY
(SELECT UnitPrice FROM OrderDetails
WHERE Discount >= .25);

Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. If you changed ANY to ALL in the previous example, the query would return only those products whose unit price is greater than that of all products sold at a discount of 25 percent or more. This is much more restrictive.

Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. The following example returns all products with a discount of 25 percent or more:

SELECT * FROM Products
WHERE ProductID IN
(SELECT ProductID FROM OrderDetails
WHERE Discount >= .25);

Conversely, you can use NOT IN to retrieve only those records in the main query for which no record in the subquery contains an equal value.

Use the EXISTS predicate (with the optional NOT reserved word) in true/false comparisons to determine whether the subquery returns any records.

You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees having the same job title. The Employees table is given the alias "T1":

SELECT LastName,
FirstName, Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;

In the preceding example, the AS reserved word is optional.

Some subqueries are allowed in crosstab queries — specifically, as predicates (those in the WHERE clause). Subqueries as output (those in the SELECT list) are not allowed in crosstab queries.

0
 
LVL 2

Author Comment

by:djdidge
ID: 7129319
hi...ive just got back from holiday so im taking a look now ;o)
0
 
LVL 2

Author Comment

by:djdidge
ID: 7129395
The original ACCESS QUERY that uses a sub query
"Students live with no future bookings"


SELECT STUDENTS.S_REF, STUDENTS.S_SURNAME
FROM STUDENTS LEFT JOIN [students with future bookings] ON STUDENTS.S_REF = [students with future bookings].S_REF
WHERE ((([students with future bookings].S_REF) Is Null))
GROUP BY STUDENTS.S_REF, STUDENTS.S_SURNAME;


"Students with future bookings"

SELECT STUDENTS.S_REF
FROM (BOOKINGS INNER JOIN SESSIONS ON BOOKINGS.B_SESS_ID = SESSIONS.SESS_ID) INNER JOIN STUDENTS ON BOOKINGS.B_S_REF = STUDENTS.S_REF
WHERE (((SESSIONS.SESS_DATE)>Now()))
GROUP BY STUDENTS.S_REF, STUDENTS.S_STATUS
HAVING (((STUDENTS.S_STATUS)="L"));


The query "Students with future bookings" is used not only in the where but also in the left join. Im not sure how to tackle it.
0
 
LVL 2

Expert Comment

by:i_jester69
ID: 7786268
Just replace the name of the query

SELECT * FROM Table1 LEFT JOIN Query1
ON Yadda yadda
WHERE Query1.Field = 'FF'

With this

SELECT * FROM Table1 LEFT JOIN
(
SQL FOR Query1
) AS Query1
ON Yadda Yadd
WHERE Query1.Field = 'FF'
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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.
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…
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

670 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