DJK(John) Robinson
djkr at msn.com
Mon Feb 20 14:39:05 CST 2006
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