Need help with query to find duplicates

[RichO]

Hi all!

I am looking for a little advice on a query. Here is the situation:

This database keeps track of calendars of many bands performing at many locations. What I need to do is find all occurrences of what we call "double bookings" or venues that have duplicate entries of bands on the same date.

What I am looking for is any records where the fields [Job_Date] and [Job_Place] match any other records in the table.

Here's the trick though: There is also a field called [Job_End_Date]. This is used when a band is scheduled for more than one consecutive night at a venue.

Example: Bob's band is scheduled at Jim's Place on 12/1/2009 through 12/2/2009. Mike's band is mistakenly scheduled at Jim's place on 12/2/2009. I need the query to be able to find such occurrences but comparing the field [Job_Date] will not reveal this record as a duplicate, although it is because [Job_Date] of the 2nd record falls within the range of [Job_Date] through [Job_End_Date] of the first record.

The following code is what I am using which creates a temporary table of the duplicates, and it works fine when testing it with a reduced size database, but when running it with the thousands of records in the actual table, this takes a very long time to run. Actually Access becomes non-responsive when running it.

sql = "SELECT Job_Number, Job_Date, Job_End_Date, Job_Place FROM tb_Jobs WHERE Job_Date>Date() ORDER BY Job_Date"
Set rs = db.OpenRecordset(sql)
Do While Not rs.EOF
    For i = rs!Job_Date To rs!Job_End_Date
        sql = "INSERT INTO tb_MultiNighters (Job_Date, Job_End_Date, Artist_Name, Job_Place) "
        sql = sql & "SELECT Job_Date, Job_End_Date, Artist_Name, Job_Place FROM tb_Jobs WHERE #" & i & "# Between Job_Date AND Job_End_Date "
        sql = sql & " AND Job_Place='" & rs!Job_Place & "' AND Job_Number<>" & rs!Job_Number 
        db.Execute sql
    Next i
    rs.MoveNext
Loop
rs.Close

Looking for a better way to do this.

Thanks for any help you can offer.

 

[HiTechCoach]

i think this may be exactly what you need:

Clashing Events/Appointments

Hope this helps ...

 

[RichO]

That's what I was looking for.

Thanks