Re^2: Searching Database Question

Dave,

Thanks so much. Your layout is much better than mine. I'm doing it in one database.

ID - database ID<br>
key_number - unique number for each key<br>
no_on_hand - number of keys we're holding.<br>
action - check in, check out, or register (inventory record)<br>
dateadded<br>
user<br>
[download]

I search and sort based mostly on the action field. For example, to get an inventory of keys I search for all fields where action=register. That shows me what we're supposed to have.

The big problem is I've already allowed them to have more than one key on hand. It would be much simpler if everyone could only have one key. That way, if the number of keys is < 0 or >1 that would mean there is a problem.

I have written error routines to catch and future entries where a check out does not have a corresponding check in before the next check out, but I'm trying to figure out a way to create a new audit routine where it would show me all keys < 0 or >2, OR keys that have two check ins in a row or two check outs in a row.

I suppose there is no way to do that.

Thanks.

Comment on Re^2: Searching Database Question Download Code

Replies are listed 'Best First'.
Re^3: Searching Database Question by davido (Cardinal) on Oct 14, 2018 at 00:29 UTC
It's really not a problem. Your application would just need to keep track of who has which keys, and with this approach it already does. When a user attempts to check-in a key, you validate against the user_id, so 'select id from key where user_id=? and opens=? and checked_out=1', and then let them check-in the first row that returns from that query. If no rows appear, they cannot check a key in, because it either means they already checked it in, or never had one checked out to begin with. Dave	[reply]

Replies are listed 'Best First'.

Re^3: Searching Database Question
by davido (Cardinal) on Oct 14, 2018 at 00:29 UTC

It's really not a problem. Your application would just need to keep track of who has which keys, and with this approach it already does. When a user attempts to check-in a key, you validate against the user_id, so 'select id from key where user_id=? and opens=? and checked_out=1', and then let them check-in the first row that returns from that query. If no rows appear, they cannot check a key in, because it either means they already checked it in, or never had one checked out to begin with.

Dave

[reply]