sharepoint lists linked to access - querying / linking

[Isaac]

I linked a sharepoint list to access today; it's been a while since I did that (since I hate trying to mix sharepoint & access for these precise reasons - it really obfuscates the clarity of presentation vs. data!), but anyway, I have no choice in the matter and will probably be utilizing access quite a bit for manipulating or reading sharepoint lists.

one of my first sources of confusion was the linking of 'people' type of fields (in the Main table), to [something?] in the Userinfo table. As soon as I added both tables to my query, Access suggested 3 links. And basically they suggested nothing more than joining the people fields directly to the ID field in the userinfo table.

this all sort of makes sense to me, except , when I see the sharepoint list, the people fields of the main table are text, not numbers. (except in the design view of the linked main sharepoint table, they DO show as numbers).

how am I to interpret/use this? high level/reusable information please? I guess a sharepoint list is like mixing data with presentation, unfortunately …….. you see text in the people type of fields, but underlying it must be an ID that is the bound column.

nonetheless, when I tried to run a generic query with these joins, it brought up nothing, so access's suggestions - though telling, were not successful in this case.

ideas/thoughts / things to study from here all welcome!

 

[theDBguy]

Hi. Welcome to AWF! Every SP List automatically gets a numeric ID column. It's usually hidden, but can change a view and show it. To say why your query didn't return anything, you may have to show us what you did. If I remember correctly, the people/users table/list is the list users registered with access to your SP site. How does this list relate to your List/linked table?

 

[Isaac]

in the main list, there are a variety of people type of fields. like QC Auditor. when i'm on the sharepoint list online, they are people lookup type of fields - like you click an icon next to it to go into the global address list and select a person, and then the value in the field shows up as a person's name with a line under it. however, when I link the list to access, and go to design view, that field shows as a Number. so even if I open the linked table and see John Doe as the value, if I run a query looking for 'John Doe', it comes up blank.

however I just remembered I should check to see if they are a lookup fields and it is. in ms access the design of linked table on those people fields shows a Lookup as, SELECT [ID], [Name] FROM UserInfo ORDER BY [Name]; with combobox as type of control and bound column = 1.
this makes it seems like I ought to be able to join the people field directly to ID in UserInfo..

 

[theDBguy]

in the main list, there are a variety of people type of fields. like QC Auditor. when i'm on the sharepoint list online, they are people lookup type of fields - like you click an icon next to it to go into the global address list and select a person, and then the value in the field shows up as a person's name with a line under it. however, when I link the list to access, and go to design view, that field shows as a Number. so even if I open the linked table and see John Doe as the value, if I run a query looking for 'John Doe', it comes up blank.

however I just remembered I should check to see if they are a lookup fields and it is. in ms access the design of linked table on those people fields shows a Lookup as, SELECT [ID], [Name] FROM UserInfo ORDER BY [Name]; with combobox as type of control and bound column = 1.
this makes it seems like I ought to be able to join the people field directly to ID in UserInfo..

Hi. That's correct. Let us know how it goes.

 

[Isaac]

When I did a inner join between the people type of field in the main list, to ID field in the userinfo table, I only got 4 results. (the list has thousands of records in it).

 

[theDBguy]

When I did a inner join between the people type of field in the main list, to ID field in the userinfo table, I only got 4 results. (the list has thousands of records in it).

Just to troubleshoot, only include your list and the userinfo table in the query, do you still only get 4 records back?

 

[Isaac]

Yes those are the only two tables I am using

 

[theDBguy]

Yes those are the only two tables I am using

Hmm, that would tell me the IDs in your list don't match what's in the userinfo table. Is that possible? Meaning, where did the list come from, originally? Was it imported into SP?

PS. If the people in the list is a lookup column, maybe it's looking them up from a different list than userinfo. Can you check?

 

[Isaac]

I know there is at least one record there that I just created yesterday. And even that doesn't come up when I join the person field (which displays a name when I open the table, but according to access design view, is a number) over to Userinfo.ID

 

[theDBguy]

I know there is at least one record there that I just created yesterday. And even that doesn't come up when I join the person field (which displays a name when I open the table, but according to access design view, is a number) over to Userinfo.ID

Hi. The only thing I can say is computers can only see numbers. So, if it doesn't make sense to you, we'll need to find out why. Unfortunately, if I can't see what you're looking at, I can't tell you where to look. How about creating a new list with just a couple of columns but include a lookup to the userinfo table. Then add a few rows in it and then link it to Access. Do you still get the same problem? If not, then there's something definitely off with your original list, which I asked how it was originally made or where it came from, which you didn't specify.

 

[Isaac]

Ok I'm not sure about my access level, I am going to try to do those 2 things and get back to you. Thanks

 

[theDBguy]

Ok I'm not sure about my access level, I am going to try to do those 2 things and get back to you. Thanks

Hope it helps...