We help IT Professionals succeed at work.

Access SQL - query on another sub query

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

In Access I understand how to do a query on another query but could someone please give me a simple example of how this would look in SQL
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
do you mean a subquery? This would be an example of that:

say you have a database containing the details of staff at a business, and each department of staff have their own table. In one query you want to find out the average age of staff in the HR department and you want to see which members of staff in the IT department are older than this average. We can use a subquery to find this information:

select name, age, averageAge
from IT_DEPT
where age > 
    (select avg(age) as averageAge
    from HR_DEPT)

as you can see the subquery finds the average age of employees in the HR department, while the first query finds the IT employees whose age is higher than that found in the subquery. I have shown that you can include this average age in the results as well be including it in the query.
Commented:
Yo have two options:

1) Create the first query then run a second query on it:

Say you create a query call Q1

    SELECT TableA.ColumnA, TableB.ColumnB, TableC.ColumnC,TableD.ColumnD
    FROM      ((TableA
    INNER JOIN  TableB ON TableA.AID = TableB.AID)
    INNER JOIN  TableC ON TableB.BID = TableC.BID)
    INNER JOIN  TableD ON TableC.BID = TableD.BID

Then in the second query use

Select ColumnB,ColumnC
FROM Q1  INNER JOIN  TableE ON Q1.ColumnA= TableE.EID
Where ColumnD=X


2) Alternatively you can nest the whole lot in a singe sql which would look like this:

Select ColumnB,ColumnC

From

(
SELECT TableA.ColumnA, TableB.ColumnB, TableC.ColumnC,TableD.ColumnD
    FROM      ((TableA
    INNER JOIN  TableB ON TableA.AID = TableB.AID)
    INNER JOIN  TableC ON TableB.BID = TableC.BID)
    INNER JOIN  TableD ON TableC.BID = TableD.BID
) As Q1

INNER JOIN  TableE ON Q1.ColumnA= TableE.EID
Where ColumnD=X



Commented:
good Practical examples

EXAMPLE 1

A sales rep. wants to hound customers who have not placed any orders in the last 90 days:

SELECT Customers.ID, Customers.Company
FROM Customers
WHERE NOT EXISTS
   (SELECT Orders.OrderID                        
   FROM Orders                                    
   WHERE Orders.CustomerID = Customers.CustomerID
     AND Orders.OrderDate > Date() - 90)


Periodically, they read the meter at your house, and send a bill for the number of units used since the previous reading. The previous reading is a different record in the same table. How can they query that?

EXAMPLE 2

SELECT MeterReading.ID,
MeterReading.ReadDate,
MeterReading.MeterValue,
   (SELECT TOP 1 Dupe.MeterValue                
   FROM MeterReading AS Dupe                    
   WHERE Dupe.AddressID = MeterReading.AddressID
     AND Dupe.ReadDate < MeterReading.ReadDate  
   ORDER BY Dupe.ReadDate DESC, Dupe.ID)          AS PriorValue
FROM MeterReading;


Example 3

You want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query:



SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
   (SELECT TOP 3 OrderID                            
   FROM Orders AS Dupe                              
   WHERE Dupe.CustomerID = Orders.CustomerID        
   ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

You want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query:


Example 4


A Totals query easily gives you a total for the current month, but to get a year-to-date total or a total from the same month last year means another calculation from the same table but for a different period. A subquery is ideal for this purpose.

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS MonthAmount,
   (SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD                                
   FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID = OD.OrderID  
   WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1)              
     AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]),                    
       Month([Orders].[OrderDate]) + 1, 1))                                       AS YTDAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);

Commented:
check the file
Access.pdf
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thank you all very much for the help