Question Handling lost inventory

[simkessy]

Hello, Im designing a key management system.

Basically I have a key table and a keyActivity table. I need a way to manage lost keys. So what I did was add a yes/no attribute called lost_key. Now I don't know how to remove from my keys table the key which was lost. From my research there is no method to disable/lock a record so I assume I now need to move the specific lost key record to a new table which tracks lost keys.

I tried adding a macro to the lost_key attribute but the options seemed very limited or I didn't know how to do it.

Any suggestions on how to handle such a situation?

This is my access file: http://jumpshare.com/b/37M1aG
and my tables: http://jumpshare.com/b/J7SaJg

 

[Catalina]

There is no need to remove the record of a lost key.
You can prevent records of lost keys showing up in a form by adjusting the query the form is based on.
That way you also maintain a history.
If you decide to purge the records after a while you can run a delete query.

Catalina

 

[simkessy]

There is no need to remove the record of a lost key.
You can prevent records of lost keys showing up in a form by adjusting the query the form is based on.
That way you also maintain a history.
If you decide to purge the records after a while you can run a delete query.

Catalina

The problem is that in the keyActivities table when im selecting a key to signout, key's that have been marked as lost still show up. This is problem. I also need to do the same for keys that have not been returned yet.

This is an example of what I mean: http://jumpshare.com/v/wIDZjB?b=b7rJIn
Key 15 is shown in two records but the first record of key 15 indicates that this key has not been returned it. The key_id attritube is a lookup field to the keys table.

I need to be able to ensure that keys which have not been returned or have been lost do no showup in the dropdown when selecting a key to signout.

 

[jdraw]

Use a query as the source of the combo/dropdown. Use a boolean/Yes_No field for KeyIsLost, and for KeyReturned.

Select keyfields from Keys
where KeyIsLost = False and
KeyReturned =True

Do NOt show
Keys that have been Lost (KeyIsLost = True) or
Loaned Out and Not Returned (KeyReturned= False)

You need to do some research and experimenting with SELECT queries.

 

[simkessy]

Use a query as the source of the combo/dropdown. Use a boolean/Yes_No field for KeyIsLost, and for KeyReturned.

Select keyfields from Keys
where KeyIsLost = False and
KeyReturned =True

Do NOt show
Keys that have been Lost (KeyIsLost = True) or
Loaned Out and Not Returned (KeyReturned= False)

You need to do some research and experimenting with SELECT queries.

I tried this but got zero records:

SELECT *
FROM keys AS a left outer JOIN keyActivity AS b 
ON a.key_id = b.key_id
WHERE return_date is not null 
and lost_key =false
and id is null;