[AccessD] Nested query

Hi John!

I could not get your suggestion to work in A97, but in A2003 (with a minor
change) it works as good as gold.

What I ended up with is:

<SQL>
SELECT tblClubs.fldIDClub, tblClubs.fldClubCode, tblClubs.fldClubShort
FROM (SELECT Max(tblClubs1.fldIDClub) AS [Highest ID] 

	FROM tblClubs tblClubs1

     GROUP BY tblClubs1.fldClubCode) AS LatestClubRecs LEFT JOIN tblClubs ON
LatestClubRecs.[Highest ID] = tblClubs.fldIDClub;
</sql>

Notice that I aliased "tblClubs" as "tblClubs1" in the second "FROM".
Without this Access complained about the same table being referred to twice.

Thanks again for your help John!

Best regards
Chris F

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of DJK(John) 
> Robinson
> Sent: Monday, February 20, 2006 1:19 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Nested query
> 
> 
> Hi Chris
> 
> A rare occasion that I'm up to date with AccessD reading - 
> displacement activity for evil tax calculations!
> 
> Anyway, how about:
> 
> SELECT tblClubs.fldIDClubs, tblClubs.fldClubCode, tblClubs.fldClubName 
> FROM (SELECT Max(tblClubs.fldIDClubs) AS [Highest ID] FROM tblClubs 
> GROUP BY tblClubs.fldClubCode) AS LatestClubRecs LEFT JOIN tblClubs ON 
> LatestClubRecs.[Highest ID] = tblClubs.fldIDClubs;
> 
> HTH
> John
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Foote, 
> Chris
> Sent: 20 February 2006 12:45
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Nested query
> 
> 
> Good day!
> 
> I'm having problems "nesting" a query within another query.
> 
> My (simplified) scenario is as follows:
> 
> I have a table (tblClubs) that has three fields: fldIDClubs, 
> fldClubCode, and fldClubName.
> 
> fldIDClubs is an AutoNumbered field, the other two are text.
> 
> In my table I currently have the following four records:
> 
> | fldIDClubs | fldClubCode | fldClubName |
> |------------|-------------|-------------|
> | 1          | WAL         | Walton      |
> | 2          | WAL         | Still Walton|
> | 3          | WEY         | Weybridge   |
> | 4          | OXU         | Oxford Uni. |
> 
> 
> What I want to do, is to return the records with the highest IDClub 
> number per ClubCode ie:
> 
> | fldIDClubs | fldClubCode | fldClubName |
> |------------|-------------|-------------|
> | 2          | WAL         | Still Walton|
> | 3          | WEY         | Weybridge   |
> | 4          | OXU         | Oxford Uni. |
> 
> I can do this in two steps, by creating a query that selects MAX 
> IDClubs and GROUP BYs ClubCode (SQL below)
> 
> <SQL>
> SELECT Max(tblClubs.fldIDClubs) AS [Highest ID] FROM tblClubs GROUP BY 
> tblClubs.fldClubCode; </SQL>
> 
> And then using that query in another query with a LEFT JOIN on the 
> original table (SQL below)
> 
> <SQL>
> SELECT tblClubs.fldIDClubs, tblClubs.fldClubCode, tblClubs.fldClubName 
> FROM qryLatestClubRecs LEFT JOIN tblClubs ON 
> qryLatestClubRecs.[Highest ID] = tblClubs.fldIDClubs; </SQL>
> 
> But, I need to do this all in one query. I'm using Access as a tool to 
> develop SQL to be used with mySQL in a PHP web page.
> 
> Any suggestions gratefully received.
> 
> TIA!
> 
> Chris Foote
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com