[AccessD] Select Distinct not working on union query - dueto MEMO field?

Sue,

I think then (2) reassures me I did the right thing to switch to a Group By
query... since Ac2010 appears to be cutting me some slack despite my bucking
a convention. Thank you so much for the explanation.
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Monday, July 11, 2011 7:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Select Distinct not working on union query - dueto
MEMO field?

1.) You can't group on a MEMO field, but that doesn't seem to be your 
problem
2.) DISTINCT considers all the fields in your query -- all the fields in the

query combined create a unique record.

I don't believe DISTINCT is supported by a MEMO field. I know SQL Server 
doesn't, Fox Pro doesn't -- but Jet and T-SQL have many differences, this 
could be one of them.

<http://technet.microsoft.com/en-us/library/ms709437(VS.85).aspx>

Susan H.




>
> I don't understand what you mean, sorry - please explain deeper if 
> possible.
> Does it have anything at all to do with the Memo field? - because changing
> that to TEXT cured the duplication in the DISTINCT query.
>
>
> Also, do you mind commenting (or anyone...)
> I am trying to write complex SQL queries in VBA, in partial steps so I can
> evaluate the intermediary SQL for syntax errors. The below expression will
> not work unless I remove inner parentheses. I would like to understand why
> that is.
>
> Select * from
> (
>     (Select * from Tbl1)
> Union All
>     (Select * from Tbl2)
> )
>
>
>
> Access is happy with
>
> Select * from
> (
>    Select * from Tbl1
> Union All
>    Select * from Tbl2
> )
>
>
>
> -- 
> 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