Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Checkin/Out Database

Discussion in 'Information Technology' started by kiddjt, Jul 28, 2006.

  1. kiddjt

    kiddjt
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    USA NSA has massive database of Americans' phone calls (USA Today 5.11.2006) Breaking News Jun 11, 2013
    Some database errors while navigating SPN forums Announcements Dec 29, 2004

  3. Fred Boer

    Fred Boer
    Expand Collapse
    Guest

    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



    "kiddjt" <kiddjt@discussions.microsoft.com> wrote in message
    news:3A50431D-66D2-4A30-BCE8-1E83C5FB4D4A@microsoft.com...
    >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
     
  4. Fred Boer

    Fred Boer
    Expand Collapse
    Guest

    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


    "kiddjt" <kiddjt@discussions.microsoft.com> wrote in message
    news:3A50431D-66D2-4A30-BCE8-1E83C5FB4D4A@microsoft.com...
    >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
     

Share This Page