[AccessD] Nested query

That's the way the query engine in Access handles the inline query under
the covers.  Your problem isn't with aliasing the tables (although I
would strongly suggest losing the spaces in your aliases).  It may work
for you if you create the query with the parens and then save it but
don't open it in SQL view again, or if you do, replace the brackets and
period with parens again before saving.    Access 97 was particularly
fussy about this.  You had to use parens because it would not recognize
the square brackets when entered by the user, even though the query
engine requires them.  

Charlotte Foust


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Chris Foote
(Spike)
Sent: Monday, February 20, 2006 12:52 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Nested query


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