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:

搜尋此網誌

:. My Diary | My Blog .:

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

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

留言

發佈留言

此網誌的熱門文章

Sales術語

英國土地查冊處 - HM Land Registry

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;