Find Missing Days

Hi all

I want to create a routine which checks if there are any missing activity
dates between a set period, for EACH person. The query which holds the
records to check is "qry Itinerary Dates Union Spec". I have started trying
to code this but I need to know a) is this the best way to do it and b) how
do I create an additional loop to go through per specialist at a time?

Thanks for any help...

Dim dtStart As Date, dtEnd As Date, dtCurrent As Date

dtStart = Me![StartDate]
dtEnd = Me![EndDate]

dtCurrent = dtStart

Do While dtCurrent dtEnd + 1
If DCount("*", "qry Itinerary Dates Union Spec", "ReviewDate=#" &
dtCurrent & "#") = 0 Then
.... add to a temp table?

hi Sue,

On 05.03.2010 12:06, Sue wrote:
I want to create a routine which checks if there are any missing activity
dates between a set period, for EACH person.
What means 'missing' exactly? Does it mean, that there must be one or
more activities or must there be an activity for each day in that period?

The query which holds the records to check is "qry Itinerary Dates Union Spec".
You should avoid spaces and special characters in object names.

Basically you can do this with a query:

SELECT Count(*)
FROM [qry Itinerary Dates Union Spec] Q
WHERE Q.[ReviewDate] BETWEEN [StartDate] AND [EndDate];

or as domain aggregate:

DCount("*", _
"[qry Itinerary Dates Union Spec]", _
"[ReviewDate] = " & SqlDate([StartDate]) & " " & _
"AND [ReviewDate] = " & SqlDate([EndDate]))


Public Function SqlDate(ADate As Date)

SqlDate = Format(ADate, ""\#m\/d\/yyyy\#")

End Function


mfG
-- stefan --

Hi Stefan

What means 'missing' exactly? Does it mean, that there must be one or more
activities or must there be an activity for each day in that period?

Ok, background info for reference... I have two tables, Itinerary and
Itinerary Dates. Itinerary holds the detail of an activity: ItineraryID
(autonumber), Specialist (Integer), StartDate, NoOfDays, Activity etc.
Itinerary Dates holds two fields ItineraryID (foreignkey) and ReviewDates.
This table is used to store the additional activity dates eg a Meeting on
01/04/10 that lasts 3 days will have one record for 01/04/10 held in
Itinerary and two records eg 02/04/10 & 03/04/10 held in Itinerary Dates
(although these may not be consecutive dates). I then do a union query [qry
Itinerary Dates Union Spec] to join these dates together and produce ALL
activity dates for each specialist. Each person (specialist) must account
for every working day minimum (Mon-Frid), holidays (and bank hols) get
entered as an activity so I don't need to worry about these. What I would
like to do is produce a query which lists any Specialists that have any
'missing' dates within a period (entered by the user as StartDate and
EndDate on a form). I want to see the dates that are unaccounted for as we
need to investigate what that specialist was doing on that date, and create
it in the Itinerary. I don't want to count how many days were missing
because they sometimes randomly work a Saturday or a Sunday too so there is
no logic to apply easily, I just want to see the Specialists name and
Mon-Frid dates that do not exist in the Itinerary for them.

Hope this makes sense... ?

"Stefan Hoffmann" wrote in message
...
hi Sue,

On 05.03.2010 12:06, Sue wrote:
I want to create a routine which checks if there are any missing activity
dates between a set period, for EACH person.
What means 'missing' exactly? Does it mean, that there must be one or more
activities or must there be an activity for each day in that period?

The query which holds the records to check is "qry Itinerary Dates Union
Spec".
You should avoid spaces and special characters in object names.

Basically you can do this with a query:

SELECT Count(*)
FROM [qry Itinerary Dates Union Spec] Q
WHERE Q.[ReviewDate] BETWEEN [StartDate] AND [EndDate];

or as domain aggregate:

DCount("*", _
"[qry Itinerary Dates Union Spec]", _
"[ReviewDate] = " & SqlDate([StartDate]) & " " & _
"AND [ReviewDate] = " & SqlDate([EndDate]))


Public Function SqlDate(ADate As Date)

SqlDate = Format(ADate, ""\#m\/d\/yyyy\#")

End Function


mfG
-- stefan --

hi Sue,

On 05.03.2010 13:15, Sue wrote:
What means 'missing' exactly? Does it mean, that there must be one or more
activities or must there be an activity for each day in that period?

I have two tables, Itinerary and
Itinerary Dates. Itinerary holds the detail of an activity: ItineraryID
(autonumber), Specialist (Integer), StartDate, NoOfDays, Activity etc.
Itinerary Dates holds two fields ItineraryID (foreignkey) and ReviewDates.
I don't understand why you need a UNION query. Cause you need a LEFT or
INNER JOIN:

SELECT I.*, ID.ReviewDate
FROM [Itinerary] I
INNER JOIN [Itinerary Dates] ID
ON ID.IteineryID = I.ItineraryID;

I don't want to count how many days were missing
because they sometimes randomly work a Saturday or a Sunday too so there is
no logic to apply easily, I just want to see the Specialists name and
Mon-Frid dates that do not exist in the Itinerary for them.
The easiest approach would be the usage of a calendar table holding all
dates, then you could use simple join like

(
Specialist CROSS JOIN Calendar
)
LEFT JOIN
(
Itenary LEFT JOIN Itenary Dates
)

The CROSS JOIN gives you all possible dates for each specialist. After
the LEFT JOIN you can filter for all specialists with their dates
without activity.

Creating the calendar is easily built:

Calendar: [Day] Date/Time NOT NULL PRIMARY KEY

Option Compare Database
Option Explicit

Public Sub PopulateCalendar(AYear As Long)

Dim sql As String
Dim day As Date

day = DateSerial(AYear, 1, 1)

Do While Year(day) = AYear
sql = "INSERT INTO [Calendar] ([Day]) " & _
"VALUES (" & Format(day, "\#mm\/dd\/yyyy\#") & ")"
CurrentDb.Execute sql
day = day + 1
Loop

End Sub

Otherwise it will be a little bit more complex.

btw, for filtering on week days use WHERE Weekday([Day], 2) 6 in the
cross join.


mfG
-- stefan --

Hi Stefan

Thanks for this! I am afraid I don't really know SQL that well and I use the
GUI query design mostly rather than the SQL view because of this so I
struggle with these types of joins etc. Never even heard of Cross Join! Will
need to try and read up on this. I created the union query to create a
complete list of dates because I didn't know any other way.

I tried your sql after modifying slightly on field and tablenames, I tried
this:

SELECT I.*, ItineraryID,ReviewDate
FROM [Itinerary] I
INNER JOIN [Itinerary Dates] ID
ON ID.ItineraryID = I.ItineraryID;

but it doesn't work. Perhaps because the field in itinerary dates is
ReviewDates - ie differs from Itinerary.ReviewDate by an 's' on the end. At
the moment though I get an error saying ItineraryID could refer to more than
one table...

Thanks for the code too, will look at this now also. Cheers

"Stefan Hoffmann" wrote in message
...
hi Sue,

On 05.03.2010 13:15, Sue wrote:
What means 'missing' exactly? Does it mean, that there must be one or
more
activities or must there be an activity for each day in that period?

I have two tables, Itinerary and
Itinerary Dates. Itinerary holds the detail of an activity: ItineraryID
(autonumber), Specialist (Integer), StartDate, NoOfDays, Activity etc.
Itinerary Dates holds two fields ItineraryID (foreignkey) and
ReviewDates.
I don't understand why you need a UNION query. Cause you need a LEFT or
INNER JOIN:

SELECT I.*, ID.ReviewDate
FROM [Itinerary] I
INNER JOIN [Itinerary Dates] ID
ON ID.IteineryID = I.ItineraryID;

I don't want to count how many days were missing
because they sometimes randomly work a Saturday or a Sunday too so there
is
no logic to apply easily, I just want to see the Specialists name and
Mon-Frid dates that do not exist in the Itinerary for them.
The easiest approach would be the usage of a calendar table holding all
dates, then you could use simple join like

(
Specialist CROSS JOIN Calendar
)
LEFT JOIN
(
Itenary LEFT JOIN Itenary Dates
)

The CROSS JOIN gives you all possible dates for each specialist. After the
LEFT JOIN you can filter for all specialists with their dates without
activity.

Creating the calendar is easily built:

Calendar: [Day] Date/Time NOT NULL PRIMARY KEY

Option Compare Database
Option Explicit

Public Sub PopulateCalendar(AYear As Long)

Dim sql As String
Dim day As Date

day = DateSerial(AYear, 1, 1)

Do While Year(day) = AYear
sql = "INSERT INTO [Calendar] ([Day]) " & _
"VALUES (" & Format(day, "\#mm\/dd\/yyyy\#") & ")"
CurrentDb.Execute sql
day = day + 1
Loop

End Sub

Otherwise it will be a little bit more complex.

btw, for filtering on week days use WHERE Weekday([Day], 2) 6 in the
cross join.


mfG
-- stefan --

Your syntax has error --
SELECT I.*, ItineraryID,ReviewDate
FROM [Itinerary] I LEFT JOIN [Itinerary Dates] ID ON ID.ItineraryID =
I.ItineraryID;

I think that [Itinerary Dates] should have all of the date, eliminating the
need for the union query.

Also if you include you employee table it would be better --
SELECT Employee.*, I.*, ItineraryID,ReviewDate
FROM (Employee LEFT JOIN [Itinerary] I ON Employee.Specialist =
[Itinerary].Specialist) LEFT JOIN [Itinerary Dates] ID ON ID.ItineraryID =
I.ItineraryID;

--
Build a little, test a little.


"Sue" wrote:

Hi Stefan

Thanks for this! I am afraid I don't really know SQL that well and I use the
GUI query design mostly rather than the SQL view because of this so I
struggle with these types of joins etc. Never even heard of Cross Join! Will
need to try and read up on this. I created the union query to create a
complete list of dates because I didn't know any other way.

I tried your sql after modifying slightly on field and tablenames, I tried
this:

SELECT I.*, ItineraryID,ReviewDate
FROM [Itinerary] I
INNER JOIN [Itinerary Dates] ID
ON ID.ItineraryID = I.ItineraryID;

but it doesn't work. Perhaps because the field in itinerary dates is
ReviewDates - ie differs from Itinerary.ReviewDate by an 's' on the end. At
the moment though I get an error saying ItineraryID could refer to more than
one table...

Thanks for the code too, will look at this now also. Cheers

"Stefan Hoffmann" wrote in message
...
hi Sue,

On 05.03.2010 13:15, Sue wrote:
What means 'missing' exactly? Does it mean, that there must be one or
more
activities or must there be an activity for each day in that period?

I have two tables, Itinerary and
Itinerary Dates. Itinerary holds the detail of an activity: ItineraryID
(autonumber), Specialist (Integer), StartDate, NoOfDays, Activity etc.
Itinerary Dates holds two fields ItineraryID (foreignkey) and
ReviewDates.
I don't understand why you need a UNION query. Cause you need a LEFT or
INNER JOIN:

SELECT I.*, ID.ReviewDate
FROM [Itinerary] I
INNER JOIN [Itinerary Dates] ID
ON ID.IteineryID = I.ItineraryID;

I don't want to count how many days were missing
because they sometimes randomly work a Saturday or a Sunday too so there
is
no logic to apply easily, I just want to see the Specialists name and
Mon-Frid dates that do not exist in the Itinerary for them.
The easiest approach would be the usage of a calendar table holding all
dates, then you could use simple join like

(
Specialist CROSS JOIN Calendar
)
LEFT JOIN
(
Itenary LEFT JOIN Itenary Dates
)

The CROSS JOIN gives you all possible dates for each specialist. After the
LEFT JOIN you can filter for all specialists with their dates without
activity.

Creating the calendar is easily built:

Calendar: [Day] Date/Time NOT NULL PRIMARY KEY

Option Compare Database
Option Explicit

Public Sub PopulateCalendar(AYear As Long)

Dim sql As String
Dim day As Date

day = DateSerial(AYear, 1, 1)

Do While Year(day) = AYear
sql = "INSERT INTO [Calendar] ([Day]) " & _
"VALUES (" & Format(day, "\#mm\/dd\/yyyy\#") & ")"
CurrentDb.Execute sql
day = day + 1
Loop

End Sub

Otherwise it will be a little bit more complex.

btw, for filtering on week days use WHERE Weekday([Day], 2) 6 in the
cross join.


mfG
-- stefan --


.

"Sue" wrote in message
...
Hi all

I want to create a routine which checks if there are any missing activity
dates between a set period, for EACH person. The query which holds the
records to check is "qry Itinerary Dates Union Spec". I have started
trying to code this but I need to know a) is this the best way to do it
and b) how do I create an additional loop to go through per specialist at
a time?

Thanks for any help...

Dim dtStart As Date, dtEnd As Date, dtCurrent As Date

dtStart = Me![StartDate]
dtEnd = Me![EndDate]

dtCurrent = dtStart

Do While dtCurrent dtEnd + 1
If DCount("*", "qry Itinerary Dates Union Spec", "ReviewDate=#" &
dtCurrent & "#") = 0 Then
.... add to a temp table?

eeerrtrtyuioll

"KARL DEWEY" a écrit dans le message
de groupe de discussion :
...
Your syntax has error --
SELECT I.*, ItineraryID,ReviewDate
FROM [Itinerary] I LEFT JOIN [Itinerary Dates] ID ON ID.ItineraryID =
I.ItineraryID;

I think that [Itinerary Dates] should have all of the date, eliminating
the
need for the union query.

Also if you include you employee table it would be better --
SELECT Employee.*, I.*, ItineraryID,ReviewDate
FROM (Employee LEFT JOIN [Itinerary] I ON Employee.Specialist =
[Itinerary].Specialist) LEFT JOIN [Itinerary Dates] ID ON ID.ItineraryID =
I.ItineraryID;

--
Build a little, test a little.


"Sue" wrote:

Hi Stefan

Thanks for this! I am afraid I don't really know SQL that well and I use
the
GUI query design mostly rather than the SQL view because of this so I
struggle with these types of joins etc. Never even heard of Cross Join!
Will
need to try and read up on this. I created the union query to create a
complete list of dates because I didn't know any other way.

I tried your sql after modifying slightly on field and tablenames, I
tried
this:

SELECT I.*, ItineraryID,ReviewDate
FROM [Itinerary] I
INNER JOIN [Itinerary Dates] ID
ON ID.ItineraryID = I.ItineraryID;

but it doesn't work. Perhaps because the field in itinerary dates is
ReviewDates - ie differs from Itinerary.ReviewDate by an 's' on the end.
At
the moment though I get an error saying ItineraryID could refer to more
than
one table...

Thanks for the code too, will look at this now also. Cheers

"Stefan Hoffmann" wrote in message
...
hi Sue,

On 05.03.2010 13:15, Sue wrote:
What means 'missing' exactly? Does it mean, that there must be one or
more
activities or must there be an activity for each day in that period?

I have two tables, Itinerary and
Itinerary Dates. Itinerary holds the detail of an activity:
ItineraryID
(autonumber), Specialist (Integer), StartDate, NoOfDays, Activity etc.
Itinerary Dates holds two fields ItineraryID (foreignkey) and
ReviewDates.
I don't understand why you need a UNION query. Cause you need a LEFT or
INNER JOIN:

SELECT I.*, ID.ReviewDate
FROM [Itinerary] I
INNER JOIN [Itinerary Dates] ID
ON ID.IteineryID = I.ItineraryID;

I don't want to count how many days were missing
because they sometimes randomly work a Saturday or a Sunday too so
there
is
no logic to apply easily, I just want to see the Specialists name and
Mon-Frid dates that do not exist in the Itinerary for them.
The easiest approach would be the usage of a calendar table holding all
dates, then you could use simple join like

(
Specialist CROSS JOIN Calendar
)
LEFT JOIN
(
Itenary LEFT JOIN Itenary Dates
)

The CROSS JOIN gives you all possible dates for each specialist. After
the
LEFT JOIN you can filter for all specialists with their dates without
activity.

Creating the calendar is easily built:

Calendar: [Day] Date/Time NOT NULL PRIMARY KEY

Option Compare Database
Option Explicit

Public Sub PopulateCalendar(AYear As Long)

Dim sql As String
Dim day As Date

day = DateSerial(AYear, 1, 1)

Do While Year(day) = AYear
sql = "INSERT INTO [Calendar] ([Day]) " & _
"VALUES (" & Format(day, "\#mm\/dd\/yyyy\#") & ")"
CurrentDb.Execute sql
day = day + 1
Loop

End Sub

Otherwise it will be a little bit more complex.

btw, for filtering on week days use WHERE Weekday([Day], 2) 6 in the
cross join.


mfG
-- stefan --


.