Re-thinking use of table for issuing keys to gate

[PDilly]

Some years ago we used to issue about 200 returnable numbered keys to people every year.

My DB has a key register table which is already populated with the available key numbers.


at an appropriate point an update query is run against a table of people and their names are added into the key register against the appropriate key number.

some years ago we found that the admin for key returns was too long winded and gave it up, so now the entry just lasts a year.

i could do away with the key register and simply use a query against the people table to see who has what, but for the fact that it gives the number of unused keys. Which is useful.

the update query works but it’s all too easy to put a the same key number in two records and overwrite one of them. To be fair it’s only happened once in ten years!

It might be better practice to have the key register update from each persons record when their key number is entered using an append query to the key register when their record saves but then i’m Still missing out on unissued key numbers.

there is no particular gate security issue involved

does anyone have any thoughts or experience of similar situations?

[davegri]

What's your DB ralationship between the key register and the people table? Sounds like it should be many-to-many with no duplicate assignments allowed in the junction table.

[PDilly]

What's your DB ralationship between the key register and the people table? Sounds like it should be many-to-many with no duplicate assignments allowed in the junction table.

Hi davegri

In essence it has no relationship. When it started life, the key register was a way of telling whether someone had a particular key or whether it had been returned.

the relationship of people to keys is one key per person per year. There are historic records but aren’t really of value as these days the keys aren’t returned and when the year is up the locks change and new keys are issued.

It is possible to populate the key table with a complete set of key numbers and have the DB assign a number to a person. In reality, it’s much easier to physically pick a key when getting everything ready to post out rather than having to find a particular that’s already been assigned.

that’s my quandary. Either using a frequent table update that updates all the key records each time, but holds a record of all keys whether issued or not, or appending each key to the table each time a key is issued but having no record of unissued keys other than the physical stock.

The nub of the problem is that in the DB, people’s records are updated and keys issued randomly but in batches as they are received. Physically, the batches of requests are filed alphabetically, because that’s the best way to keep track of them.

its a bit of dilemma!

i had a brief thought. What would work, if it’s possible to do, is to have a drop down box which only ever shows the next available unused key number.

[davegri]

Here's a possible solution with visually friendly assignments. Select a key in the combo box. The left listbox shows all people not assigned to that key. The right listbox shows all people with that key assignment.
Currently it's possible for more than one person to have the same key, and it's possible for one person to have more than one key. That can be adjusted if need be.
Also, a report could be created to show all unassigned keys.

KEYS PDilly - davegri-v01.zip

[rpeare]

so what is your table structure?

I would think you would have something like

tblKey
K_ID  K_Number
1     200
2     199
3     198
...

tblPerson
P_ID  P_FirstName  P_LastName ----> other person related fields
1     Mickey       Mouse
2     Donald       Duck

tblPersonKey
PK_ID  P_ID  K_ID  PK_StartDate  PK_EndDate ----> other person/key related fields
1      1     3     1/1/2019      12/31/2019
2      1     1     1/1/2019      12/31/2019
3      2     2     1/1/2019      12/31/2019

If you had this kind of setup it should be fairly trivial to pull up a list of keys and whether or not they're in use for any given year.

[PDilly]

Here's a possible solution with visually friendly assignments. Select a key in the combo box. The left listbox shows all people not assigned to that key. The right listbox shows all people with that key assignment.
Currently it's possible for more than one person to have the same key, and it's possible for one person to have more than one key. That can be adjusted if need be.
Also, a report could be created to show all unassigned keys.

KEYS PDilly - davegri-v01.zip

Its not physically possible for one person to have more than one key (unless they lose one!) they are issued only by one person who posts them to people.

having said that, from the DB point of view it is possible to get the number wrong.

where things can fall down is when running the update query it is for all records in the current year where key numbers have been entered in an individual’s record. It is not automatic and it doesn’t look for errors. That said key numbers cannot be duplicated but key numbers that don’t exist are ignored.

my inclination from experience, is that the process is dependant upon the physical process of picking a key from a ring of 200+ keys and putting it in a wallet ready to post. During that process, the number can be entered into the individual’s record.

on balance, what I’m seeking to do, is to do away with the manual global update to the key table and replace it with a key number specific, table update, triggered by saving the record in view in my form. It can then easily check that the key number entered actually exists and hasn’t been assigned to someone else.
The update query only needs to trigger if a key number has been entered. Sometimes a part record is created in preparation for completion later.

Picking a key from a drop down and then trying to find the actual key sometimes doesn’t work too well. Keys can be misfiled or numbers missed from the sequence during manufacture!

Current table structure is:
personal details PK ——-first name, last name, other details ——key no., year.
key details PK. Key no., first name, last name, year. If a key is unused only the key number is present.

i want to keep the key table simple. It’s just a quick visible record without the need for query’s etc.

[PDilly]

so what is your table structure?

I would think you would have something like

tblKey
K_ID  K_Number
1     200
2     199
3     198
...

tblPerson
P_ID  P_FirstName  P_LastName ----> other person related fields
1     Mickey       Mouse
2     Donald       Duck

tblPersonKey
PK_ID  P_ID  K_ID  PK_StartDate  PK_EndDate ----> other person/key related fields
1      1     3     1/1/2019      12/31/2019
2      1     1     1/1/2019      12/31/2019
3      2     2     1/1/2019      12/31/2019

If you had this kind of setup it should be fairly trivial to pull up a list of keys and whether or not they're in use for any given year.

hi rpeare
please see my reply to davegri above. It pretty much answers your questions

the key table has a ‘keys in stock’ query set up and of course just sorting the table by year or name gives all the info needed.

as you’ll see, what I want to do is ‘automate’ the process of adding names and year to the key table so that it’s no longer a manual global update, but single key specific.

[accesstos]

From the DB point of view, it's completely wrong to keep customers information in table of keys and vice versa.
My opinion is that you need a structure like this:
A table for the keys (only if you want to keep more informations for the keys than the key number)
A table for the persons, with fields only for the person's info.
A table for the key posting with the KeyID, PersonID, Year and other fields with all necesary info.
You can use those three basic fields as multi-column primary key.
Enforcing Referential Integrity for the relationships can avoid the loss or inadvertent updating of your data.

Pulling data using queries is always a very good idea.

[rpeare]

Accesstos is suggesting to you the same thing I did.

If you work your data entry correctly you can have it check to see if anyone has been assigned a key number before you type it, or just bring up a list of 'available' keys for the year as you're doing data entry. This isn't a hard problem but as Accestos pointed out keeping client information on your key table and key information on your client table is a recipe for disaster and confusion.

[accesstos]

Accesstos is suggesting to you the same thing I did.

Indeed, I had to point it out.
It takes another three to make a team.

[PDilly]

Indeed, I had to point it out.
It takes another three to make a team.

Thank you for your input. Yes of course, you’re all absolutely right. This was set up about 14 years ago when a locked gate was hurriedly put up. The key table and handling the return of keys was a rush job.

having put more thought into it I think I don’t need a relational key table at all as such. A single key number is assigned to a single person annually. Key numbers aren’t repeated. (Well maybe over a ten year period)

As far as I’m concerned, the key number is part of the person’s individual record. It relates only to that person. If the key is lost, it’s replaced with a new number and at the end of the year, keys are replaced and the information becomes irrelevant.

I believe the right way forward is to have a key number table simply to populate a drop down list on my form.
the table could have a checkbox against each number so that at the next use of the drop down any ‘checked’ numbers are ignored, don’t appear in the list and the next available number bubbles to the top of the list. I can also retain an ability to choose any available number if a physical key is misfiled out of sequence and not readily to hand.

as far as keys in stock are concerned they can be identified as they are ‘unchecked’ and integrity will be maintained as no number can be allocated twice. I can set that field to ‘unique’

my question is, can what I propose be achieved, and does it seem an acceptable approach?

[rpeare]

PDilly.zip

Attached is a simple data entry mechanism with some low level validation checks

[PDilly]

PDilly.zip

Attached is a simple data entry mechanism with some low level validation checks

Hi Rpeare,

many thanks for taking the time and trouble to send me the sample DB it contains a great insight that will be really useful.

I’ve reached the conclusion that I no longer need a key table as such other than to hold a list of numbers to populate a list box on my form with available numbers. I will store a key number with each individual’s record. My DB already does that.

there was a need for a key table when keys were returned at the end of each year as they were either in use or in stock. Not all keys got returned so we had to keep track.

now that they are single year use, I don’t need to monitor them, just issue the right key to the right person!

you’ll probably see in my earlier post, i’m Looking to select a key in my form and mark it as used in the key table so that the list box doesn’t display it next time. I’ve done a couple of brief simple tests and in principle it works.

I think I’m taking the right approach and should end up with data integrity.

again, many thanks

[accesstos]

Hi guys!

PDilly, in my sample database, the validation of user input is based in Query-Form level. The point is to prevent the user to save invalid data.
Better forecast than treatment.
I also think it covers the issue of returns with more canonical way.

Explore it! I hope it helps!
John

[rpeare]

You will still need a key table if you want to determine where your unassigned keys are. If you have nothing to compare to MS access won't know how many are missing or which numbers are not accounted for. Also, what happens if you end up adding another 100 keys (even if it may never happen) every query you build based on having 100 keys would then be invalid where in a system you have your keys stored in a key table all you'd have to do is add the key numbers and everything should continue as normal.

I'm also curious because I'm looking at Accesstos's database. I built mine with the assumption this was some sort of storage facility where a person could possibly have more than one key. Accesstos has made the assumption it's going to be one key per person per year. Just be aware of those types of subtleties as you compare the two examples. You have an example using both bound and unbound forms as well you'll note we both use a key table.