[AccessD] Nested query

Many thanks John!

I'll give that a go.

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