Function to count date occurrences

[Leslie Isaacs]

Hello All

I have previously posted this problem in the Queries newsgroup, but I now
think it may be more appropriate here: apologies for the cross-posting!

I have a table [absences], with fields 'absencedate' (a date/time filed),
'absencetype' (a 1-character text field) and 'employeename' (text). Each
record represents one day of absence.

Where any two absence periods (which may be 1 or more consecutive days) are
seperated by 8 weeks or less, they are considered 'linked'. Thus any number
of absence periods could all be linked if the maximun gap between
consecutive periods is 8 weeks or less.

I need to find, for any newly-entered absence period start date, the total
number of absence days that have occured for the selected
employee, where 'absencetype' ="W", within any absence periods that are
'linked' to the newly-entered absence period start date.

I THINK the way to do this might be along the lines of the following:

In a module, open a recordset with all the selected employee's absence
dates/types.
Create a loop that repeats for each date from the newly-entered absence
period start date, and increments backwards, until a 'stop' flag become
True.

Herewith my English/vba version of the loop:

Before starting loop:
'Stop'=False
'countW' = 0
'gap' = 0
'datetocheck' = newly-entered absence period start date

Within the loop:
If 'stop' = True then goto exitloop
first check if the 'loop-date' is an absence date
if it is not an absence date: if 'gap' <=56 then 'gap'= 'gap'+1, else if
'gap' >56 then 'stop' = True
if it is an absence date: 'gap' = 0
if 'absencetype' = "W" then 'countW' = 'countW' +1
'datetocheck' = 'datetocheck' +1
Repeat loop
Exitloop

If I am on the right lines, I hope someone can help me with the logic and
the syntax.

Many thanks
Leslie Isaacs

 

[JerryData]

Whoa! Hitting a rabbit with an elephant gun!
I'm on the road, so I'll give you a hint.
create a recordset with absences.
use a datediff query to find any related entries
example: datediff("d",-56, somedate)
you can email me if you want the whole thing
shouldn't take long
(e-mail address removed)

 

[Leslie Isaacs]

Hello Jerry

Many thanks for your reply.
I'm not sure this can be done in the way you suggest - because it's not just
a case of looking for "W" absences within a set time-gap. Potentially there
could be any number of absence periods (of 1 or more days each) that are all
'linked' - during which any "W" absences would need to be counted!

Do you still think I don't need an elephant gun?
Les

Whoa! Hitting a rabbit with an elephant gun!
I'm on the road, so I'll give you a hint.
create a recordset with absences.
use a datediff query to find any related entries
example: datediff("d",-56, somedate)
you can email me if you want the whole thing
shouldn't take long
(e-mail address removed)
--
-Jerry
JR Data Inc.

Hello All

I have previously posted this problem in the Queries newsgroup, but I now
think it may be more appropriate here: apologies for the cross-posting!

I have a table [absences], with fields 'absencedate' (a date/time filed),
'absencetype' (a 1-character text field) and 'employeename' (text). Each
record represents one day of absence.

Where any two absence periods (which may be 1 or more consecutive days)
are
seperated by 8 weeks or less, they are considered 'linked'. Thus any
number
of absence periods could all be linked if the maximun gap between
consecutive periods is 8 weeks or less.

I need to find, for any newly-entered absence period start date, the
total
number of absence days that have occured for the selected
employee, where 'absencetype' ="W", within any absence periods that are
'linked' to the newly-entered absence period start date.

I THINK the way to do this might be along the lines of the following:

In a module, open a recordset with all the selected employee's absence
dates/types.
Create a loop that repeats for each date from the newly-entered absence
period start date, and increments backwards, until a 'stop' flag become
True.

Herewith my English/vba version of the loop:

Before starting loop:
'Stop'=False
'countW' = 0
'gap' = 0
'datetocheck' = newly-entered absence period start date

Within the loop:
If 'stop' = True then goto exitloop
first check if the 'loop-date' is an absence date
if it is not an absence date: if 'gap' <=56 then 'gap'= 'gap'+1, else if
'gap' >56 then 'stop' = True
if it is an absence date: 'gap' = 0
if 'absencetype' = "W" then 'countW' = 'countW' +1
'datetocheck' = 'datetocheck' +1
Repeat loop
Exitloop

If I am on the right lines, I hope someone can help me with the logic and
the syntax.

Many thanks
Leslie Isaacs

 

[JerryData]

how about datediff("d",<=56, somedate)

--
-Jerry
JR Data Inc.

Hello Jerry

Many thanks for your reply.
I'm not sure this can be done in the way you suggest - because it's not just
a case of looking for "W" absences within a set time-gap. Potentially there
could be any number of absence periods (of 1 or more days each) that are all
'linked' - during which any "W" absences would need to be counted!

Do you still think I don't need an elephant gun?
Les

Whoa! Hitting a rabbit with an elephant gun!
I'm on the road, so I'll give you a hint.
create a recordset with absences.
use a datediff query to find any related entries
example: datediff("d",-56, somedate)
you can email me if you want the whole thing
shouldn't take long
(e-mail address removed)
--
-Jerry
JR Data Inc.

Hello All

I have previously posted this problem in the Queries newsgroup, but I now
think it may be more appropriate here: apologies for the cross-posting!

I have a table [absences], with fields 'absencedate' (a date/time filed),
'absencetype' (a 1-character text field) and 'employeename' (text). Each
record represents one day of absence.

Where any two absence periods (which may be 1 or more consecutive days)
are
seperated by 8 weeks or less, they are considered 'linked'. Thus any
number
of absence periods could all be linked if the maximun gap between
consecutive periods is 8 weeks or less.

I need to find, for any newly-entered absence period start date, the
total
number of absence days that have occured for the selected
employee, where 'absencetype' ="W", within any absence periods that are
'linked' to the newly-entered absence period start date.

I THINK the way to do this might be along the lines of the following:

In a module, open a recordset with all the selected employee's absence
dates/types.
Create a loop that repeats for each date from the newly-entered absence
period start date, and increments backwards, until a 'stop' flag become
True.

Herewith my English/vba version of the loop:

Before starting loop:
'Stop'=False
'countW' = 0
'gap' = 0
'datetocheck' = newly-entered absence period start date

Within the loop:
If 'stop' = True then goto exitloop
first check if the 'loop-date' is an absence date
if it is not an absence date: if 'gap' <=56 then 'gap'= 'gap'+1, else if
'gap' >56 then 'stop' = True
if it is an absence date: 'gap' = 0
if 'absencetype' = "W" then 'countW' = 'countW' +1
'datetocheck' = 'datetocheck' +1
Repeat loop
Exitloop

If I am on the right lines, I hope someone can help me with the logic and
the syntax.

Many thanks
Leslie Isaacs

 

[Leslie Isaacs]

Hello Jerry

Thanks for your further reply.
I just can't see how it would be possible to use DateDiff count the "W"
absences, since I don't know how far back to go. Certainly I can't just look
56 days back, because is is likely that there will be 'linked' absences
going back further thas that!
I really think I need some kind of loop: don't you?

Thanks again
Les

how about datediff("d",<=56, somedate)

--
-Jerry
JR Data Inc.

Hello Jerry

Many thanks for your reply.
I'm not sure this can be done in the way you suggest - because it's not just
a case of looking for "W" absences within a set time-gap. Potentially there
could be any number of absence periods (of 1 or more days each) that are all
'linked' - during which any "W" absences would need to be counted!

Do you still think I don't need an elephant gun?
Les

Whoa! Hitting a rabbit with an elephant gun!
I'm on the road, so I'll give you a hint.
create a recordset with absences.
use a datediff query to find any related entries
example: datediff("d",-56, somedate)
you can email me if you want the whole thing
shouldn't take long
(e-mail address removed)
--
-Jerry
JR Data Inc.


:

Hello All

I have previously posted this problem in the Queries newsgroup, but I now
think it may be more appropriate here: apologies for the cross-posting!

I have a table [absences], with fields 'absencedate' (a date/time filed),
'absencetype' (a 1-character text field) and 'employeename' (text). Each
record represents one day of absence.

Where any two absence periods (which may be 1 or more consecutive days)
are
seperated by 8 weeks or less, they are considered 'linked'. Thus any
number
of absence periods could all be linked if the maximun gap between
consecutive periods is 8 weeks or less.

I need to find, for any newly-entered absence period start date, the
total
number of absence days that have occured for the selected
employee, where 'absencetype' ="W", within any absence periods that are
'linked' to the newly-entered absence period start date.

I THINK the way to do this might be along the lines of the following:

In a module, open a recordset with all the selected employee's absence
dates/types.
Create a loop that repeats for each date from the newly-entered absence
period start date, and increments backwards, until a 'stop' flag become
True.

Herewith my English/vba version of the loop:

Before starting loop:
'Stop'=False
'countW' = 0
'gap' = 0
'datetocheck' = newly-entered absence period start date

Within the loop:
If 'stop' = True then goto exitloop
first check if the 'loop-date' is an absence date
if it is not an absence date: if 'gap' <=56 then 'gap'= 'gap'+1, else if
'gap' >56 then 'stop' = True
if it is an absence date: 'gap' = 0
if 'absencetype' = "W" then 'countW' = 'countW' +1
'datetocheck' = 'datetocheck' +1
Repeat loop
Exitloop

If I am on the right lines, I hope someone can help me with the logic and
the syntax.

Many thanks
Leslie Isaacs

 

[JerryData]

You might loop through the table and check for a linked record for the table,
maybe save it to a recordset, temp table or array.
Then check that for links, etc.
What is the output of this code?
--
-Jerry
JR Data Inc.

Hello Jerry

Thanks for your further reply.
I just can't see how it would be possible to use DateDiff count the "W"
absences, since I don't know how far back to go. Certainly I can't just look
56 days back, because is is likely that there will be 'linked' absences
going back further thas that!
I really think I need some kind of loop: don't you?

Thanks again
Les

how about datediff("d",<=56, somedate)

--
-Jerry
JR Data Inc.

Hello Jerry

Many thanks for your reply.
I'm not sure this can be done in the way you suggest - because it's not just
a case of looking for "W" absences within a set time-gap. Potentially there
could be any number of absence periods (of 1 or more days each) that are all
'linked' - during which any "W" absences would need to be counted!

Do you still think I don't need an elephant gun?
Les


Whoa! Hitting a rabbit with an elephant gun!
I'm on the road, so I'll give you a hint.
create a recordset with absences.
use a datediff query to find any related entries
example: datediff("d",-56, somedate)
you can email me if you want the whole thing
shouldn't take long
(e-mail address removed)
--
-Jerry
JR Data Inc.


:

Hello All

I have previously posted this problem in the Queries newsgroup, but I now
think it may be more appropriate here: apologies for the cross-posting!

I have a table [absences], with fields 'absencedate' (a date/time filed),
'absencetype' (a 1-character text field) and 'employeename' (text). Each
record represents one day of absence.

Where any two absence periods (which may be 1 or more consecutive days)
are
seperated by 8 weeks or less, they are considered 'linked'. Thus any
number
of absence periods could all be linked if the maximun gap between
consecutive periods is 8 weeks or less.

I need to find, for any newly-entered absence period start date, the
total
number of absence days that have occured for the selected
employee, where 'absencetype' ="W", within any absence periods that are
'linked' to the newly-entered absence period start date.

I THINK the way to do this might be along the lines of the following:

In a module, open a recordset with all the selected employee's absence
dates/types.
Create a loop that repeats for each date from the newly-entered absence
period start date, and increments backwards, until a 'stop' flag become
True.

Herewith my English/vba version of the loop:

Before starting loop:
'Stop'=False
'countW' = 0
'gap' = 0
'datetocheck' = newly-entered absence period start date

Within the loop:
If 'stop' = True then goto exitloop
first check if the 'loop-date' is an absence date
if it is not an absence date: if 'gap' <=56 then 'gap'= 'gap'+1, else if
'gap' >56 then 'stop' = True
if it is an absence date: 'gap' = 0
if 'absencetype' = "W" then 'countW' = 'countW' +1
'datetocheck' = 'datetocheck' +1
Repeat loop
Exitloop

If I am on the right lines, I hope someone can help me with the logic and
the syntax.

Many thanks
Leslie Isaacs