Mark A Matte
markamatte at hotmail.com
Mon Feb 20 15:41:44 CST 2006
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 > > > -----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 > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com