[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