Checkin/Out Database

[Guest]

I have a few questions about making a checkin/out system:

Here's my basic design:
table:members
mid-pk
name
email
etc.

table:folders
fid-pk
description

table:checkouts
mid
fid
date out
date in

I also have a many-to-many relationship setup with these three tables.

1) I need a form where I can search for a member name(NOT mid) and then
search for available folders to checkout to them. I also need to be able to
add a new member at this point.

2) I need to do the reverse: take a folder and search for it, see who has
it checked out, not necesarily searching using the primary key.

3) I need a form to checkin the folder, by looking up a member name(ie,
member says, "here's my folder, check it in please).

4) I need a checkin/out history.

5) I need to record what member(same group of members as above) checked
something out.

6) I need to use history from a previous year to checkout a folder this
year.(ie, ah, you got folder A last year, here's folder A this year). With
this, I also need the option to give them a different folder.

I know this can be done, and I know it's out there somewhere, but I can't
seem to find it explained anywhere, and I'm having trouble tearing apart
templates that seem to do it, especially with item 1 above

 

[Fred Boer]

Dear kiddjt:

It sure sounds like you want something similar to a library program. You are
unlikely to find a template that will meet all of your requirements. It can
certainly be done in Access, but it might be worth your while to investigate
whether or not existing library software might do the job for you. There are
professional quality library programs available for very low cost ($50...),
or even freeware. Considering how much time this might take, you might be
better off using something pre-built.

A quick Google search reveals many dozens of vendors selling a wide variety
of library management software. For example, you might look at the
following:

http://directory.google.com/Top/Reference/Libraries/Library_and_Information_Science/Software/

Or, you can find shareware library systems as well, with a search at
www.tucows.com, or even google for "free library software", for example.
Creating a library system yourself, from scratch, could be a very time
consuming and costly business! You will probably find that a professionally
written commercial package would be much more cost effective. I am a
teacher-librarian for a small school, and I have created a library system
for my school in Microsoft Access. There were some valid reasons that I did
this, but I could never claim (at least not with a straight face! <g>) that
this was the most cost effective or efficient solution!

HTH
Fred Boer

 

[Fred Boer]

Dear Kiddjt:

Whoops! That'll teach me to post after my bedtime - I gave you my "standard"
make a library program in Access answer, forgetting that I've already
communicated with you in an earlier post. Your general table structure is
reasonable, although some suggestions might be offered. You may want to post
your proposed final table structure for comments in the "tablesdbdesign"
newsgroup. For example, most people would recommend against a single field
for a person's name (better to use FirstName, MidName, LastName), and you
don't want to use "Name" as the actual name of a field - it is a reserved
word in Access and would cause problems. Also, you want to avoid spaces in
field names... (You might want to look here:
http://www.mvps.org/access/tencommandments.htm )

Looking at your points:

1. Yes, you would need a form to do the actual checking in and out of
materials. That form would use the "checkouts" table you mention as its
recordsource. To "checkout" a book, you would create/add a new record, and
enter the memberID data and folderID data. The DateOut field could be filled
by having the default value of the field set to Date(). (Or better yet, set
it to Now(), since you might have a folder circulate more than once in a
single day.) You would probably want to have a separate form to manage the
membership data - if the member doesn't have an ID, you could bring up the
membership form and add them. To check in a book, you locate the record in
the "checkout" table, and set the DateIn to Now()...

2. The transaction table ("checkouts table") would be able to provide you
with the information you need to see if a folder is in circulation. You
would, for example, have a textbox in which you entered the folderID, and
then run a query to check if it is available.

3. Again, you would do this by creating a form which accepts the folderID,
and then updates the transaction table, finding the record of circulation,
and setting the "date in" to be Now()

4. Check-in/out history can be derived from the data in the transaction
table.

5. See 4...

6. Sorry, I'm not sure what this means....

Finally, you say " can't seem to find it explained anywhere, and I'm having
trouble tearing apart templates that seem to do it"... You *may* be
underestimating the amount of work it might be to create the system you
want - particularly if you aren't an experienced Access developer. It *can*
be done, but it might take a lot more time and effort than you'd like.

Cheers!
Fred Boer