Fwd: Access: Create a query that returns a new table based on 3 nested subqueries in Access 2003/XP/2000/97

http://www.techonthenet.com/access/queries/multiple_subqueries.php

Access: Create a query that returns a new table based on 3 nested subqueries in Access 2003/XP/2000/97


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:


留言

此網誌的熱門文章

Sales術語

曹仁超 - 投資者日記 (04/01/2007) - 08係炒家年