Design problems with a file checkout database

[OhBother30]

I am working on a database that is similar to a Library book check out. It involves files from my office that can be checked out by various employees. They want to keep track when a file is checked out and returned. They also want to keep a history of the activity for each file.

I am having a little trouble with the design. I want to limit the checkout form to only files that are not already checked out and similar with check in form. I wondered if I should separate the records of the files that had been returned in a different table from the files that are still checked out. I thought this might eliminate some of my problems. I'm not sure how to write the code that would move the record from one table to another when the checkin date was entered.

I am importing a customer file from our AS400 for the main data. One problem is that we may or may not have a file received for that customer. I am using a DateRec in my Customer table to identify that the file was ever received in inventory. So, that is another condition for my Checkout form (eg. can't check it out if we never had it).

I have another table of the individual checkout records keyed by the customerID. This table contains the name of the person checking out the file and other dates and info. I'm having trouble dealing with the multiple records for each CustID in this table as far as populating my form for checkin. I'm using a checkout ID so I though I could somehow find the MAX ID for each cust where the CheckInDate was blank.

OK.... I am afraid none of this is making sense. If anyone has any suggestions I would be thrilled to hear them. Also, if you know anywhere I can view a sample of a similar database... that would be great too.

Thanks a bunch.
Andrea

http://www.ohbotherpooh.com

 

[Junior1544]

well, this seems like a little bit of a complicated mess...

(It's ok, I was exacly where you are now about 5 months ago...)

Here is a untested sample of how i would check to see if a file is already checked out...



if dcount("index_of_Table", "table_name", "[File_Number_Field] = Forms!FormName![Document_Number]&quot > 0 then

msgbox "This file is already Signed out."

else
'put the code you would have to sign this document out...
end if

just an idea off the top of my head, and is very untested...

--James

junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JeremyNYC]

OB3,

Actually, you want to avoid any scheme that involves inserting and deleting records like the plague. For one thing, Access files bloat horribly when you do things like that. For another, you immediately violate rules of "data normalization". (Do a search for that phrase on the web, and look for some short summaries of it. It's critical to your design of databases).

What you want to do is have tblFile store the information about the files and tblHistory store the information of files being checked in and out.

Jeremy

tblFile
-------
FileID (Autonumber)
FileName
Filepath
Whatever other information you need to store about the file itself


tblHistory
----------
FileID
CheckOutDate
CheckInDate
PersonID

That should be it for tblHistory. You'll, of course, also need a tblPerson to store information about who's checking out the files. =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.