totn Access

MS Access 2003: Create a query that returns a new table based on 3 nested subqueries

This MSAccess tutorial explains how to create a query that returns a new table based on 3 nested subqueries in Access 2003 (with screenshots and step-by-step instructions).

Question: I have an Access 2003/XP/2000/97 database with a base table called tblApplicant whose primary key is AppId.

There are also 3 additional tables all connected using the AppId field. Each of these additional tables has a PermitID field and an Active (yes/no) field.

How do I create a query that returns a new table with the Applicant (AppId) and each of the latest PermitID's from the 3 additional tables? The latest PermitID is determined by finding the largest PermitID for the Applicant where the Active field is set to Yes.

Answer: The following SQL will work:

SELECT tblApplicant.AppId,
  (select max(PermitID) from tblPermitType1 where Active = Yes
   and tblApplicant.AppID = tblPermitType1.AppID) AS Permit1,
  (select max(PermitID) from tblPermitType2 where Active = Yes
   and tblApplicant.AppID = tblPermitType2.AppID) AS Permit2,
  (select max(PermitID) from tblPermitType3 where Active = Yes
   and tblApplicant.AppID = tblPermitType3.AppID) AS Permit3
INTO [Latest Permits]
FROM tblApplicant;

The query would look as follows:

Microsoft Access

Share on: