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?

 

[Pat Hartman]

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.

Going back in time though, the reason that the multi-value field got into Access in 2007 was to make Access more compatible with SharePoint because rather than actually giving us a rational way to use ODBC across the web, MS wanted us to use SharePoint because DAP's had failed and the later AWA's failed. And we're still asking for a rational way to use ODBC across the web. We do NOT want to reinvent the wheel. We like Access as it is (for the most part), we just want to access data that is not on our local servers

 

[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

 

[Pat Hartman]

DAP's were Data Access Pages and MS' first attempt to webify Access. 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.

 

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

 

[Pat Hartman]

I did get it but got lost in the rest of my commentary. I've seen many places in Access which are inconsistent. One I run into regularly is CrossTabs with arguments. For all other query types, you can just add an argument and be done with it. For CrossTab queries, you MUST define any arguments, even if they occur in sub queries.

I know that the people who adopted ADP's loved them. I found them useless since they constrained how I had to work and they limited my options. When they first came down the pike, I was at a client site where they used Oracle almost as much as SQL Server and I couldn't link to both databases. They also took way more code than a standard bound form. I started my love affair with Access when I did a project for Reader's Digest that used Access to get to DB2 on their mainframe. I thought I'd died and gone to heaven. All the tedium of coding in COBOL, gone, forever! I was sold but never on ADP's

 

[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

 

[Pat Hartman]

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

 

[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

 

[Pat Hartman]

You caught me I just washed my hands and can't do a thing with them.

 

[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