Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Prevent duplicates over 2 criteria (Access 2003)

    Hi,

    I was asked this question from a client. They have a delegates/courses database that they are designing but ran into this scenarion. They want to prevent a delegate from attending the same course as well as prevent a delegate from being scheduled for a course on the same date.

    They have set up the database containing a delegates table, a courses table and a junction table linking the two with referential joins (many to many using the P/K's (DelID and CourseID) from the other two tables). Now this solves the problem so no person can be booked on the same course twice, but how do we prevent the delegate from being booked twice on the same course date?

    Any ideas would be appreciated.
    TX
    Regards,
    Rudi

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent duplicates over 2 criteria (Access 200

    Rudi

    I think if you add the course date to the junction table and add that to the PK this should solve the problem.

    Nick

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

    Re: Prevent duplicates over 2 criteria (Access 200

    Nick,

    Tx for the speedy response. Your answer made me realise that I phrased my question incorrectly. If the course was only 1 day in duration this would be the solution, but the listed courses have different durations. Some are one day, others are two and longer. So if a person is scheduled to do "Course X" ( say a three day course) on 1 Feb 2008, then it must prevent an entry for the same person booked on "Course Y" on 3 Feb 2008. This will be a double booking!

    Sorry for the confusion.
    Any ideas.
    Regards,
    Rudi

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent duplicates over 2 criteria (Access 200

    Rudi

    OK. In the case of courses running over more than one day I think the solution would be a new table DelegateBookedDate - this would contain one row for each day of each course for each delegate (columns would be DelID + CourseDay). This would be populated (using an Append query) when the delegate is booked on a course. You would need to add to the booking procedure a check that for a new booking for a delegate none of the days of the new course already appear in the DelegateBookedDate table.

    HTH

    Nick

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

    Re: Prevent duplicates over 2 criteria (Access 200

    I wonder if this is the best approach to take, (not that I know any better as I am asking the questions <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>). Doing it this way seems like lots of extra effort, having to create an append query, storing extra data in an extra table and only then querying if a date is conflicting. (If this is the only way to do it, it would have to suffice). Is there not a formula or even a automated check that can be added to the DB to do this without having to store additional data and have to rely on the accuracy of it?

    Hope I don't sound ungrateful for that which you have already given me. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Prevent duplicates over 2 criteria (Access 200

    Do delegates always participate in all the days of a multi- day course?

    Assuming so then I would have a CourseDates table that lists the dates that a course runs for. They junction table would not contain any dates,as that information is known from the course and its dates.

    To prevent a person doing two courses on the same day I would use a VBA function to check whether the person was booked for any courses that ran that day. This could be called in the before update event of the relevant control on a form. The relevant control, would depend on whether you are adding the person to the course, or the course to the person.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent duplicates over 2 criteria (Access 200

    Rudi

    Here's a simpler version. It assumes delegates attend each day of a multi-day course, and that the course table contains a start and end date for each course.

    Once you know the delegate ID, and the proposed start and end dates of the course they want to book, run this query which takes 3 parameters:

    SELECT COUNT(*) FROM tblJunction j INNER JOIN tblCourse c
    ON j.CourseID = c.CourseID
    WHERE (([ProposedStartDate] <= c.EndDate AND [ProposedEndDate] >= c.StartDate) OR ([ProposedEndDate] >= c.StartDate AND [ProposedStartDate] <= c.EndDate))
    AND j.DelID = [Delegate]

    If the returned count > 0 then the delegate's course dates overlap.

    Nick

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

    Re: Prevent duplicates over 2 criteria (Access 200

    John, tx, but if it is possible, I will need some help with setting this up as I do not know too much about VBA in Access. I have attached a tiny sample DB that can be used to help. Theory is one thing, but a viewable and functional DB showing the theory is better.
    I appreciate the advice. I dunno if you have time, but if you do...it would help.
    Cheers
    Attached Files Attached Files
    Regards,
    Rudi

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

    Re: Prevent duplicates over 2 criteria (Access 200

    Hi Bodders,

    I tweaked your SQL to fit the attached database I sent. Here is the SQL, but it is not working. Could you assist if you think this could work.
    TX

    SELECT COUNT(*)
    FROM tblJunction AS j INNER JOIN tblCourse AS c ON j.CourseID = c.CourseID
    WHERE (([ProposedStartDate] <= j.EndDate AND [ProposedEndDate] >= j.StartDate) OR ([ProposedEndDate] >= j.StartDate AND [ProposedStartDate] <= j.EndDate))
    AND j.DelID = [Delegate];
    Regards,
    Rudi

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent duplicates over 2 criteria (Access 200

    Rudi

    I added StartDate and Enddate to the courses table to make this work.

    If you run the query qselCountDup in the revised attachment, and put in the DelID (number), start and end dates for the proposed course you will get back the number of overlapping courses.

    Nick
    Attached Files Attached Files

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

    Re: Prevent duplicates over 2 criteria (Access 200

    <P ID="edit" class=small>(Edited by Rudi on 08-Jan-08 18:25. Modified to include something more...)</P>Nick, I really appreciate all you have done so far, but I would like to ask if this could still be a little more dynamic. (Not for myself, I'm not so difficult to please <img src=/S/yep.gif border=0 alt=yep width=15 height=15>, but for a client). When they mentioned this to my they gave me the impression that they want to add a record to capture a course but if the delegate is already scheduled it must warn them right in the process as they exit the record or enter the record. I don't think they will first want to run a test to see if the person is tied up in another course. I am sure that its not just one person that will be capturing data but a number of data capturers, some with little computer skills, so this must be as dynamic and easy as possible.

    TX as stack...

    PS: The start date and end date should not really be stored in the courses table. The dates belong to the course booking or (the junction table). Many people will be scheduled to do a certain course, so I cannot duplicate the course name just to link a date to it.
    Regards,
    Rudi

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

    Re: Prevent duplicates over 2 criteria (Access 200

    It would help if you posted a representative sample database with some dummy data, instead of an incomplete one that lacks most of the relevant information.

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

    Re: Prevent duplicates over 2 criteria (Access 200

    The problem is that I do not have a database from the client. The one I made here hopefully contains sufficient data to build a probable solution. If it lacks anything more I will gladly build it in, but I am flying blind as to what may be needed for a any solution.

    The attached DB has a tiny amount of sample data. If I focus on Andy in the form. Andy is scheduled for training on course A on the 9th to 11th of Jan. Yet the second course for Andy is scheduled for the 11th which will conflict with course A. How do I prevent this. I need a check built into the form that will look at previous dates for Andy and ensure that none conflict. This is my problem that I do not know how to work around.

    PS: I notice I forgot to assign referential integrity on the joins in the attached example. Sorry!

    TX
    Attached Files Attached Files
    Regards,
    Rudi

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

    Re: Prevent duplicates over 2 criteria (Access 200

    In the attached version I have added an AutoNumber field to the Junction table to have an easy way to identify each record.
    I have added a Before Update event procedure to the form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If DCount("*", "Junction", "Junction.DelID=" & Me.DelID & _
    " AND StartDate<=#" & Format(Me.EndDate, "mm/dd/yyyy") & _
    "# AND EndDate>=#" & Format(Me.StartDate, "mm/dd/yyyy") & _
    "# AND JunctionID<>" & Me.JunctionID) > 0 Then
    MsgBox "Boo!", vbCritical
    Cancel = True
    End If
    End Sub

    This checks whether the proposed period for the course overlaps with other courses for the same delegate, and cancels the update if there are.
    Attached Files Attached Files

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

    Re: Prevent duplicates over 2 criteria (Access 200

    Thankyou for your efforts Hans, and Nick. I appreciate the solution.

    Hans, does the "Cancel = True" statement cancel the attempt to update the record? I've also checked and your date formats you use in the DCount work with any format of date I select. I changed the date format in the table and the form to be Medium Date. No problems with the function.

    TX (a lot)
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

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