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

Ok your table structure looks good. Though I have a couple of points for improvement. 

1. Don't use spaces in Object names. This can come back to haunt you. You can use the Caption property to display the name with spaces.
2. Name your PKs more descriptively. For example AssetID. Give your FKs the same name as the corresponding PK so your relationships become very obvious. So you would have Racks.RackID and Assets.RackID.
3. Don't use lookup fields on the table level. Lookups should be done only on forms. Users should never see tables directly. Lookup fields on the table level mask what is actually stored whihc can come back to haunt you.

Also, I don't see where you are adding the EmployeeID. I understand you don't think you need history, but I find such a requirement usually gets asked for. 

A crosstab query should get you what you want. The spreadsheet looks what you descibe, so I'm not sure if there is another question.

Answer 4

I haven't worked in the employee ID yet, primary because I don't quite know how to use forms yet with these new (to me) table styles. So I will be doing some research about how to incorporate the forms to do what I want. I will also rename the PK and FK fields so that they do match, this makes absolute sense. With regards to your first suggestion, I am not quite sure what you mean by objects. Are objects "tables, queries, reports, etc"? Or are they field names, or are you talking specifically about my items?

My last question: I have only been able to find info on crosstab queries that are used for summarizing data, what would I put as my "group by" selection for the serial number (since this would be the value field)

Answer 5

YES! I finally got it!

Thank you so much Scottgem for all your help! This is going to be extremely helpful and save a TON of time. I appreciate all your help and patience.