[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