Access Database Auto Populate Field on Table Based on Values of Multiple Tables with the same Field

Question

Hi, I am somewhat new to Access and I have a question if I am doing this right and if so if it's possible to auto-populate a field on table based on the information from a similar field on multiple tables.

I know it is not generally a good idea to have duplicate information in a database, but I feel it's the only way to do this.

Background: I use access to manage an armory (weapon storage facility) of over 700 different individual items. Each item has a serial number and multiple items (not more than one of each item) are assigned (issued) to people and stored inside the armory when not in use. I use access to manage the location, equipment status (working, damaged, sent out for maintenance, etc), and who the items are assigned to.

How it works: I have 14 tables for each separate type of object inside the armory. Inside each table are 5 fields: Rack (physical location of the item), Serial Number, Status, Date of Status, and Notes. Because multiple items are stored inside the same rack space, I use a separate table called Rack where I put the rack number (A1, A2, B1, B2, etc) and the employees ID number (not assigned by access). Using these 15 tables, I generate a query that matches their ID Number to the Rack Number and then displays each item in that rack assigned to that individual. This allows me to make a full table with their name, ID number, and any items issued to them. I believe this is the most efficient way to do this; I know it's kind of confusing, but maybe you have a better idea on how to do it. When I go to assign a rack to a person, I look for an open rack and type in their ID Number.

Now the problem:  When I fill in the rack and serial number on the items table, I have to make sure I also put the rack number into the "Rack" Table. Granted this could easily be accomplished by just manually adding A1-100, B1-100, C1-100, etc but this puts a lot of unnecessary data into the table, because not every rack (physical location) exist inside the armory. So I would like access to automatically fill in the Rack number in the "Rack" Table based on what racks are in the individual item tables. This way if i add an item to table with rack R13 and it's serial number, the rack number will automatically pop up in the Rack table and I can assign it to someone, without having to manually make sure I add it to the rack table (avoiding the human error of forgetfulness).

Is there a way to do this? If not, no big deal, I am just trying to avoid a bunch of unnecessary data inside my rack table.

Thanks in advance for your help! Constructive criticism on how i run the database is welcome (not just, that's a stupid way to run your database!)

Some images of how the database works:

The Rack Table-

Match rack numbers to EDIPI's (Employee ID Number)-

Issued Weapons Query

Just in case, this is what each of the item tables look like-

Answer 1

The forms don't really depend on the table styles. From what you have, there should be a form bound to the Assets tables. On that form, you should use comboboxes to select all the FKs (EmployeeID, Item, RackID).

I would add a lookup table for Status as well.

Everything in Access is an object, tables, forms, queries, fields, controls (on forms), etc. are all objects. Objects have properties. You control Access by modifying the properties of its objects.

Use the First of option to pull the SNs into the grid on your crosstabs. Use the Wizard to create the crosstabs.

Answer 2

I suggest you read up on Normalization. You will find a good beginners reading list at utteraccess.com.

Your database is not constructed properly, which is one reason you don't see the solution. There is no reason to have a separate table for each type of object, especially when the tables have the same construction. You should have one table with a field for object type. based on what you have said you need several tables here: 1) Employee table, 2) Item table; ItemID (PK), SerialNumber, OjectTypeID, RackID, 3) ItemStatus table; ItemStatusID, (PK), ItemID (FK), StatusID (FK), StatusDate, etc. 4 Lookup table for Object Type, Rack, Status, etc.

As you are aware you shouldn't be duplicating data. except to include foreign keys (FK) that refer to the record in a related table. This way, you don't need to duplicate data.

Answer 3

Okay, I figured out how to make foreign keys and link them together. I used this guide: http://msofficeuser.com/pages/access/creating-primary-keys-and-foreign-keys-in-microsoft-access

My next question is: how do i display the data in the linked table on a query, but with the linked info going across as row headers and the serial numbers listed below?

Essentially, how would i display data as such:

I am using the Rack as the PK, because it will always be unique inside the Rack table. This way I can add as many items to a rack.

This also does not solve my original problem, when I add an item under Assets and put in a new rack number (not already defined in the Racks table) it doesn't auto-populate in Racks.

Answer 4

Using PKs and FKs are only part of the issue of normalization. Normalizing your database properly is key to a efficiently functioning database and you haven't gotten it yet. You misunderstood what I said. I said; "You should have one table with a field for object type" What you apparently did was create a table with a field for EACH object type and that is incorrect as well.

You originally listed the structure of your 14 tables as; "5 fields: Rack (physical location of the item), Serial Number, Status, Date of Status, and Notes." What I'm saying is that this appears to be different data. So you need to parse it out. The way it appears to me is you need a table that lists each item. That table should have details only abut the item itself with one record per item. So you need a field for the ItemID (which I would use an autonumber field for), the ItemTypeID (which would come from a lookup table of item types, i.e. the 14 types that you previously had separate tables for), the SN for the item, and the location of the item (RackID). The status of an item is a different set of data points. Also, I would assume you want to track the status history. So you would need to have a record for each status. Something like: StatusHistoryID (PK Autonumber), ItemID (FK), StatusID (FK from lookup table of statuses), AssignedTo, EffectiveDate. So anytime the status of an item changes, you would add a new record for that Item. 

I don't know why you would want to display like you described with the Item Type as columns and the SNs as rows.

Answer 5

I have all what you are describing setup. After I posted this last evening I created a table with the item types, and you select it using a lookup and relationship field under the assets table. Everything is working the way I want it to with that and as far as I understand, nothing is redundant and it follows normalization. The need to track status is history is not needed as only the current status is necessary and that is accomplished under the Assets table where the serial number is listed. So (i think) I'm good with the structure of my tables.

I am trying to use a query to display the information with the item type as columns and the SN's as rows (with the rack number on the far left column) because it is a requirement from my boss to make a master issued gear report (which i used to have a query that would generate this) and I would export it to excel and email it to him. This identifies what items are issued to what person and it's extremely simple to read.

A sample of what I was able to produce with data included is below:

I blanked out the Names and EDIPI (Employee ID Code) for privacy reasons. But those are filled in fields that my old query used to match everything with using the old setup I had.

My new table relationships look like this:

I really do appreciate your help with this, I don't have any formal training with MS Access and I am trying to come up with a more efficient way to manage unit armories because I am changing positions in August. So THANK-YOU very much for your time.