Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    18

    Unanswered: Is it possible to have Access 2003 generate random 7 digit alphanumeric strings?

    First off, let me say that I am quite new to using these kind of capabiliities within Access 2003. My previous databases have been quite simple (keeping track of my record collection). But now, I have been presented with the task of generating "client id's" for a small business.

    I guess what I really need to do is to have the generator serve as a field within a table that will house client name, address, etc...

    Any help at all would be appreciated. Thank you.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If preventing duplicates is the goal for the Client ID, then you could concatenate part of the name, or address, or city, or state, etc... and save that into a primary key field (i.e. with no duplicates). You need to decide on what fields and length of characters to use as it can vary on what information you're collecting and how specific you want it to be (for example, I've used LastName + first 4 characters of address + city) but I've also used different combinations as well. I've even had to break out the address into different fields (i.e. StreetNumber, StreetDirection, StreetName, StreetSuffix, etc.. and used the StreetNumber or StreetName (for example)) Otherwise, I'd just use an autonumber field.
    Last edited by pkstormy; 04-03-08 at 00:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why on earth does it have to be 7 alphanumeric characters, and not a simple auto-incrementing identifier?

    Even if you do end up going with some naffy self generated string (no offence Paul), which I cannot condone, you should (read MUST) include a surrogate key to ensure uniqueness.

    Oh and if it's in the specs that it has to be 7 characters, still use your autogenerated integer and display it as that when necessary.
    Code:
    Function ShowClientID(client_id As Integer)
    
        ShowClientID = Right("0000000" & CInt(client_id), 7)
    
    End Function
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a classical way of doing that is to use say the first 5 letters of the name and 2 digits. eg SMITH01
    so when creating a new accoutn you would select the max (or top 1) "Smith" account ID, and then split out the numeric part, add oneand away you go. Ive actaully seen a slightly more devious technique of alwasy havign thr next account neumber ready to go

    say SMITH05 is the currnet highest number... thast system wrote a SMITH06 as a blank skeleton record....

    personally Id be tempoted to make it a 3 digit number

    I think one of the reasons proffered for this is ease of retrieving data, or ease of manual filing. certainly the SMITHXX system is very common on things like SAGE

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why on earth does it have to be 7 alphanumeric characters
    Yes, I am curious on that one too
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by georgev
    Even if you do end up going with some naffy self generated string (no offence Paul), which I cannot condone, you should (read MUST) include a surrogate key to ensure uniqueness.
    Absolutely no offense taken George. Although it was "somewhat" nice having a concatenated primary key which helped identify the customer record fairly easily with 1 field, I found that the success of it actually preventing duplicates was not perfect as I had hoped (but somewhat effective in a check-writing database.) The best method was showing a popup form of "similar" customers to let the data entry persons select the matching customer. I had a few cases where the data entry person came to me and said they kept trying to enter a customer but couldn't because it told them it would duplicate a customer (yet they kept trying and trying and trying to enter that same customer and kept ignoring the popup form showing similar customers.) - go figure. Even though the fields used to create the primary key was thought out ahead of time for months, duplicates still found their way into the db. I think I had approximately 200 duplicates which wasn't bad out of 3 million or so customers. Duplicates were very bad in my case as we didn't want to send the same customer duplicate checks. Geocoding the data was the absolute best way (but you still had examples of people calling in as Tom at one time and Thomas (or his wife Mary) another time.)

    Personally, I think creating such a primary key (to prevent duplicates) should be thought out carefully with what data is collected and on what fields are used to create it and it's not the best solution for all databases. I did like the aspect though of identifying a customer record on 1 field which was beneficial given the number of records I was dealing with. It made some of the queries easier and faster to locate a specific customer and linking on that field between a customer/address table made it easy for data corrections (but more difficult on re-creating the new primary key if data consisting of the primary key needed changing).
    Last edited by pkstormy; 04-03-08 at 00:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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