Help needed with change to SQL code in module

[PDilly]

Hi

I’m not sure if I’m posting in the right place but:

a couple of years ago, Accestos kindly wrote a module for me.

In a nutshell it compares two tables and assigns a numbered key to a lock to a member who does not have one.

For the last two years it has worked fine but I think the coding does not take account of keys that were not returned and are no longer available but for audit purposes remain in the table as used but in previous years.

I need a piece of code which takes advantage of the ‘used’ field which Accestos did not use in his original coding in favour of ‘good housekeeping’ and he was probably right!

When run, the code issues every number in the table but takes no account of the used flag

Below is a link to his code in my original post

I’d be really grateful if someone could help. I understand queries but SQL is way out of my league!

I’m looking to insert something like “WHERE used Is True” in New_key_register



Much appreciated
Pete

https://www.accessforums.net/showthr...488#post473488

[June7]

Is database intended to be multi-year with history of key issue retained?

If a key is lost its KeyNo is never reused? So need a field to flag "Lost"?

[PDilly]

Is database intended to be multi-year with history of key issue retained?

If a key is lost its KeyNo is never reused? So need a field to flag "Lost"?

Hi June7

The table is multi year and we started with 2 sets of 250 keys whilst generally needing under 200 per year
The 2 sets rotate and are re-cut at the end of each year
As time passes we will need to replace keys and reuse numbers.
I’ll keep the lost info until I need to re-use the number and then commit it to hardcopy if necessary.
There’s No need for a lost flag thanks, just used.

The original code didn’t handle 2 series but it was added further down in my original post using a field that shows series 1 or 2. It won’t affect what’s needed to to cure the used problem though

regards
PDilly

[June7]

I am struggling to follow your business model. Is db from other thread representing your system? If not, could you provide db? Would help if db had multi-year data since you say it is multi-year. As is, I don't see how this db retains history. Why is there is no Members table?

To me, there is a difference between USED and LOST. USED can be returned and reissued, LOST cannot (until you actually reassign the number to a new key). I would expect procedures need a way to make that distinction - to not include LOST key numbers in BULK RETURN or BULK ISSUE.

By re-cutting a key, do you actually mean a new key blank is cut and assigned number? Are these padlocks or door locks?

Since there is no key 0 suggest you not have key 5000, start each group sequence with 1 (5001, 6001, etc.)

[PDilly]

Hi June7

Our business model is a set of fishing lakes owned by a registered U.K. charity, of which I'm one of the Trustees. Our sole income is derived from the sale of annual permits to fishermen.
For security, the site is locked and only current permit holders have access by key to a padlock.

That db represents the relevant part. Member details are stored in one table and membership years in another on a one to many relationship. Variations of PERMNO (Member ID) links relevant info across tables.
Over the years weve tried various schemes for returning keys such as against a deposit, low cost but less secure keys that are non-returnable and so on. We charge a hefty price for the current keys (they are expensive) but our costs are covered in membership fees. The keys that are returned are re-cut and re-used every two years. We will replace any that are not returned as needed using the same number. Any that are not returned will be unusable by the holder because the padlock has been changed. A condition of further membership is the return of the key and elswhere it's flagged up from members' annual record.

When a key is returned it is removed from the annual membership record and flagged as used=False in the key register. If a key is not returned it remains 'used' in the register and on the member's record annual record.
Knowing the year a key was issued and to whom tells us everything we need to know.
We are genuinely not concerned with differentiating between lost and used.
The two series of keys are currently1-249 and 500-699 (series 1 & 2). the keys can be re-cut for at least 15 years.

You will probably have seen in the old post, that bulk issue works fine once but takes no account of the used flag in subsequent uses
It's one of those tasks where there will be a lot of people joining at the start, then a gap, then stragglers. The easiest way to issue membership is by doing as many as possible at one time. Being able able to do more than one bulk issue would be a real time saver.

If you think it's not possible to utilise the 'used' field in the module SQL, please let me know and I'll try and find another way.

If you feel you need a cut down copy of the DB, please let me know.

Kind regards
PDilly

[June7]

As I noted, I think distinction between USED and LOST is relevant in a batch issue and batch return process. Can certainly use a field to track USED status instead of calculating it but consider also indicating LOST status. One field could serve.

I would like to work with your db structure to be able to help more.

My curiosity is piqued by the "re-cut and re-used" description. I have never heard of a key being "re-cut".

[orange]

Could you please describe to readers what re-cutting a key means and what is involved? The term is not familiar.

[PDilly]

Could you please describe to readers what re-cutting a key means and what is involved? The term is not familiar.

Keys of any type whether for use in Yale, mortice locks or padlocks usually come with keys that fit.
In a normal course of life you might take a key to a shop to have a duplicate made from a key blank.

Most good locksmiths will carry standard key blanks.

The keys we use are from a very secure range which only our locksmith has the codes for. They cannot be duplicated anywhere else.
the locks we purchased were supplied with a cutting code and no keys. The locksmith cut all the key blanks to match.

Two years on, the lock cylinders were changed to a new code and the key profiles were altered or re-cut to suit

In simplistic terms, a bit more was shaved off each key though it’s a little more hi-tech than that.

when you look at the profile of a key, it’s a bit like a mountain range. Each peak and trough corresponds to a matching pin in a lock cylinder
these can altered about 25 times before the key reaches the end of it’s life. The peaks can only be made smaller and the troughs deeper and of course it isn’t necessary to alter more than two or three each time

Hope that’s a good explanation!

[PDilly]

Hi June7

At one time, keys being returned by post did frequently get lost. The Post Office sorting machines were good at ripping them out of envelopes.
We gave that up and provide a different system now. I think we’ve actually lost one key in five years. That’s why we aren’t concerned.

You’ll see that Orange (moderator) asked for an explanation for re-cutting which I’ve done. It’s not something that happens in the domestic market. In industry, secure locations do it but office environments usually change locks and keys. We did do that by rotating 7 or 8 different sets of keys and locks but maintaining that was quite a task. Two sets are much simpler!

I’ll put a zip file up with the current db in it with some fictitious data in it for you to look at as soon as I can
You’ll have to be a little forgiving it has suffered from evolution but it works!

Kind regards
PDilly

[PDilly]

Hi June7

I'm struggling to get my DB below 2Mb zip file limit
Will try and reduce further tomorrow
regards
PDilly

[orange]

Keys of any type whether for use in Yale, mortice locks or padlocks usually come with keys that fit.
In a normal course of life you might take a key to a shop to have a duplicate made from a key blank.

Most good locksmiths will carry standard key blanks.

The keys we use are from a very secure range which only our locksmith has the codes for. They cannot be duplicated anywhere else.
the locks we purchased were supplied with a cutting code and no keys. The locksmith cut all the key blanks to match.

Two years on, the lock cylinders were changed to a new code and the key profiles were altered or re-cut to suit

In simplistic terms, a bit more was shaved off each key though it’s a little more hi-tech than that.

when you look at the profile of a key, it’s a bit like a mountain range. Each peak and trough corresponds to a matching pin in a lock cylinder
these can altered about 25 times before the key reaches the end of it’s life. The peaks can only be made smaller and the troughs deeper and of course it isn’t necessary to alter more than two or three each time

Hope that’s a good explanation!

Thanks for the info.

[Micron]

If it contains a lot of embedded images or attachments in attachment fields you might have to remove them from your copy.

[PDilly]

If it contains a lot of embedded images or attachments in attachment fields you might have to remove them from your copy.

Hi Micron
I’ve whittled the records down to a minimum but there are some images. I’ll delete them and see what else I can get rid of
regards
PDilly

[PDilly]

I've whittled down my DB to a manageable size. There are sufficient working parts for key issue
When run, bulk issue will allow keys that are used to be used. If a member is added, when bulk issue runs again it will issue the first key in the list which has already been issued

Regards
PDilly

Sample Fishery DB.zip

[PDilly]

Hi Micron
I’ve whittled the records down to a minimum but there are some images. I’ll delete them and see what else I can get rid of
regards
PDilly

Hi Micron,
I’ve removed the images and that was enough to be able to upload direct to the thread
regards
PDilly