[AccessD] SQL for Subquery being changed

"Max Moment"... love that. Can't wait to see "Mad Maccess", the movie.

Well, what I did was moved it to it's own query in the database. Let's see
you screw THAT up Access!

I have implemented your suggestion in the stored query just in case!

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland
Sent: Thursday, October 21, 2010 8:41 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SQL for Subquery being changed

William,

Not seen that before, have you tried changing the select SQL that it puts in
square brackets from:

(SELECT IIR_Parent, Max(Multiply_Pct) AS Max_Fuzzy FROM tbl_Fuzzy_Matches
GROUP BY IIR_Parent)

To

(SELECT [IIR_Parent], Max([Multiply_Pct]) AS Max_Fuzzy FROM
tbl_Fuzzy_Matches GROUP BY [IIR_Parent]) Just in case its have a mad moment
with some of the field names.

Paul
On 21 October 2010 13:26, William Benson (VBACreations.com) <
vbacreations at gmail.com> wrote:

> I have a query which Access allows me to code in SQL, it shows up just 
> fine in Design View as well. Somewhere down the road Access changes the
SQL.
> Change from regular parentheses to square brackets. Is this normal 
> behavior?
>
>
> Also, sometimes the query stops running for no apparent reason. I go 
> into edit the SQL, essentially do nothing but remove the final 
> semicolon and all of a sudden it is running. Since a subform is based 
> on this query it is very annoying.
>
> Original:
> SELECT C.Max_Fuzzy, A.Plant_id AS IIR_Plant_ID, B.GIB_Owner_ID FROM 
> (tbl_IIR AS A LEFT JOIN qry_103_Matched_Plants_For_Display_All AS B ON
> A.Plant_id=B.Sort_ID) LEFT JOIN
>   (SELECT IIR_Parent, Max(Multiply_Pct) AS Max_Fuzzy FROM 
> tbl_Fuzzy_Matches GROUP BY IIR_Parent) AS C
>   ON A.Parentname=C.IIR_Parent
> WHERE (((B.Sort_ID) Is Null));
>
> Access Changes to:
> SELECT C.Max_Fuzzy, A.Plant_id AS IIR_Plant_ID, B.GIB_Owner_ID FROM 
> (tbl_IIR AS A LEFT JOIN qry_103_Matched_Plants_For_Display_All AS B ON
> A.Plant_id=B.Sort_ID) LEFT JOIN
>   [SELECT IIR_Parent, Max(Multiply_Pct) AS Max_Fuzzy FROM 
> tbl_Fuzzy_Matches GROUP BY IIR_Parent; ] AS C
>   ON A.Parentname=C.IIR_Parent
> WHERE (((B.Sort_ID) Is Null));
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



--
Paul Hartland
paul.hartland at googlemail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com