Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Question Unanswered: Alphanumeric serial number tracablility

    I am trying to create a sheet that relates a range of alphanumeric serial numbers to a lot number. The serial numbers are in the form of AA001, AA999...AB001, AB002, etc. I am looking for a solution to relate a range of these numbers to a lot number. I would prefer not to have a record for every serial number but have them expressed as a range (i.e. AA001-AA030). Is this possible? If not and a record for each serial number must be created, is there an easy way to batch enter a range of the serial numbers?

    The only way I have found to make this work is by splitting the serial number into two fields with the alpha prefix and a numeric suffix. Is there another way?

    Using Access 2007
    Last edited by panthrosrevenge; 11-16-09 at 13:31. Reason: added current solution

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    explain why the way that you found that works is not sufficient? as the answer to this may not be to find another way - but to make your way acceptable....

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    I feel that if there is a record for each serial number the database will grow too large and be slow to search/filter. If there could be one record for each lot number with the serial numbers expressed as a range it would keep things nice and tidy.
    .
    Also, I don't know how to automate the entry if a record needs to be generated for each part.
    Last edited by panthrosrevenge; 11-17-09 at 12:28.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you get to the point where one record per serial number is too unweildy then in all honesty you have likely reached the rational limits of what one can expect Access to handle.

    If you're REALLY gung-ho on going this range route, store the start and end range as separate fields. Say something like "start_lot_serial_prefix, start_lot_serial_number, end_lot_serial_prefix, end_lot_serial_number"

    However, don't come back to regale us with war stories later should you choose to pursue this route. You have been duly warned that such a design is brittle and inflexible.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2009
    Posts
    340
    Panthro - - - I can understand why you don't want to have a record per serial number. That wasn't my question. You stated you know the way to do what you wanted but it requires splitting the serial number.

    Why is splitting the serial number a problem? If you explain that, as perhaps the solution is to then unsplit the serial number...because your serial number is both alpha/numeric and it is tough to get ranges using < > type symbols if there is alpha....so my first thought was just to split that serial number....use some math logic on the numbers to bound the range...and then put the serial number back together again.....

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Relational database design almost demands that there be one record per lot number/serial number combination. It needn't be too onerous in terms of database resources.

    Assuming that the lot numbers and serial numbers are held in different tables, all you need is an intersection table to hold the relevant combinations.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

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