2 Replies Latest reply on Oct 8, 2009 4:45 PM by Brucey

    Tracking places on a course

    Brucey

      Title

      Tracking places on a course

      Post

      I have set up a relational database for keeping track of the number of places available on a course eg Word processing, spreadsheets, webpage design etc. There are 20 places available on each course.  The tables I have created are Customer_tbl, Course_tbl and Booking_tbl.

       

      1. When a booking is made I need to have confirmation in a dialogue box that a place is available and when "OK" is pressed the number of places available is reduced by 1. Perhaps even telling me how many places are left.

       

      2. I also need to know if a customer has already booked on a particular course and if so a dialogue box appearing telling me this. eg "This customer is already booked on this course"  

       

       Any help much appreciated. 

       

       

       

        • 1. Re: Tracking places on a course
          philmodjunk
            

          1. When a booking is made I need to have confirmation in a dialogue box that a place is available and when "OK" is pressed the number of places available is reduced by 1. Perhaps even telling me how many places are left.

          First, define a field in Course_tbl called Size and store 20 in it. (If you store the course size this way, you can easily change it if future course bookings require a larger or smaller size.)

           

          You should have the following relationships:

          Customer_tbl::CustID = Booking_tbl::CustID

          Course_tbl::CourseID = Booking_tbl::CourseID

           

          Now you can create a calculation field in Course_tbl, TotalBooked. Define it as Count(Booking_tbl::CourseID)

           

          Each time you book a customer for a course by adding a record in Booking_tbl, the TotalBooked will increment by one and the space available, Size - TotalBooked, will decrease by one.

           

          With all that in place, the following expression will tell the user if there is room to book additional customers:

          Course_tbl::TotalBooked < Course_tbl::size

          You can use this in an if or case function in a calculation field to display text such as "Space Available" or in the If step of a script set to check on availability. It depends on how you choose to design your interface.

           

          2. I also need to know if a customer has already booked on a particular course and if so a dialogue box appearing telling me this. eg "This customer is already booked on this course"  

          A simple way to do this is by setting up a validation rule. Define a text field in Booking_tbl to auto-enter the following text: CourseID & " " & CustID. Set the Unique value option on this field and enter your validation message "This customer is already booked on this course".

          • 2. Re: Tracking places on a course
            Brucey
               Thanks a lot.  Works fine now.