1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Linking a table of users names with their user accounts in MSAccess2007

Discussion in 'Business Applications' started by mikejreading, Mar 31, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Right, for a start off I don't think I explained myself very well in the title - so I will have another go here.

    I have a table of users that I have created (imported from a spreadsheet) to the tune of about 25000 users.

    I have a list of calls from the IT Service Desk - however they have not linked the users active directory account into the call, just the callers surname and forename(s).

    I want to link all the users calls to the possible users in the database in a separate table.

    The tables I have are laid out as follows:

    Users Table:
    Code:
    UserID - Autonumber [b]PK[/b]
    User Active Directory Name - text [i](Actually their email address)[/i]
    IT Calls:
    Code:
    Callid - number [i]Unique[/i] [b]PK[/b]
    FirstName - text
    Surname - text
    Call Details - text
    Linking Table:
    Code:
    UniqueID - Autonumber
    UserID - number [b]FK[/b]
    Callid - number [b]FK[/b]
    PossibleDuplicate - Yes/No
    Basically I want to write some sort of script that will go through my IT Calls table and query the users, at the moment I am doing it by hand using the query:
    Code:
    Like "*" & [Enter Name] & "*"
    And I am having to enter users names like this, joe*bloggs (as some users Active Directory accounts have middle initials and some don't.)

    I want all the users that return more than one result to tick the Possible Duplicate box so I can go through and look at these manually later.

    Does that make sense? Any ideas on how I would go about writing a script for this???

    Cheers guys
    Mike
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Mike, first a couple of questions,
    The Users table does not have actual names in it, only email addresses?
    Are the duplicates in the Users table or the IT table.
    Would you like to "Extract" the user's names form the email address?
    One way to remove duplicates is to use a new table with a field that combines the Last, first and middle names and set it to "No Duplicates", then use an append query to transfer the data in to the new table, it will only the name to go in once.
     
  3. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    I'll try and explain again from the beginning.

    I have a Users table, sample and schema below:

    Code:
    userID       AutoNumber
    emailAddress Text
    Code:
    [b][u]userID[/u]    [u]emailAddress[/u][/b]
    1      [email protected]
    2      [email protected]
    3      [email protected]
    Then I have an ITCalls table, sample and schema below:

    Code:
    Callid      Number
    FirstName   Text
    Surname     Text
    CallDetails Text
    Code:
    [b][u]Callid[/u]    [u]FirstName[/u]   [u]Surname[/u]   [u]CallDetails[/u][/b]
    1      PAUL      SMITH   CallDetailsGoHere
    2      JOE       SMITH   CallDetailsGoHere
    3      JOE       BLOGS   CallDetailsGoHere
    Then I have a table which should link these, called LinkingTable, sample and schema below:


    Code:
    UniqueID           AutoNumber
    Callid             Number
    UserID             Number
    PossibleDuplicate? Yes/No
    Code:
    [b][u]UniqueID[/u]    [u]UserID[/u]   [u]Callid[/u]   [u]PossibleDuplicate?[/u][/b]
    1        1      1      Y
    2        2      1      Y
    3        3      1      Y
    4        9      2      N
    
    As you can see in the above, call one is from PAUL SMITH, this could be users 1 thru 3 in the users table, as all that separates them is a middle initial, which the IT desk do not record.

    So, basically, I want to query the calls to the IT desk against the users table, if more than one result shows, all should be placed in the LinkingTable with the PossibleDuplicate set to yes, however if only one result is returned it means that user is unique, and the PossibleDuplicates should be set to No.

    Hopefully that now makes sense.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Yes it now makes sense.
    But I don't think you can do it with a single query, you can put all the records in to the Linking table in one Append Query and to set the Duplicates check box I think you will need a "Find Duplicates Query" based on the Linking table converted to an Update query.

    Or you could probably use VBA code do to it.
     
  5. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    So... I'm going to have to do it by hand?? Haha.

    I can write pseudo code for what I want, just not sure how to do about it IRL.

    Pseudo:
    Code:
    for each line in ITCalls, select surname as SNAME and forename as FNAME, 
    select "*" & FNAME & "*" & SNAME & "*" from users
    if no_of_records = 1 then
    write to LinkedTable, "userid", "callid", NO
    else if no_of_records > 1 then
    write to LinkedTable, "userid", "callid", YES
    end if
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    I am not sure what you mean by "I'm going to have to do it by hand??"
    You just have to create 2 or 3 queries and run them.

    Your pseudo code can be translated in to VBA by using a recordset for the first part and a recordset between these lines
    select "*" & FNAME & "*" & SNAME & "*" from users
    if no_of_records = 1 then
     
  7. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    I meant I thought I was going to have to go through each record by hand and sort it out myself.

    I normally live in Excel - so I'm a bit like a fish out of water at the mo.

    I'll have a go and see what happens, ahha :)
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    If you need help with the queries post a dummy version of the database tables in Access 2003 format and I will have a go at it.
     
  9. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Sample data attached. For the data I have put into it I would expect the following in the LinkedTable:

    Code:
    [u]ID[/u] [u]UserID[/u] [u]CallId[/u] [u]Possduplicate?[/u]
    1  5      1      N
    2  5      2      N
    3  2      3      Y
    4  4      3      Y
    5  1      4      Y
    6  3      4      Y
    7  1      5      Y
    8  3      5      Y
    
     

    Attached Files:

  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    I forgot an Access Query quirk that you can't use "Duplicate Queries" for updating data.
    So I will create a bit of VBA code for you after I have picked up the Grandchildren.
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    I do not agree with your "expectation", surely UserID 5 must be a Possible duplicate and UserIds 2 & 4 aren't
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Anyway that is the result I physically get.
    Here is the database, there is a Query that splits out the User Names and then a Query to Append the results to the linked table.
    The 3rd "find Duplicates" query is used by the VBA code run by the Button on the form. The vba code runs the query to append the data to the Linking table and then ticks the check boxes of the duplicates found in the Find duplicates query.
    The Linking table is currently empty.
     

    Attached Files:

  13. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    Thanks for that, but it doesn't do what I wanted.

    PossibleDuplicates was meant to show when a user can not accurately be identified. The example data I gave is correct.

    In the sample data there are:

    • Two calls from BOB SMITH, of which there is only one bob smith in the users table. As such, no duplicate.
    • One call from BEN BLOGGS, of which there are two possibilities in the users table, ben.b.bloggs and ben.d.bloggs. As such - there is a duplicate.
    • Two calls from BOB JONES, of which there are two possibilities in the users table, bob.a.jones and bob.c.jones. As such there is a duplicate.

    Any further clues?
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    OK, I think this does what you want, I have had to add a names field to the Linking table, which you can delete after running the VBA code.
     

    Attached Files:

  15. mikejreading

    mikejreading Thread Starter

    Joined:
    Jul 2, 2009
    Messages:
    63
    OBP,

    Just in case noone has ever told you, you are a genius!

    Thanks so much

    Mike
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/988994

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice