[AccessD] Nested query

Cheers Mark!

Your version works fine in A97. MySQL 4.0.23a-Max still not happy, but I can
work on it.

Regards
Chris F



> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark A Matte
> Sent: Monday, February 20, 2006 9:42 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Nested query
> 
> 
> Chris,
> 
> You mentioned A97...just tested this...works fine...hope it helps.
> 
> Thanks,
> 
> Mark A. Matte
> 
> **********
> SELECT tblClubs.fldIDClubs, tblClubs.fldClubCode, tblClubs.fldClubName
> FROM tblClubs
> WHERE (((tblClubs.fldIDClubs) In (SELECT Max(tblClubs.fldIDClubs) AS 
> MaxOffldIDClubs FROM tblClubs GROUP BY tblClubs.fldClubCode;)));
> **********
> 
> >From: "Chris Foote (Spike)" <spikee at oatlandspark.org.uk>
> >Reply-To: Access Developers discussion and problem 
> >solving<accessd at databaseadvisors.com>
> >To: "'Access Developers discussion and problem 
> >solving'"<accessd at databaseadvisors.com>
> >Subject: Re: [AccessD] Nested query
> >Date: Mon, 20 Feb 2006 20:52:21 -0000
> >
> >There's something not quite right about it as well John.
> >
> >The A2003 query grid keeps replacing the round brackets 
> surrounding the
> >sub-query with square brackets. Odd!
> >
> >I'm trying to run this query against mySQL using phpMyAdmin 
> and it does not
> >like it one bit. I think I need to read up on aliasing 
> tables a bit more.
> >
> >I wonder if I should move sideways onto the SQL list?
> >
> >Regards!
> >Chris F
> >
> >-----Original Message-----
> >From: accessd-bounces at databaseadvisors.com
> >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DJK(John)
> >Robinson
> >Sent: 20 February 2006 20:39
> >To: 'Access Developers discussion and problem solving'
> >Subject: Re: [AccessD] Nested query
> >
> >That's interesting, version-wise.  It was OK in XP (2002).
> >J
> >
> >
> >-----Original Message-----
> >From: accessd-bounces at databaseadvisors.com
> >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Chris Foote
> >(Spike)
> >Sent: 20 February 2006 20:16
> >To: 'Access Developers discussion and problem solving'
> >Subject: Re: [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