Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005

    Unanswered: Booking database - count number of available resources?

    Hi there

    I have a booking database consisting of several tables. Each resource (resourceid) on my database can be booked by a user (userid). There can me more than one copy of each resource so I have a field called "numberofcopies" related to that resource.

    When I come to place a booking, I have a query which takes the details of the user and the selected resource to create that particular booking. Each booking which is created has its own Autonumber ID.

    My question is - How can I get the number of copies of each resource to diminish everytime a copy is booked out? Eg - 5 copies of a dvd, a booking is placed for one copy - leaving for copies free to book.

    Also, when they are returned, I need the amount to go back up again!

    Any ideas?

    Many thanks!!

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    one idea - don't!

    you have the total number of copies in your table
    your bookings table knows how many copies are out

    subtract the two and you have the number left in stock

    crude example (yes - i know everyone hates domain aggregate functions, but it's something to start with!)

    dim thisID as long
    thisID = .....find some way to grab the resourceid you are trying to book

    if (dlookup("numberofcopies", "tblresource", "resourceid = " & thisID) - dcount("resourceid", "tblbookings", "((resourceid = " & thisID & ") AND (isReturned = False")))) > 0 then
    msgbox "OK"
    msgbox "None available"

    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2005
    Ok thanks for that Izy, I will give that a shot and let you know!!

Posting Permissions

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