Results 1 to 9 of 9

Thread: Unique Records

  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique Records

    Hi folks, I have to admit that I am far from being a whiz with Access but have a database that I use to manage my business that is going well (the database that is!). I have a situation that I don't know how to resolve though and am sure someone does. BTW using Access 2000, with no s/ps.
    My problem is a table that has the following fields amongst others - Date, Start Time, Finish Time, Workstation. I have 7 workstations numbered 1 - 7. I am trying to set this up to avoid double bookings at the time of entry. When the time of bookings are the same eg 2 bookings which start 9am finish 11am, I get told I have a duplication which I can avoid by changing the workstation number until I run out of workstations. However if someone books 8:30 to 10:30 the system doesn't recognise that it could be a double up. Does anyone know how I can have Access realise that overlaps in time are double bookings. There is already a primary key on the bookingid field which ties in with the customer details.
    Hopefully this is sufficient info for an answer but let me know if you need more explanation. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Records

    You can't do this from a table. You have to build the logic into your form and test the validity of the record there *before* you try to save it. And, BTW, apply at least SR1a to Access 2000. It cures some inconspicuous problems that bite when you least expect it.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records-MULTI-Re: Unique Records

    Thanks for the quick reply Charlotte. Can you tell me how to go about it at form level?? Is it code? or filters?????Any help with this gratefully accepted. <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Records-MULTI-Re: Unique Records

    Well, I'm not sure what the workstations have to do with it. If you only want to book a time window once, what difference does it make which workstation you booked it on? And if you can book the same time from each workstation then what makes the booking unique? You didn't explain what the booking is for, so it makes it hard to guess at how to make it work.

    In general, you put code into the BeforeUpdate event of the form to open a recordset based on a query that will return any records that match the criteria you specify, which could be a booking that starts less than the end time and >=the start time specified OR that starts less than the end time but ends >=the end time OR that starts less than the start time but ends > the start time, etc. If the recordset returns any records (in other words, if EOF is false) then an record already exists for that time window. In that case, you would set the BeforeUpdate's Cancel argument to true and notify the user that a booking conflict exists. That keeps the record from being saved and leave the user where they can change the booking to another time.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records-MULTI-Re: Unique Records

    Ok sorry Charlotte, I was trying to keep my explanation as brief as possible but I guess brevity does not equal clarity! I run a computer training business - I must do my Access course when I get a moment! The training is done on screen not in a classroom, using the great material prepared by LearnKey, so when a customer wants to make a booking I need to book them a workstation (read computer). If machine 1 is busy at that time try 2 or 3 etc. Customers come in at varying times and stay for varying lengths of time so ensuring that a workstation will be free at a certain time is crucial. When the schedule is busy a visual check is not always sufficient.

    Hopefully this clarifies the situation enough to give me an answer. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records-MULTI-Re: Unique Records

    I would have thought this was a relatively easyish task to solve for all of you experts. Am I wrong???? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records-MULTI-Re: Unique Records

    Dear Mike,
    I understand from reading your posts that what you mean is if there

  8. #8
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unique Records-MULTI-Re: Unique Records

    If you've got the start and end times of the existing bookings you can elaborate on this by doing something like:

    SELECT BookingDate FROM tblBooking WHERE BookingStart<[ThisBookingEnd] AND BookingEnd>[ThisBookingStart] GROUP BY BookingDate HAVING BookingDate =[ThisBookingDate] AND Count(BookingDate) >=[MaxBookings]

    [ThisBookingDate] is whichever date you're trying to book on and [ThisBookingStart] and [ThisBookingEnd] are the start and end ranges of the period you're trying to book. MaxBookings is the maximum number of bookings allowed at any given time. You can get these values into the query a number of ways.

    If the query returns a record, you can't book a session, otherwise there are vacant PCs and you can.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records-MULTI-Re: Unique Records

    Rupert and Simon, thanks for the info. I haven't had much of a chance to work on this in the past few days but will see what I can do with your suggestions. Thanks again. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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