Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Tracking the availability of equipment! (Access 2002)

    I need to be guided in the right direction here with some sound advice! As this is a query from a client, I do not have a DB to attach. I can howevr, (if necessary) design a small sample if the Q is to dodgey!

    Imagine a many to many relationship between an Employee Table and an Equipment (Tools) Table. The Junction Table will list the Employee, the piece of Equipment and a field called Status. This Status field will capture a boolean YES or NO indicating that the piece of Equipment is OUT (signed out and being used) or IN (returned to the workshop).

    These pieces of Equipment will be signed out and returned many times by many different employees. My question is this: How would I be able to query which equipment has NOT been returned? As I see, the table will register a piece of equipment multiple times with OUT and IN occuring throughout. How can I set up a query to check for an OUT value without a matching IN value if the same piece of equipment is recorded with multiple OUT'S and IN'S???

    PS: I sound like <!profile=TonyE>TonyE<!/profile> with his cricket description (IN'S and OUT'S....) <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Hope I make sense!
    Regards,
    Rudi

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking the availability of equipment! (Access 2002)

    How about using date fields instead of yes/so fields to record date checked out and date checked in. You could then look for all records with a date checked out and no date checked in that is greater than the date checked out. Entering the date could be simplified by using a popup calendar attached to the doubleclick event for each field.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tracking the availability of equipment! (Access 2002)

    Judy...this is some wise advice. I will refer your advice to the client. I am pretty sure that this will work.
    I can see its potential is far greater than a simple YES / NO entry!
    Thank you very much.
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tracking the availability of equipment! (Access 2002)

    Do you want to keep a history of which employee uses which equipment? If so, Judy's recommendation is the way to go. By using CheckOut and CheckIn date fields, it's easy to keep track of which pieces of equipment are checked out at any given moment.

    If you're not interested in keeping historic records, you don't really need a many-to-many relationship. You could add an EmployeeID field to the equipment table. When a piece of equipment is given out to an employee, his/her ID is put in the field, and when it is returned, the field is cleared.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tracking the availability of equipment! (Access 2002)

    Thanx for the input Hans. I have not seen the DB that this client emailed me about! All I know is that it is supposed to track equipment and it isn't working well. I have a tentative meeting with him on Friday and he is bringing the DB in with him. I will keep in mind you suggestions regarding if the data must capture historically or not!

    Thanx again both of you!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •