query date ranges

[Guest]

Hello

I have a database which holds staff sickness details. Each member of staff
has an Identifier (primary key) and sickness is input using [start date] and
[end date] plus a reason for sickness code.

What I would like to do is run a query that will tell me the number of times
a monday, tuesday, wednesday ...etc is taken off sick to monitor for paterns
of sickness. Is this possible using just the start and ends date my database
uses?

Many thanks... John

 

[OfficeDev18 via AccessMonster.com]

John,

Easy. Just have a For...Next loop, as in:

Dim XX As Date, arSickDays() As String, Cntr As Integer

ReDim arSickDays(EndDate-StartDate+1)
Cntr = 0
For XX = StartDate To EndDate
Cntr = Cntr + 1
arSickDays(Cntr) = Format(XX,"dddd")
Next XX

Now have some code to, perhaps, put the array info into a table, together
with the employee's ID number.

Hope this helps,

Sam

Hello

I have a database which holds staff sickness details. Each member of staff
has an Identifier (primary key) and sickness is input using [start date] and
[end date] plus a reason for sickness code.

What I would like to do is run a query that will tell me the number of times
a monday, tuesday, wednesday ...etc is taken off sick to monitor for paterns
of sickness. Is this possible using just the start and ends date my database
uses?

Many thanks... John

 

[Guest]

Hi Sam

Thanks for the info, however can i trouble you for a bit more advice.

I am unsure where to put this code... I have, as a tester, set up a dummy
form with the data source as my sickness table and entered my code in General
and tried running it in the intermediate window to see what happens.
Unfortunately it keeps stopping and throwing up an error 'invalid outside
procedure.'

My code looks like this

Dim XX As Date, arSickDays() As String, Cntr As Integer

ReDim arSickDays(End_Date - Start_Date + 1)
For XX = Start_Date To End_ Date
Cntr = Cntr + 1
arSickDays(Cntr) = Format(XX, "dddd")
Next XX

It crashes on the 'ReDim arSickDays(End_Date - Start_Date + 1)' line.. i
have also tried
ReDim arSickDays([End Date] - [Start Date] + 1)
ReDim arSickDays([End_Date] - [Start_Date] + 1)
ReDim arSickDays((End_Date) - (Start_Date) + 1)

All these varients come up with the same problem...I know the spaces are bad
design but I inherited it this way... honest!!

I have never used the for... next loop before or arrays so your help is much
appreciated.

Thanks
John

John,

Easy. Just have a For...Next loop, as in:

Dim XX As Date, arSickDays() As String, Cntr As Integer

ReDim arSickDays(EndDate-StartDate+1)
Cntr = 0
For XX = StartDate To EndDate
Cntr = Cntr + 1
arSickDays(Cntr) = Format(XX,"dddd")
Next XX

Now have some code to, perhaps, put the array info into a table, together
with the employee's ID number.

Hope this helps,

Sam

Hello

I have a database which holds staff sickness details. Each member of staff
has an Identifier (primary key) and sickness is input using [start date] and
[end date] plus a reason for sickness code.

What I would like to do is run a query that will tell me the number of times
a monday, tuesday, wednesday ...etc is taken off sick to monitor for paterns
of sickness. Is this possible using just the start and ends date my database
uses?

Many thanks... John

 

[OfficeDev18 via AccessMonster.com]

John,

In T-Sickness, how do you know the employee name or number? I don't see a
space for that; or is that the identifier, which you are using as a foreign
key?

Sam

Cheers Sam... i appreciate your time

Managers log onto our staff database and chose their staff memeber from
cascading combo boxes, once selcted they have various subforms, annual leave,
staff ids and one for sickness. This is opened bu clicking on a command
button.

the sickness subform takes it's info from a table called T_Sickness. the
fields are...

Identifier:Start Date:End Date: Number of days off:number of hours:reason
(this is a value list):First Instace (a check box):Still off sick(a check box)

Sickness details are input on the subform just typing it in

The identifier field is on all my tables as a primary key, so when i run
queries/reports i use my T_StaffDetails table (which contains all my staff
details) for the user name/deprtment/manager etc.

I currently already have a report which allows me to print staff sickness
individually/by team. The report has 3 headers the normal report header, a
team header and a identifier header. Grouped and sorted by team then
identifier. This displays the results by team and individual.

What I would like is a quick way to identify patterns of sickness via a
query/report eg.. how many fridays have been taken off each week/month/year
and by who.

Thanks again
John

Tell you what, John, let's start from the basics. How do you enter the
employee name, the illness (name or code), the start date, and the end date

[quoted text clipped - 46 lines]

 

[Guest]

hi sam

the identifier field is my primary key so my report is based on a query
linking the t_sickness and t_staffdetails this gives me the employee name.

when anyone uses the staff databses and they chose staff by a combo boxes,
this staffname combo box has 3 columns and is bound to column 1.
[identifier] the other 2 are [firstname][surname]. (all users see are cols
2+3 as 1 is set to width 0.) All of the queries then reference this combo box
as a way to identify the staff member.

Sorry if my posts are unclear this is the first time i have asked a
question...
Thanks for bearing with me!!
John

John,

In T-Sickness, how do you know the employee name or number? I don't see a
space for that; or is that the identifier, which you are using as a foreign
key?

Sam

Cheers Sam... i appreciate your time

Managers log onto our staff database and chose their staff memeber from
cascading combo boxes, once selcted they have various subforms, annual leave,
staff ids and one for sickness. This is opened bu clicking on a command
button.

the sickness subform takes it's info from a table called T_Sickness. the
fields are...

Identifier:Start Date:End Date: Number of days off:number of hours:reason
(this is a value list):First Instace (a check box):Still off sick(a check box)

Sickness details are input on the subform just typing it in

The identifier field is on all my tables as a primary key, so when i run
queries/reports i use my T_StaffDetails table (which contains all my staff
details) for the user name/deprtment/manager etc.

I currently already have a report which allows me to print staff sickness
individually/by team. The report has 3 headers the normal report header, a
team header and a identifier header. Grouped and sorted by team then
identifier. This displays the results by team and individual.

What I would like is a quick way to identify patterns of sickness via a
query/report eg.. how many fridays have been taken off each week/month/year
and by who.

Thanks again
John

Tell you what, John, let's start from the basics. How do you enter the
employee name, the illness (name or code), the start date, and the end date

[quoted text clipped - 46 lines]

Many thanks... John

 

[OfficeDev18 via AccessMonster.com]

John,

On the sickness subform, make a new command button, maybe call it cmdAnalyze.
In the button's OnClick event, you might put the code I gave you previously,
but of course it must be expanded to open the table as a recordset and cycle
from top to bottom (of course you can use combo boxes or the like to limit
the start and end dates) and perform the task. After putting all the info
into an array, place the emplyee Info (identifier) and day of week (if you
think it's important, you can include anything, such as calendar date; maybe
you have a baseball fan who's going to the world series. You won't figure out
that by the day of week alone. Calendar date can be another element in the
array) into a table, and make your table the Record Source for an analysis
report. Your report might be "smart" to count all Mondays, Tuesdays, etc., as
well as Fridays. You never know what you'll come up with until you come up
with it...

Sam

hi sam

the identifier field is my primary key so my report is based on a query
linking the t_sickness and t_staffdetails this gives me the employee name.

when anyone uses the staff databses and they chose staff by a combo boxes,
this staffname combo box has 3 columns and is bound to column 1.
[identifier] the other 2 are [firstname][surname]. (all users see are cols
2+3 as 1 is set to width 0.) All of the queries then reference this combo box
as a way to identify the staff member.

Sorry if my posts are unclear this is the first time i have asked a
question...
Thanks for bearing with me!!
John

John,

[quoted text clipped - 40 lines]