Results 1 to 5 of 5

Access Database is showing a number instead of a persons name for a couple of columns

  1. #1
    Dropsix is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3

    Access Database is showing a number instead of a persons name for a couple of columns

    Good day!



    I am trying to set up a database based on various SharePoint lists and two of the more important columns, which refer to a person or group are coming back with that persons ID instead.

    I read this following answer from Jim OLeary found on the Microsoft Forums:
    "SELECT [ID], [Name] FROM [UserInfo] Order By [Name]
    The BoundColumn is 1, which means that although the value the column displays is the Name, the actual value is the ID. When you join the linked table to another table and insert the value of the Person field, it inserts the actual value (the ID), not the display value (the Name). Access 2003 was not so smart - it just inserted the Name.
    The solution is to join to the UserInfo table, on the UserInfo.ID = "linked table"."linked field name", and select UserInfo.Name. Now instead of inserting the linked table id, you insert the UserInfo.Name, which is what you want"


    Now, I am wondering what the best way to go about this is (I don't fully understand the explanation given above). Do I try to get access to the list of all users in our organization, load that into access and make reference to it from one list to the other? Or is there a better way to point those columns to show the peoples names instead of the codes associated to their names?

    Hoping someone can give me a little something to go on as the best way forward for this.

    Thanks in advance!

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,460
    it all depends on what you're trying to do (exactly) and how you've designed your tables. If tblUser has an autonumber id field and Fname and Lname fields (for example) and your combo list retrieves ID and Fname, you likely ought to be writing the ID to a related table, not the name. This means there has to be a field in a related table with the same data type setting so that will accept the bound column (again, usually ID, which must be a Long if it is to be joined to autonumber ID). A query gets whatever that related data is by matching tblUser.UserID to foreign table tblSomeTable.UserID_fk, where fk suffix is often used to identify that there is a primary key (PK) somewhere in a related table.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  3. #3
    Dropsix is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3
    Quote Originally Posted by Micron View Post
    it all depends on what you're trying to do (exactly) and how you've designed your tables. If tblUser has an autonumber id field and Fname and Lname fields (for example) and your combo list retrieves ID and Fname, you likely ought to be writing the ID to a related table, not the name. This means there has to be a field in a related table with the same data type setting so that will accept the bound column (again, usually ID, which must be a Long if it is to be joined to autonumber ID). A query gets whatever that related data is by matching tblUser.UserID to foreign table tblSomeTable.UserID_fk, where fk suffix is often used to identify that there is a primary key (PK) somewhere in a related table.
    hey! thanks for the response!

    So to give context, this is really my first time using Access, so I am very much a beginner.

    Our org uses SharePoint for everything. What I am ultimately trying to do is create a database with Access which will house lists from all over SharePoint to then feed into Power BI for reporting purposes. So ideally each list would display in Access as shown in SharePoint, but since it is showing the user ID, I guess I need to find a way around that.

    So with what you're suggesting, if I got the SharePoint list of all users in the organization, I could use that as a central reference for any other list I get which displays the users as ID numbers instead, right?

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,460
    The only experience I have with SP is using Access vba to check in/out a workbook that I then populated with Access data via automation. I've never seen a SP list so won't be of much help there, but what you're asking sounds right in principle. From what little I've read, it seemed to me that interaction between SP and Access was falling out of favour.

  5. #5
    Dropsix is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3
    I may be going about this the wrong way. I was hoping to use Access to store data from various lists and libraries which could be refreshed to get new data. I am realizing now that I've figured out the user name issue. However, Access and SharePoint don't work for libraries, only lists.

    Do you or does anyone else have any suggestions? I've been using Excel but thought this might not be the best way forward...Now I am thinking it may be. The file sizes remain relatively low in Excel.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-22-2015, 12:30 PM
  2. Replies: 1
    Last Post: 08-13-2015, 05:16 PM
  3. Replies: 3
    Last Post: 07-11-2014, 04:51 PM
  4. Number ID instead of Name showing when Access/Excel Connect
    By jurbin in forum Import/Export Data
    Replies: 7
    Last Post: 03-06-2014, 04:38 PM
  5. Calculate age using a persons identity number
    By rwcozens in forum Access
    Replies: 13
    Last Post: 05-03-2012, 06:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums