Ms-access – How to track specific item as part of a kit

ms access

Here is my situation. I have over 50 boxes each with a unique ID number. That number is tracked for inventory purposes. My problem is that inside each box there is one specific part that is tracked independently of the box. What I am trying to accomplish is to be able to track the serial number from the part and link it up to which box it goes in such that the following rules are followed:

Each box cannot contain more than one primary part
Not every box has a primary part (some were broken and never replaced)
If a primary part needs to be moved from one box to another the original box it was taken from shows up as having no primary part.

I thought this would be fairly easy to accomplish with MS Access since it is already deployed across the network, but I am having a hard time preventing multiple boxes from being assigned the same primary part. If I just put the part SN as a field in the same table as the box then I have to remember to change 2 things any time one of the parts moves.

Best Answer

Put the Box ID against the primary part, instead of the primary part serial number against the box. To see what's in the box you need to search the Primary Part table for the Box ID. If you move which box a part is in, you just update it once.

You could make a unique index on BoxID as well to ensure that there are never 2 primary parts in the same box.

Related Solutions

Ms-access – Database to track employee performance

Following the question and your comment - I'm not sure that I remember correctly, but I think that MS Access doesn't allow for a primary key with multiple columns, so you'd better add an auto-increment (autonumber in Access) integer column as PK, and add the other needed columns: EmployeeID, Date, Performance, Quality, Attendance. I'm sure that the Employee table could benefit from having a EmployeeID column (simply just use an int that's autonumber or use the SSN of that person..or whatever id you think you have and it's unique). This EmployeeID column would then be used as a reference in other tables (as the one with gathered values for P, Q and A).

Also, I wouldn't make a separate Manager table, but add a column called ManagerID to the Employee table and make it reference the EmployeeID (eg: for EmployeeID = 5, the ManagerId = 1, and based on the IDs you will get their names).

Later edit: found out I'm a dumb Access user, MS Access allows multi column PKs :-). Anyway, it's still much more simple to manage users and reference them by an ID, and not by a compound PK.

In your current situation I'd make 2 tables:

Employee - EmployeID, FirstName, LastName, Email, ManagerId; and a second with
History: ID, EmployeeID, Date, Performance, Quality, Attendance.

Should be enough for what data you want to gather now.

Ms-access – Create primary keys based upon higher-level entity in access

I think the difficultly lies in using the parent table as part of the sub-table's primary key. So long as you have a reference to the parent table, you can always generate a reference that combines Location+Room or Room+Rack. Otherwise the primary key will get too hard to update and you have bad primary key.

I can see two ways to tackle this depending on how many locations, rooms, racks you have. It depends on quantity for your interface.

Firstly, a schema...

tblLocation(locationID[PK++], locationReference)
tblRoom(roomID[PK++], keyLocation[tblLocation.locationID], roomNumber)
tblRack(rackID[PK++], keyRoom[tblRoom.roomID], rackReference)
tblDevice(deviceID[pk++], keyRack[tblRack.rackID], positionU, deviceLabel)

...tables for custom devices can extend tblDevice

Foreign keys in square brackets, PK++ means an auto increment primary key.

As you can see, a Location has many Rooms, a Room has many Racks, a Rack has many Devices.

I have made primary keys as auto IDs as I prefer them to be integers and there's less to think about should you change a location/room/rack reference.

Populate this with Locations, Rooms and Racks as appropriate.

Solution 1 :: Not a lot of racks

Have three drop downs on the form.
User chooses a location, you then select all the rooms of that location in the next combobox.
User then selects the room, you then fill the next combobox with the racks in the room.
User then fills in device details and U position (which you probably want to validate to make sure that you don't have anything allocated there.)

Solution 2 :: Lots of racks

Have a query similar to:

SELECT
    rackID,
    locationReference + '-' + roomReference + '-' + rackReference AS [Full Location]
FROM tblLocation
    INNER JOIN tblRoom
        ON tblLocation.locationID = tblRoom.keyLocation
    INNER JOIN tblRack
        ON tblRoom.roomID = tblRack.keyRoom
ORDER BY
    locationReference ASC,
    roomNumber ASC,
    rackReference ASC

To generate a list of Location-Room-Rack references.

ABC-101-A/1
ABC-101-A/1
ABC-101-A/2
ABC-101-A/2
ABC-105-B8
ABC-105-B8
FAY-208-X8
FAY-210-A9T
FAY-210-A9T
FAY-210-F4
NAT-410-78
NAT-410-78

User selects one, then fills in the information. List will be equal to the number of racks (which could get a bit excessive).

Advantages

If you move a rack (or racks) of equipment from one room to another, it's just a simple update query to update the database
If a location/room/rack reference changes, you only have one piece of data to update.

Further schema additions

I've used racks in the past, I simplified the schema but these are some additions that spring to mind.

Add rackSize to tblRack: the size in U's
Add sizeU to tblDevice: the size of the device in U's

You can then do further validation: if you have a 39U rack and somebody tries to put a 5U device in at location 38, you can throw an error (as U's 40-42 don't exist!). Also if somebody puts a 3U device in at location 10, and something is in location 11, you know it won't fit or they have not updated the details of the device that is no longer there!

You could also make some clever queries that would tell you how much space there is in your racks (and where). But that's beyond the scope of this question!

Hope this helps.