Produce the day?

[mariaw]

Hi

I have a table which keeps a record of days off sick.

The table has the field [First Day] = the first day they were off sick, in a dd/mm/yy format

and a [Last Day] = last day they were off sick, again in the same date format.

I need to have a query that will produce the actual day, and all of the days in between; so - if the First Day is 25/10/06, it will produce "Wednesday"
and then the last day is 27/10/06, it will produce "Friday"
But, I also need it to produce "Thursday" (which would be the 26/10/06)

Any ideas??

Thanks

Maria

 

[lightray]

Hi Maria, You could use the Diff statement to calculate the number of days between 2 dates. Then Format(Startdate, "dddd") to get your day names. I'm guessing you can just add 1 to the format to get other day names?

 

[mariaw]

Hi Lightray

I can see how this works, but I am wondering how, for example, if I have a long sickness period: e.g. from 19/9/06 to 21/11/06, although I can get the system to tell me what day the 19/9 and the 21/11 was, how can I get it to tell me what all the days in between are? Is there some way of making a query to produce results 19/9, 20/9, 21/9 etc...and then I could tell the query to say what days they were?

Thanks

Maria

 

[lightray]

Hi Maria,
You can add to the date Format([Startdate]+1,"dddd") but this could be cumbersome in a querie if a person is off sick for a week.
I wonder if there are any other ideas out there?

 

[gemma-the-husky]

have a calendar table (ie every date in the table). You would have to populate it with a recordset but it would only take a momoent.
then you can compare this table with your dates

>= your startdate and
<= your end date.

that will give you a table of dates

 

[Rich]

You could also use a function to fill a table with a list of temp dates based on the dates selected

 

[lightray]

Gemma, Rich
I think maria is concerned about the days between that won't be part of the startdate or enddate. ie if 3 days sick leave what dayname is the second day
if 4 days what is second and third day name etc

 

[Rich]

A function will produce the days between the two dates as will Gemma's suggestion

 

[mariaw]

Hi guys!

Thanks for putting your heads together on this!

So: If I create a query which lists all of the dates of the year, which then automatically produces the day....what then...?

Maria

 

[mariaw]

Hi again

Thought I knew how I could get the table of dates to work....but I clearly don't

If I put in as the first date 1/1/04, how do I get the table to auto populate (e.g. with new records) all the dates up to an including today.....and then to produce what days they were?

So the table would look like this

1/1/04 Tuesday (or whatever!)
2/1/04 Wed
3/1/04 Thurs

etc

It would be great as well if the system would refresh the table every day so that the new day is added (e.g. tomorrow is the 24th Nov?)

) thanks a lot guys

M

 

[mariaw]

Hello again

OK: got the table set up which says what day each date is.

Now: If someone is off sick from the 25th to the 27th Oct, how do I get the system to produce a result to tell me what days the 25th, 26th and 27th are? Where the only things inputted are the first and last days of sickness (e.g. 25th and 27th Oct)?

Maria

 

[gemma-the-husky]

you need a query that produces all the dates from your calendar table "between startsickdate and endsickdate"

I hope that makes sense?

I think perhaps its hard to understand why you need a list of the actual days involved - what are you trying to do with it?

 

[mariaw]

Hi Gemma!

Yes, I do need a query that produces all of the dates from my calender between [startsickdate] and [end sick date] - do you know how I would do this?

I need to know the actual days involved in order work out whether we are losing money or not: if they were off ill on a Saturday and they don't actually work on a Saturday, then we are not losing money, if you see what I mean.

Thanks!!

Maria

 

[neileg]

So all you need is a workdays function. Do a search in these forums.

Try here
http://www.access-programmers.co.uk/forums/showthread.php?t=117749&highlight=workdays

 

[mariaw]

Hi Neil

Thanks my database has to be slightly more complex though; because we do have some staff that work Saturdays, some that work Mon-Fri etc. I do have a record (on the employee table) which says which days they work.

So: what I need is a query that will check, for example, if someone was off from the 25/10 to the 27/10 (say a Monday to a Wednesday) - to check whether that person actually works on any of those days: e.g. Monday, tues or Wed) - and there is also the fact that a person could be off for several weeks...

good one this..!!

Maria

 

[gemma-the-husky]

i think the best way to achieve this depends on what you want to do with the result.

eg if you need say a total of the work hours absent then you need a function not a query, and you could work all this out within the function.

it just seems that having a query returning a list of dates still doesn't get you very far

-----------------------------
if you are preparing say a report of all absences for staff, then perhaps a properly specified bulk query is appropriate, and we need to look at the underlying data structures

alternatively if this is to deal with absences for a single individual (oer even a series of individuals) then having a function do the calcs is a better way to go

I think in particular that if the standard hours for each worker are quite complex then you may not be able to create a query to handle all the variables, and you may HAVE to use a function to evaluate what you need

 

[mariaw]

Re: How to do this - produce the day

have a calendar table (ie every date in the table). You would have to populate it with a recordset but it would only take a momoent.
then you can compare this table with your dates

>= your startdate and
<= your end date.

that will give you a table of dates

Hi Gemma

I have set up a calendar table, which produces the day from the 1/1/04 to the end of 2007 - that should do for now!

So: how do I link this "calendar" table in a query to the table which contains the start & end date of a period of sickness, so that it reproduces every day in between?

Thanks

Maria

 

[mariaw]

Whoops!

Hi Gemma

Sorry - didn't realised you had replied - was looking at page 1!

Anyway: yes, the database needs to look at more than one person, and there are only going to be a few different work patterns - e.g. Mon to Fri; and then part-time hours (not sure what they are) etc. The employee table is filled in with the number of hours that they are supposed to work each day (e.g. 7.4 hours for a Monday).

So - if that person is off sick on a Monday (or if they are off sick for a three weeks, then there might be 3 Mondays when they are off) - then I want the system to tell me that, so I can work out how much money we have lost because that person has been off ill.

I agree a function would be better...just don't know how to write one - can you advise?

Thanks

Maria

 

[gemma-the-husky]

have a look at the employee table

then have a look at the sub "tryme" in the module.

just position the cursor against the start of the sub and click run, and it will execute the funcxtion for the two employees.


the code in the module is not the most efficient, but it should give you an idea


View attachment HoursOff.zip

 

[mariaw]

Hi Gemma

You have blown my mind I sorta understand what this does, but I need the result to print out in a report - not sure how I can do this....

Will keep trying!!
Thanks again

Maria