[AccessD] Nested query - SOLVED

Just to close this thread-

I ended up using the SQL below:

<sql>
SELECT t1.*
FROM tblClubs AS t1 LEFT JOIN tblClubs AS t2 ON
(t1.fldIDClubs<t2.fldIDClubs) AND (t1.fldClubCode=t2.fldClubCode)
WHERE t2.fldIDClubs IS NULL
ORDER BY t1.fldClubCode;
</sql>

It works fine in A97, A2k3, and MySQL 4.0.23a.

My problem seemed to stem from the fact that version 4.0.23 of mySQL does
not support the MAX function.

Thanks to everybody who contributed ideas and solutions!

Regards
Chris F

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
> Foust
> Sent: Tuesday, February 21, 2006 4:33 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [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