Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Update Table (Access 2003/SP 1)

    In the early days of my Access use, I had tables without any primary keys. Now I have a table that I have been using, but I'd like to insert a field for a primary key. This would be the CustomerID or something similar. The way we have this Customer ID is like this: 01234henjo01.
    John Henry
    41 Somewhere Ln.
    What Town, KS 01234.

    Explanation: We take the zip code (this is just for USA addresses), the first 3 letters of the last name, the first 2 letters of the first name and add 01. If we ever have someone in the same area with a similar name (such as Joana Henrique) we add 02. Is there anyway that I can run some kind of append query or a RunSQL code that will do this automatically so I don't have to go back through about 1,000 addresses manually?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Update Table (Access 2003/SP 1)

    Why set the primary key to what you are trying to do, why not set a new field CustID as an autonumber field, this can then serve as the primary key.

    If you want to setup another key for what you are doing, why not use multiple fields as the index instead. eg Postcode, Lastname, Firstname

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Table (Access 2003/SP 1)

    I guess I haven't found a real appreciation for Autonumber.

    <hr>If you want to setup another key for what you are doing, why not use multiple fields as the index instead. eg Postcode, Lastname, Firstname
    <hr>
    I'm not sure I understand what you mean by this. Do you mean 3 fields that would make up the primary key, or the full post code, the full lastname and the full firstname?

    I'm going to try John Hutchinson's suggestion and see what happens.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Table (Access 2003/SP 1)

    Thanks John. I did most of what you suggested. What I did was checked to make sure the query would work correctly, then I opened the actual table and sorted by PostalCode ascending and did that in the query also. I then just copied it over and checked to make sure it copied correctly. It did. I also used StrConv to make it all caps as that is how we wanted it stored. Thanks a lot!

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update Table (Access 2003/SP 1)

    Firstly, in Design view add the extra field CustomerID as a text field. Initially don't make it the Key.

    Next create a query based on the table.
    Add the three fields Zip, LastName and FirstName.
    Now Calculated Field like this
    NewID: [postcode] & [left([Lastname],3) & Left([firstname],2) & "01"

    Switch to datasheet view and the new field should show you NEWID containing what you want.
    (This first step is just to be careful)
    If the results are OK, go to the Menus. Query...Append Query. Ooops..Just noticed... this should have said Update Query
    The query grid will now have an additional line "Update to"

    Add your new CustomerID to the query and paste the expression

    [postcode] & [left([Lastname],3) & Left([firstname],2) & "01"

    into the Update to line.
    Then execute the query by Clicking the Red apostrophe.

    Now you have to manually adjust the duplicates, but first you can see how many there are.
    Change the query back to a select query, and remove all the fields except your new CustomerID and Last Name.

    Click the Totals buttons on the toolbar, and count the LastNames when you group by customerID, and sort descending on Lastname.
    Most will have a count of 1, but the few that have higher counts will be at the top. Take a note of those names (or print the first page of the query) then change the query back to a plain select quey and go to those lastnames and manually adjust the CustomerIDs.

    You should then be able to go back to table Design and make that field the Key. (you can't do that if there any duplicates.)
    Regards
    John



  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update Table (Access 2003/SP 1)

    I glad that worked.

    I hope you were not thrown by my mistakenly writing "append query" instead of "update query".

    John
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Table (Access 2003/SP 1)

    Yes, I did an append query first and it just added a bunch of new records with just the NewID, FirstName, LastName and PostalCode. I had to delete those all. No harm done.

Posting Permissions

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