Create my own Unique ID number to track records and link records in different tables instead of using AutoNumber

Posted on 2007-10-04
Last Modified: 2013-11-29

I have a two Excel Spreadsheets,rds and the second contains 4000 records.  I have converted the files into Access called tblPartInventory (6000 Records) and tblPartTransfer (4000 records).  

When I receive a new part, I enter the PartName, PartNumber, and VendorIDe information into a table called tblPart.  When I have to catalog the part and place it on the shelf, I add a Barcode number, part serial number, and additioal part information into to a recvord in tblPartInventory.  When I have to transfer the part, the part information is deleted from tblPartInventory and moved to tblPartTransfer.

Both excel files that I received do not have a unique product ID, that can be associated with a part's PartNumber or BarcodeNumber, therfore I canot create a one-to-on or one-to-many relationship.  The data was just entered into colums on the spreadsheet and as a result; I cannot use the Parts Barcode Number, Part Number or Part Serial Number because 50% of all the fields are blank.

Please help me come up with a way to assign a unique ID number to all related records in both tables that can be added to an partID field the same way Access AutoNumbe works each time I create a new Inventory record.  I would perfer not to have to enter the unique on each line manually.
Question by:cesemj
    LVL 1

    Assisted Solution

    The Easiest way I can think of to relate them would be to add an auto number to both tables and then create a new join table with both numbers in it to relate the records.  It depends on how you need them to be related as to whether or not this solution will work for you

    Author Comment

    Is there a way to automatically create a random alapha numeric value ike, AUTO1234567, that is automatically entered into the ID field when I enter a new record from a form.
    LVL 19

    Accepted Solution

    not directly but there are several workarounds that can be done.
    one is to write some fancy code to do this, or the simplest way is use a composite key (ie two fields) as your primary key, one alpha the other an autonumber field.
    the alpha field doesnt have to be unique here due to the uniqueness of the numeric field so you can use a default value for the alpha field or use code to generate a random value, something like:
    chr((round(rnd * 25,0) + 1)+ 64)
    for each character

    or still use 2 fields (first autonumber, 2nd alphanumericID) where just before the user "submits" via a command button, the button will populate the alphanumericID field with concatenation of the alpha part (using the code above) + the autonumber value from that field. the difference here is that your alphanumericID  will contain the AUTO1234567 so it will be unique.


    Author Comment

    IThank you, I am going to work on this now and let you know what happens.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now