Discovery Regarding DISTINCT vs. MULTI-VALUED fields

[Isaac]

I just stumbled across something that shocked me. At least a quick Google search did not reveal others discussing it, but it's quite possible I missed that.

Anyway, I am working with an Access table as a linked Sharepoint list, with People (multi valued) fields.

After hours of frustration discovering the NUMEROUS ways that the multi-valued field was making my life near impossible (I'd never experienced it, since I always took the advice and never had used them before!) ....... I accidentally stumbled across a workaround for a particular problem. Thank you Microsoft for this glitch? Despite their best efforts, something works with the multi valued fields

- I can't use the Distinct keyword with a multi valued field in the query
- Except: I can run the query as TransferText, only in code, and it works fine--including distinct.

Only text. Not Excel. Only code, not right click export.

Will I come back from lunch today and it no longer works? Or is this a known thing?

 

[isladogs]

I would also say that this is yet another reason to avoid MVFs (or their near equivalent - Attachment fields)
See http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

However, although you can't use DISTINCT with MVF queries, you can use DISTINCTROW (Unique Records = Yes)

@Pat Hartman
Just to check. DAPs? I think you meant ADPs

 

[Isaac]

All I can say is we told you so You probably didn't make this decision and you probably can't change it so I'm sorry that you have to deal with it.

Well yes, of course - Like I said it's a Sharepoint list. If you want to make use of People fields in Sharepoint, (which I think most people do when creating a SP list including my boss and his boss), that IS a MVF...there's nothing I can do about it. They like them because then we can create a SP View with criteria: [PeopleColumn]=[ME], which means each person sees only their things..

But I think everyone missed the point of my post. It wasn't the negative, it was the weird "exception" to not being able to use Distinct--That I could use it as long as I saved the query with it, and then coded TransferText (export). Is that not weird or what?

@isladogs I'll have to remind myself the difference in Access between Distinct & Distinctrow - that's very good to know, thanks.

 

[Isaac]

ADP's failed because they weren't Access, they were different and couldn't even link to Jet or ACE. What the ****? An Access app that can't link to Jet or ACE? That's why I never used them.

Weeell....I know what you mean, but, ADP's were all about SQL Server, being a "true" totally integrated FE for SQL Server, and they shined like the midday sun in that regard! I thought they were awesome.

 

[Isaac]

Huh, I'll have to remember that in case I use a crosstab, haven't hardly

 

[theDBguy]

Huh, I'll have to remember that in case I use a crosstab, haven't hardly

Don't worry, you'll get reminded right away, if you forget to do it, as soon as you try to run that crosstab query.

 

[isladogs]

Pat
Apologies. I assumed you meant Access Data Projects (ADP)
For anyone who hasn't a clue what that means - ADPs were designed to connect to a Microsoft SQL Server database through the use of OLE DB. Introduced with A2002(?) and deprecated with A2010

 

[isladogs]

NP, DAP's didn't last long but I think they might have even originated in the same release as the DAP's.

Ha! As written, I'm sure that's true
This time I'm certain there's a typo

 

[isladogs]

Feel free to 'sanitise' your previous response if you wish! Anyway I knew what you meant.

Of course, in the UK, daps have for many decades been another name for plimsoles ... now rebranded as trainers!

EDIT: according to the FMS website: https://www.fmsinc.com/MicrosoftAccess/history/features.htm
DAPs - A2000 => A2003
ADPs - A2000 => A2010