Disable a record from a table based on the attribute of another table

[simkessy]

I have a keys table and a keysctivity table. I need keys to not be available if they are currently signed out (return_date is null) or if they have been marked as lost (lost_key = true)

This is my activity table.
http://jumpshare.com/v/6ID9bi?b=D7NY0X
If for example signin_id 1 was not returned or was lost, key_id 1 should no longer be available. Is this something that can be done?

This is the access file I am working on: http://jumpshare.com/b/V7g9ko

A possible solution would even be to create two new tables "lostKeys" "unreturned_keys" and have records moved to their respective tables based on whether they are indicated to be lost or currently not returned. Im just not sure how to accomplish this or if it's a good solution.

 

[Pat Hartman]

A better solution would be to use a single table and let the signed_out and return_dates dictate availability.

 

[simkessy]

A better solution would be to use a single table and let the signed_out and return_dates dictate availability.

Im sorry, I didn't get that at all.

 

[simkessy]

A better solution would be to use a single table and let the signed_out and return_dates dictate availability.

Are you saying get rid of my key table? If not, how do I let the dates dictate availability. Letting dates or lostKey attribute dictate availability is the core of my question, I just don't know how.

I've tried writting this query:

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;

But it resulted in not records. I tried removing the id is null condition but this resulted in only keys that had previously been rented and ignored available keys in the keys table.

Once I get the query correct, i am hoping i'll be able to copy it into the key_id record source in order to be provided with keys actually available and not every key in the key table.

 

[Pat Hartman]

Notice the parentheses. These are usually required when your compound condition includes both AND and OR relational operators.

WHERE (return_date is not null 
and lost_key =false)
OR b.key_id is null

As long as the item is unique, you don't need the second table unless you have a need to keep history. And I would treat it as history and keep it out of the mainstream of processing. Keep the signout information in the main table. Always look at the main table when looking for keys. When the item is returned, copy all the information and create the history record. Then remove the signout data from the main table. That means that if the out date is filled, the key is not available. What you do with lost keys is a different matter. You may need to keep them in the main table or you may need to remove them. It depends on how long the "missing" information is relevant.