File Check Out System

[Emily]

Hello,
I am trying to devise a system for checking out files and
I have four tables 1)Files in file room 2)People who check
out files 3)People who deliver files 4)File Check Out
Table (This table include barcode (Lookup frm Table 1)
delivered to (Lookup from Table 2) delivered by (Lookup
from Table 3) check out date, check in date and another
field named "status".

The status field is a yes/no that records whether the file
is checked in or out. I want to link this field to a
field of the same name in Table 1 (Files in file room).
This way, whenever I check out a file in the check out
table, the file will also change to "checked out" in Table
1 (files in file room).

I tried making a look up field titled "status" in the file
room table, but when I would change the status info in the
check out table the information would not change in the
file room table. How can I solve this? Or is there a
better way to design a file check out database for this
purpose.

Thank you very much for you time and effort,
Emily

 

[Tim Ferguson]

I tried making a look up field titled "status" in the file
room table, but when I would change the status info in the
check out table the information would not change in the
file room table. How can I solve this? Or is there a
better way to design a file check out database for this
purpose.

Probably yes, but you are not far off. From here it looks like you have
three entities:

Files(...)

People(...)

Movements(
*FileID FK ref Files,
*DateOut
CheckedTo FK ref People
DeliveredBy FK ref People
DateBack
)

The magic field is Movements.DateBack, which will be NULL for files that
are still checked out, and NOT NULL for files which have been returned.
You don't need a status field at all in the Files table, because it is
always readable in the Movements table. Note too that there is a one-to-
many relationship between Files and Movements because a file can be
checked out more than once in its history. Unfortunately Access cannot
enforce the rule that says a file which is still checked out cannot be
checked out again until it's returned -- you either need a real DBMS to
do that, or rely on code behind the form.

Hope that helps


Tim F

 

[Guest]

For my DateBack field, what would be "null" or "not null"
to make the check out work. Sorry, I just taught myself
this program in the last 3 weeks and I have much to learn!!

Thanks for your help,
Emily

 

[Tim Ferguson]

For my DateBack field, what would be "null" or "not null"
to make the check out work. Sorry, I just taught myself
this program in the last 3 weeks and I have much to learn!!

Yes, that's true -- which makes the first attempt even more impressive.


A field starts off NULL, in other words empty, until someone puts some
data in it. The meaning of NULL is taken to be "not available", "not
applicable", "missing", etc -- although entire websites have been
dedicated to the subject. In this case, if there is no DateBack date
(i.e. DateBack IS NULL) then the file is still checked out; once the date
has been filled in (i.e. DateBack IS NOT NULL) then you know it's back in
the library. To get the file status, therefore, you just have to look at
the most recent Movements record and check whether there is a DateBack
value.

One of the underlying themes of R theory (and normalisation, etc., etc.)
is never to store something you already know. If you know the cost and
the tax rate, then it's a bad idea to store the net price. In this case,
you know whether the file has moved back in or not, so you don't want to
store it as a separate fact. What would you infer when a File.Status
field disagrees with the Movements table?- if you see what I mean...

All the best


Tim F