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

Susan Harkins ssharkins at gmail.com
Mon Jul 11 18:02:20 CDT 2011


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 




More information about the AccessD mailing list