Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62

    Unanswered: Unique ID after the fact???

    Iv'e inheireted a DB that was made by someone with even less knowledge about Access than me.
    I;m in a University and th Dr i work with siad she needs unique id for all clients.....
    The problem is were 2 years into it now and 3000 clients (who have multiple entries)
    How can i Apply a unique ID number to these people??
    I have run a distinct query and applied a unique number to each person how ever how do make it so that every instance of "John Doe" has the same number?
    I have the distinct clients with ID's in a seperate table, and another table with all info from a survey we have sent out. Would i run a query and if so how do i pull criteria from a specific column?

    Please help, and thanks in advance
    Brownie

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you are happy that your DISTINCT query returns truly distinct "John Doe"s...
    copy it and switch the copy into a make-table query & run it to generate the table.
    then design view the new table and add an autonumber field (your unique ID).
    add a long to the original table and you should be able to insert the uniqueID foreign key back into the original table using your DISTINCT query to tie the two tables together.
    all that's left is to remove "John" and "Doe" from the original table so this stuff comes from the new table.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2003
    Posts
    6

    Add a field to the existing table & run update query

    You could also do the following:
    Add a ClientID field to the main table and survey tables

    Create an update query that will update the main and survey table's new ClientID fields with the ID number from your client table where the client's name is a match.

    Basically like this:
    Create update querys to update both main and survey tables
    In the ClientID column of your update query you'll need an statement in the "update to" like:

    IIF([tblMainTable].[clientname] = [tblClientTable].[clientname], [tblClientTable].[ID], null)

    Hope this helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •