Re-thinking use of table for issuing keys to gate

[PDilly]

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.

Hi, rpeare & Accesstos

thank you so much for the amount of work that you've put in to help me solve my problem.

Both your DB's contained really useful information. they've given me a lot of insight of how to achieve my aim.



It's actually a locked gate to a fishing lake owned by a charity and it is only one key per person per year and then they are thrown away, locks changed and new keys issued. we used to re-use keys over a 5 year cycle but the amount of admin and number of keys that got returned has made it a futile exercise.

there are instances where a key gets lost and is replaced or someone dies and a key is returned but they aren't worth worrying about.

When keys were being returned there was a point to historic key records but my thoughts are that other than the previous year perhaps, they have no value. the keys effectively no longer exist.

Hence my inclination to go for simplicity and have a key number list (table) which can be altered every year and issue available keys from a dropdown on my form. The key number can held in a field the person's annual record as it is now

The key table needs only a PK, key number and a field to indicate that the key is issued or in stock. If it's not in stock, the form dropdown won't show it. When the year ends the list will be repopulated with new numbers and new records created. the keys in stock will be disposed of and records of who had which will remain in last year's people data.

My available keys can be queried from the key list table and those in use queried from the people records

I hope that gives a clearer insight

[accesstos]

Hi, rpeare & Accesstos
[...]
It's actually a locked gate to a fishing lake owned by a charity and it is only one key per person per year and then they are thrown away, locks changed and new keys issued. we used to re-use keys over a 5 year cycle but the amount of admin and number of keys that got returned has made it a futile exercise.
[...]
The key table needs only a PK, key number and a field to indicate that the key is issued or in stock. If it's not in stock, the form dropdown won't show it. When the year ends the list will be repopulated with new numbers and new records created. the keys in stock will be disposed of and records of who had which will remain in last year's people data.

My available keys can be queried from the key list table and those in use queried from the people records

I hope that gives a clearer insight

Well, after your detailed describe, that who I have to say is that, if you only need a table with client details and a table for the annual records for each client, you don't need an Access application to keep those informations. You can do it with Excel. A workbook with two worksheets, one with the client details and an other for the annual record for each client, is only that you need.
If all keys opens a unique lock and each year the lock and the keys change, there is no reason to keep any info for the keys. That will be just a number in the annual records, just for the record.
With some simple formulas, validation rules, conditional formatting and the powerful AutoFilter of Excel you can create a simple, rubost and usefull workbook. In the future, if you will need to jump to Access, you will have those worksheets as datasource for your Access project.

Whatever you decide, I'm glad to help you.

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.

rpeare, just for the record, we can give this possibility of choice to the user, adding a checkbox on the form frmAssign and multiply it's value with the value of the cboYear in qryYearPersons as follows:

SELECT qlkpAssigned.assYear, qlkpAssigned.perIDfk
FROM qlkpAssigned
WHERE (((qlkpAssigned.assYear)=
[forms]![frmAssign]![cboYear]*abs([forms]![frmAssign]![chkOnePerYear])));

As we can see, at this high level, customizations like this can be made easily without any change in the code, even by the user himself.

Cheers,
John

P.S.:
After a closer look, I noticed that the availability of keys should not depend on the year. It should be the result of the keys not being used plus those that have been returned.