R
Ron Hinds
I'm building an appointment scheduling feature and I'm having trouble trying
to figure out if a user has created scheduling conflicts. Here is the
scenario: The user will choose from a list of their customers the day(s) of
the week they wish to call that customer, a call time, and optionally
another call time, in five minute increments. The actual schedule will be
built from that template nightly rolling 90 days out. I want to determine if
there are conflicts in the template. The template table is laid out as
follows:
[SalesID] Text (50)
[CustID] Text (50)
[CallTime1] Date/Time
[CallTime2] Date/Time
[Monday] Boolean
[Tuesday] Boolean
[Wednesday] Boolean
[Thursday] Boolean
[Friday] Boolean
[Periodicity] Number (Long)
The Primary Key is [SalesID] and [CustID]
I tried using the Find Duplicates wizard, selecting only the columns that
may have dupes (i.e. everything except the SalesID/CustID). In the next
step, it asks which columns to return in addition to those that might be
dupes. I ask for the CustID field. But the resulting query doesn't return
anything when I run a test, even though every field except CustID is
identical in two records. Here is the SQL the wizard generated:
SELECT DISTINCTROW tblSalesmanApptsTemp.CallTime1,
tblSalesmanApptsTemp.CallTime2, tblSalesmanApptsTemp.Monday,
tblSalesmanApptsTemp.Tuesday, tblSalesmanApptsTemp.Wednesday,
tblSalesmanApptsTemp.Thursday, tblSalesmanApptsTemp.Friday,
tblSalesmanApptsTemp.Periodicity, tblSalesmanApptsTemp.CustID
FROM tblSalesmanApptsTemp
WHERE (((tblSalesmanApptsTemp.CallTime1) In (SELECT [CallTime1] FROM
[tblSalesmanApptsTemp] As Tmp GROUP BY
[CallTime1],[CallTime2],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[
Periodicity] HAVING Count(*)>1 And [CallTime2] =
[tblSalesmanApptsTemp].[CallTime2] And [Monday] =
[tblSalesmanApptsTemp].[Monday] And [Tuesday] =
[tblSalesmanApptsTemp].[Tuesday] And [Wednesday] =
[tblSalesmanApptsTemp].[Wednesday] And [Thursday] =
[tblSalesmanApptsTemp].[Thursday] And [Friday] =
[tblSalesmanApptsTemp].[Friday] And [Periodicity] =
[tblSalesmanApptsTemp].[Periodicity])))
ORDER BY tblSalesmanApptsTemp.CallTime1, tblSalesmanApptsTemp.CallTime2,
tblSalesmanApptsTemp.Monday, tblSalesmanApptsTemp.Tuesday,
tblSalesmanApptsTemp.Wednesday, tblSalesmanApptsTemp.Thursday,
tblSalesmanApptsTemp.Friday, tblSalesmanApptsTemp.Periodicity;
If I remove CustID then it finds the dupes no problem. But, that isn't very
useful to me! BTW I'm using Access 97.
to figure out if a user has created scheduling conflicts. Here is the
scenario: The user will choose from a list of their customers the day(s) of
the week they wish to call that customer, a call time, and optionally
another call time, in five minute increments. The actual schedule will be
built from that template nightly rolling 90 days out. I want to determine if
there are conflicts in the template. The template table is laid out as
follows:
[SalesID] Text (50)
[CustID] Text (50)
[CallTime1] Date/Time
[CallTime2] Date/Time
[Monday] Boolean
[Tuesday] Boolean
[Wednesday] Boolean
[Thursday] Boolean
[Friday] Boolean
[Periodicity] Number (Long)
The Primary Key is [SalesID] and [CustID]
I tried using the Find Duplicates wizard, selecting only the columns that
may have dupes (i.e. everything except the SalesID/CustID). In the next
step, it asks which columns to return in addition to those that might be
dupes. I ask for the CustID field. But the resulting query doesn't return
anything when I run a test, even though every field except CustID is
identical in two records. Here is the SQL the wizard generated:
SELECT DISTINCTROW tblSalesmanApptsTemp.CallTime1,
tblSalesmanApptsTemp.CallTime2, tblSalesmanApptsTemp.Monday,
tblSalesmanApptsTemp.Tuesday, tblSalesmanApptsTemp.Wednesday,
tblSalesmanApptsTemp.Thursday, tblSalesmanApptsTemp.Friday,
tblSalesmanApptsTemp.Periodicity, tblSalesmanApptsTemp.CustID
FROM tblSalesmanApptsTemp
WHERE (((tblSalesmanApptsTemp.CallTime1) In (SELECT [CallTime1] FROM
[tblSalesmanApptsTemp] As Tmp GROUP BY
[CallTime1],[CallTime2],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[
Periodicity] HAVING Count(*)>1 And [CallTime2] =
[tblSalesmanApptsTemp].[CallTime2] And [Monday] =
[tblSalesmanApptsTemp].[Monday] And [Tuesday] =
[tblSalesmanApptsTemp].[Tuesday] And [Wednesday] =
[tblSalesmanApptsTemp].[Wednesday] And [Thursday] =
[tblSalesmanApptsTemp].[Thursday] And [Friday] =
[tblSalesmanApptsTemp].[Friday] And [Periodicity] =
[tblSalesmanApptsTemp].[Periodicity])))
ORDER BY tblSalesmanApptsTemp.CallTime1, tblSalesmanApptsTemp.CallTime2,
tblSalesmanApptsTemp.Monday, tblSalesmanApptsTemp.Tuesday,
tblSalesmanApptsTemp.Wednesday, tblSalesmanApptsTemp.Thursday,
tblSalesmanApptsTemp.Friday, tblSalesmanApptsTemp.Periodicity;
If I remove CustID then it finds the dupes no problem. But, that isn't very
useful to me! BTW I'm using Access 97.