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

When I changed the field type from MEMO to TEXT and found Distinct started
working again... I was pretty sure the MEMO field was the reason. I know
MEMO causes problems with soe other areas of SQL... however, I appreciate
you confirming it.

What I meant about bucking convention was based on you writing:
 
 >> You can't group on a MEMO field

I appear to be able to group on a memo field without a problem in my current
application.
Thanks again,

Bill

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Monday, July 11, 2011 8:34 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Select Distinct not working on union query- dueto
MEMO field?

I'd be surprised if DISTINCT worked against a MEMO -- although the link only

mentions dBase, I'd still be surprised. I'm not sure I understand why a MEMO

field would need to be part of a GROUP BY or a DISTINCT, but as long as it 
works. :)

Susan H.

>
> 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.
>
>
> 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.

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com