What is the proper design for an "equipment" sign out database in MS Access 2003?

Posted on 2008-11-19
Last Modified: 2012-05-05
I would like to create a database to track the movement of equipment.  Each of my company's employees has a barcoded ID badge and each piece of equipment has a barcoded equipment id.  

Here is what I would like to track:
1. Associate ID (checking equip out)
2. Equipment ID (going out, and time back in)
3. Equipment Damages / Malfunctions

This question is doesn't require a ton of detail, just direction on table design/setup and form setup suggestions.


Question by:Derek_Indianapolis
    LVL 84
    Have you looked at the Templates here:

    There's an Asset Tracking database, and an Inventory Management database that should give you some good ideas.

    Author Comment

    Yeah I did.  I was hoping that someone would be able to point me in another direction.  I've been researching and found other demos, which are similar to my application but are for 400 dollars +.
    LVL 15

    Expert Comment

    There are other sample databases that come with Access XP/2003 -
    Open Access
    Templates/On My Computer
    Databases Tab

    You then have a different Asset Tracking database to try out and tweak to uor own requirements

    Author Comment

    I appreciate the help so far, but these solutions requiring re-engineering of the template databases are more work than it will be worth.  I am not looking for an asset tracker, more of an equipment check in/check out.  

    The assocate would come to the computer with the database and the equipment to be checked out.  The clerk checking out the equipment would need to just scan the associates barcoded id and scan the equipment barcoded id.  I would then like for the scanned out equipment to be unavailable to check out again, until checked back in.  I would then like to keep history of each time the individual pieces of equipment were checked out by time and return time.

    LVL 84

    Accepted Solution

    I would tend to disagree, but:

    You'd need a table to track the Equipment, a table to track the Rentor, and a table to track which user has a particular piece of equipment at that time:

    lEquipID [autonumber, PK]
    etc etc

    lRentorID [Autonumber, PK]
    etc etc

    lCheckOutID [AutoNumber, PK]
    etc etc

    You'd need a form to capture Rentor information (i.e. your Associates), Equipment information, and one for the clerk to use when scanning. The scan form would basically have two textboxes; the clerk would enter the first textbox, scan the Associates barcode, then enter the second textbox and scan the Equipment barcode. Code in the AfterUpdate events of those textboxes would then lookup the Equipment's barcode in the tblCheckout and verify that it has, indeed, been returned (obviously it would have been returned, but your code needs to see if someone returned it without checking it back in).

    If the scan doesn't locate either the Associate or the Equipment, you'd probably need to alert the clerk and allow them to capture this information (i.e. bring up one of your other forms), then let them go back to the business of scanning.

    Of course this is extremely "bare bones" but should get you started. Much of your apps internals will depend on exactly what sort of scanner you use (most just use a keyboard wedge scanner).


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    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…

    729 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

    17 Experts available now in Live!

    Get 1:1 Help Now