If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
File Check Out System
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 |
#2
|
|||
|
|||
"Emily" wrote in news:308401c4a4c6
: 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 |
#3
|
|||
|
|||
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 -----Original Message----- "Emily" wrote in news:308401c4a4c6 : 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 . |
#4
|
|||
|
|||
wrote in news:319301c4a59f$32a7fe30
: 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. 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
URGENT: Mailing rule problems of filing in outlook express and hard copy file systems in real case | Teres | Outlook Express | 7 | August 14th, 2004 01:55 AM |
Can a formula check for existance of a file | BigWoof | Worksheet Functions | 6 | August 13th, 2004 01:16 AM |
Office 2003 uninstall/reinstall problems | Jerryn3 | Setup, Installing & Configuration | 2 | August 2nd, 2004 03:39 PM |
Application must be installed to run Error | Keith | Setup, Installing & Configuration | 1 | June 29th, 2004 03:02 AM |
Outlook 2000: "System cannot find the file specified" error | serenas-favorite | General Discussion | 0 | June 21st, 2004 09:12 PM |