Hello all.
I am struggling with finishing out the table design for my database. The current structure works fine for the basic part of what I am doing, but not for what I want it to do.
Background:
Our employees sign for door keys when the inproccess. Our keys have a key number, a keyway, and a serial number. For instance Key Number Q1 is in Keyway A2 and has 10 keys so there would be a record for Q1 - 1, Q1 - 2, all the way up to 10.
There are a total of two keyways. Each keyway has multiple key numbers and each key number can only have one keyway.
An employee can sign out multiple key numbers and each key number can be signed to multiple employees.
Thats where tblProjectkeys comes in. It pulls in the ID from tblKeySeries which is a list of all the key numbers and their keyway, and creates a new record for each serialized key.
Each employee can have multiple serialized keys, but each serialized key can only be with one employee as there is only one of each serialized key.
This all works great, and I have a form that shows employees and the keys assigned to them in a subform.
Now the harder part.
I currently have the table "tblKeyInventories" Every x amount of months we look at everyones keys to make sure they still have what they originally signed for.
There are also times someone signs for another key, or turns another key in.
What I need to be able to do is select a key or set of keys and say they were inventoried and have each of those keys and the date of the inventory recorded into a table.
Also when a key is signed out, or signed back in, lost, etc I need a transaction log that says what happened. Key so and so was signed out to employee on DTG. These transaction logs would also need to be as a subform for the employees form.
What I am asking for is if, my current structure looks like it is of sound principle and what should I add/change to do more of a transaction log style table. Its almost like an equipment rental kind of thing, but cant wrap my head around it. Not sure if it needs junction tables, or a table for status, etc.
I am doing this in O365 version of Access.