Key Control Database with transaction logs though process

[dnd214]

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.

[mike60smart]

Hi
Can you upload the actual database?

[dnd214]

Im trying but the database if 700kb and it wont let me upload anything over 500kb

[mike60smart]

Hi

You need to Zip the file before upload

[dnd214]

Database zipped and attached.

KeyControl_FE__Scrubbed - Copy.zip

[mike60smart]

Hi
I am a little confused with the Key Series.

If I select Keyway A as an example would the Key list be as follows:-

A-1A1
A-1A2
A-1A3 etc upto
A-1A10

If this is not correct can you give me an example of a full Keyway Key Listing?

[dnd214]

In a way yes. The keyway is really just a slot cut in a key. It's not as important for the database overall as the key series and the key serial would be the main components for tracking.

So for Keyway A you could have 1A1 - serial numbers 1-10 which we would normally annotate as 1A1/1, 1A1/2, etc.. you could also have 1B1/1, or 1Z2/1.

You could technically have the same key series for Keyway B, but because of the slots the same key series would not actually work on the other keyway.

When its labeled as 1A1 or 1A2 that just means its more of a master submaster series setup. So 1A1 can open all doors with 1A2 and 1A3 core locks. 1A2 could do all of 1A2 and 1A3, and 1A3 could only do 1A3. When we issue a key to the individual, we just issue it as the key series and the key serial, so when we do our inventory/inspection we want to make sure if we issued 1A1 key 1 to an individual we want to make sure they still have that key, as if they are missing it, or somehow they now have 1A1 key 2 we need to question where there key is, and why they have a different key.

[mike60smart]

Hi

More confusion reigns.

Initially you said there were 2 Keyways - A and B

Now you are saying there could be A, B or Z ??

[dnd214]

In my system we only have two keyways A and B, but there could be a good number of different keyways.

The Z I was referencing would be the Key Series. In my last post it would be (key series/key serial) (1A1/1 or 1Z2/1).

The keyway really does not have much to do with these numbers. If you look at your car keys or really any key, you will see a long horizontal indent. These can come in all different thicknesses, widths, etc. Those lines are what constitute the keyway, so overall the keyway is part of the database, but only in the sense of we could have a 1A1 series in both the A and B keyways, so adding the keyway would add clarity to which keyway is which. Generally, you won't have this, but I want to add it in there now just in case.