Dates (A2k)

[D Willett]

Does anyone possess an information paper or word doc etc, showing a full range of Date expressions to use in queries.

ie

First Date of current week
Last Date of current week

First Date of current month
Last Date of current month

First Date of Each Month
Last Date of Each Month

etc etc

I need to quite a lot of calculations in queries regarding Hrs, Days, Weeks, Months, Years etc.
An information paper would be extremely useful.

[MarkD]

I don't know of a comprehensive reference, but this MSKB article summarizes many of these calculations:

ACC2000: Functions for Calculating and Displaying Date/Time Values

HTH

[D Willett]

Thanks Mark.

This is very useful.

[D Willett]

Mark
The samples provided are very good but just a further question.

How do I use them in a query.

I have a list of employees showing there productivity rating, ie 25%, 50%, 100% etc.
This is the easy bit,
"HrsAvail: [productivityrate]*8/100" which gives the hrs available per day.
"Hrs Per Week: [hrsAvail]*5" which gives the hrs available per week.

The complicated part starts from here:

How to calculate per month
How to calculate per quarter
How to calculate per Year

I must take into consideration Standard Bank Holidays etc which I have defined in a module "modWeekDaysMinusHolidays"
So this in fact makes the weekly calculation as above none functional.

My query is as follows:-

SELECT qryStaff.Unit AS Employee, qryStaff.ProductivityRate AS [ProductivityRate in %], qryStaff.HrsAvail AS [Hrs Per Day], [hrsAvail]*5 AS [Hrs Per Week]
FROM qryStaff
WHERE (((qryStaff.Production)=True) AND ((qryStaff.Active)=True));

Some indication as to how this should be with the interaction of the module would be really helpful.
The attached PNG shows how I am trying to achieve the basic layout, for use later on in future calculations.

[D Willett]

<P ID="edit" class=small>(Edited by D Willett on 09-Sep-03 17:40. Expression Found (bottom of post)
)</P>The expression as follows, looks specifically designed for this process, but how can it be manipulated to suit my purpose.

WeekDaysMinusHolidays([Date1],[Date2])

I do not have a Date1 & Date2 within the query.
My need would be

WeekDaysMinusHolidays([StartOfWeek],[EndOfWeek])
WeekDaysMinusHolidays([StartOfMonth],[EndOfMonth])
WeekDaysMinusHolidays([StartOfQuarter],[EndOfQuarter])
WeekDaysMinusHolidays([StartOfYear],[EndOfYear])

Does this further explanation help to describe what I need ?

Post edited below:




SELECT tblStaff.Code, tblStaff.Surname, tblStaff.Initial, tblStaff.Workgroup, tblStaff.CurrencyRate, tblStaff.ProductivityRate, tblStaff.Active, tblStaff.Address, tblStaff.TelNo, tblStaff.OtherContact, tblStaff.EmployeeType, tblStaff.NINumber, tblStaff.Production, [productivityrate]*8/100 AS HrsAvail, tblStaff.Code AS UnitID, [Surname] & "," & [initial] AS Unit, [hrsAvail]*5 AS [Hrs Per Week], Weekdaysminusholidays(Date()-Weekday(Date(),0)+1,Date()-Weekday(Date())+7)*[productivityrate]/100 AS [Days This Week], Weekdaysminusholidays(Date()-Weekday(Date(),0)+1,Date()-Weekday(Date())+7)*[hrsavail] AS [Hrs This Week], Weekdaysminusholidays(DateSerial(Year(Date()),Mont h(Date()),1),(DateSerial(Year(Date()),Month(Date() )+1,0)))*[productivityrate]/100 AS [Days This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Mont h(Date()),1),(DateSerial(Year(Date()),Month(Date() )+1,0)))*[hrsavail] AS [Hrs This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Int( (Month(Date())-1)/3)*3+1,1),(DateSerial(Year(Date()),Int((Month(Date ())-1)/3)*3+4,0)))*[productivityrate]/100 AS [Days This Qtr], Weekdaysminusholidays(DateSerial(Year(Date()),Int( (Month(Date())-1)/3)*3+1,1),(DateSerial(Year(Date()),Int((Month(Date ())-1)/3)*3+4,0)))*[hrsavail] AS [Hrs This Qtr], Weekdaysminusholidays(CDate("1/1/" & Year(Date())),CDate("1/1/" & Year(Date())+1)-1)*[productivityrate]/100 AS [Days This Year], Weekdaysminusholidays(CDate("1/1/" & Year(Date())),CDate("1/1/" & Year(Date())+1)-1)*[hrsavail] AS [Hrs This Year]
FROM tblStaff;

[MarkD]

I had a chance to look at this, my recommendation would be to create some user-defined functions to calculate the number of workdays in current week, month, quarter, and year, and use these in the queries that calculate the work-hours based on employee's productivity rate. Examples:

Public Function GetWorkdaysThisWeek() As Integer

GetWorkdaysThisWeek = GetWorkDays(Date - Weekday(Date) + 1, _
Date - Weekday(Date) + 7, True)
End Function

Public Function GetWorkdaysThisMonth() As Integer

GetWorkdaysThisMonth = GetWorkDays(DateSerial(Year(Date), Month(Date), 1), _
DateSerial(Year(Date), Month(Date) + 1, 0), True)
End Function

Public Function GetWorkdaysThisQtr() As Integer

GetWorkdaysThisQtr = GetWorkDays(DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1), DateSerial(Year( _
Date), Int((Month(Date) - 1) / 3) * 3 + 4, 0), True)

End Function

Public Function GetWorkdaysThisYear() As Integer

GetWorkdaysThisYear = GetWorkDays(DateSerial(Year(Date), 1, 1), _
DateSerial(Year(Date), 12, 31), True)
End Function

Example of results:

? GetWorkdaysThisWeek
5
? GetWorkdaysThisMonth
21
? GetWorkdaysThisQtr
64
? GetWorkdaysThisYear
251

These functions use some of the formulas provided by MSKB article, and a user-defined GetWorkDays function to calculate number of workdays in specified period. Holidays are taken into account using a GetHolidayCount function. See attached text file to see code used for these functions. They may be similar to what you are using. You'd have to change the table & field names in GetHolidayCount function to reflect the holiday table you are using. Using functions shown above should simplify the syntax used in query. You'd just multiply number of days returned by function times number of hours available per day for the employee (I assume this value or the percentage used is stored in one of the tables used in query).

If some of the employees are only producing at 25% productivity, unless they're part-timers they should get the sack.

To import the attached text file as module, change file extension to ".bas", then import from VB Editor (File>Import File command).

HTH

[D Willett]

<P ID="edit" class=small>(Edited by D Willett on 10-Sep-03 18:39. Additional question relating to same post added at the end.)</P>If some of the employees are only producing at 25% productivity, unless they're part-timers they should get the sack.

Mark, Steady on a bit !!

I'll explain:-
A 25% operative does have a valuable meaning.
An operatives Productivity is not the same as their Efficiency.

An operative with a 100% Productivity rating means he is fully skilled, His Efficiency is calculated by Time Given Multiplied by Time Taken.
Basically because of his skill, if he is given a 10hr task, he can achieve it in 10hrs or less.
He recieves a fully experienced hourly staff rate.

A 50% operative is semi skilled, give him a 10hr task, he will complete it in 20hrs or less, and recieves only 50% of a fully experienced hourly rate.

A 25% operative is a new comer with very little skill, mainly apprentices and the rest should be clear as to his salary and status.

Each operative has his own target to achieve.
If he wants more salary, he must learn more to achieve more, the better the efficiency, the higher the productivity.
Pretty basic really.

This is where all these calculations come into affect.

To load the workshop, we need to know how many hrs are available given the amount of operatives.

So, 3 fully skilled would mean 8hrs each for each day, giving 24hrs per day I can book work in.

Now, having 2 x 50%ers would mean I could book 4hrs each operative per day.

2 x 25%ers would mean 2hrs booked in per day.

So 3 x 100%ers, 2 x 50%ers, 2 x 25%ers would give an accurate 36hrs available each day.

You can probably tell by this the reason for these expressions I needed.

I have the SQL query above which I can hold a constant availability of hrs. I am hoping to be able to pick info from this for date ranges, but have I made the correct decision here.???
This is the ultimate question before I proceed.

Thanks for the information as above anyway.

A further question, How to remove breaks ?
Our staff work 08:30 to 17:15, without the break, this gives 8hrs, with break 8.75hrs.
How does the expression read to accomplish this:-

WeekDaysMinusHolidays(From,To)-.75hr ???

[HansV]

Are you still looking for answers, or did you solve this?

[D Willett]

Hans
I managed to (I think) do what I set out to do, ie:

SELECT tblStaff.Code, tblStaff.Surname, tblStaff.Initial, tblStaff.Workgroup, tblStaff.CurrencyRate, tblStaff.ProductivityRate, tblStaff.Active, tblStaff.Address, tblStaff.TelNo, tblStaff.OtherContact, tblStaff.EmployeeType, tblStaff.NINumber, tblStaff.Production, [productivityrate]*8/100 AS HrsAvail, tblStaff.Code AS UnitID, [Surname] & "," & [initial] AS Unit, [hrsAvail]*5 AS [Hrs Per Week], Weekdaysminusholidays(Date()-Weekday(Date(),0)+1,Date()-Weekday(Date())+7)*[productivityrate]/100 AS [Days This Week], Weekdaysminusholidays(Date()-Weekday(Date(),0)+1,Date()-Weekday(Date())+7)*[hrsavail] AS [Hrs This Week], Weekdaysminusholidays(DateSerial(Year(Date()),Mont h(Date()),1),(DateSerial(Year(Date()),Month(Date() )+1,0)))*[productivityrate]/100 AS [Days This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Mont h(Date()),1),(DateSerial(Year(Date()),Month(Date() )+1,0)))*[hrsavail] AS [Hrs This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Int( (Month(Date())-1)/3)*3+1,1),(DateSerial(Year(Date()),Int((Month(Date ())-1)/3)*3+4,0)))*[productivityrate]/100 AS [Days This Qtr], Weekdaysminusholidays(DateSerial(Year(Date()),Int( (Month(Date())-1)/3)*3+1,1),(DateSerial(Year(Date()),Int((Month(Date ())-1)/3)*3+4,0)))*[hrsavail] AS [Hrs This Qtr], Weekdaysminusholidays(CDate("1/1/" & Year(Date())),CDate("1/1/" & Year(Date())+1)-1)*[productivityrate]/100 AS [Days This Year], Weekdaysminusholidays(CDate("1/1/" & Year(Date())),CDate("1/1/" & Year(Date())+1)-1)*[hrsavail] AS [Hrs This Year]
FROM tblStaff;

But never accounted for the breaks, 45minutes lunch.
I had further thought on this also which came to light the other day.
All my equations are based around an 8 hr day. One of our operatives only works 30hrs per week and I haven't accounted for overtime ie > 8hrs per day or > 40hrs per week.
At the moment I a m on another part of the database so I've shelved this part for later.

It would be nice though to see an example expression of:

[TimeEnd]-[TimeStart]-[BreakTime]

08:00 to 17:15 = 9.15 less 0.45 break = 8hrs

[HansV]

How do you get from 9.15 less 0.45 break to a result of 8 hours?

[D Willett]

Shhhhhhhhhhhhhh

The lads on the shop floor have never worked that one out <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
(This will cost me thousands if they find out.)

It should have been 08:30 to 17:15

[D Willett]

Hans
As ever, <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> , Nice to see you back in the seat <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

[D Willett]

Hans
Just had chance to try the two equations and this the result.
Any Idea's ?

[HansV]

<P ID="edit" class=small>(Edited by HansV on 26-Sep-03 17:12. Original version had DateSerial instead of TimeSerial.)</P>Since Access stores and calculates dates and times in units of 1 day, something like [End Time] - [Start Time] - 0.75/24 should work. Or use [End Time] - [Start Time] - TimeSerial(0, 45, 0). Of course, if these are expressions in the control source of a text box, you should precede them with an = sign.

[D Willett]

This will not do !! <img src=/S/scold.gif border=0 alt=scold width=50 height=15> <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

Only Kidding, Thanks <img src=/S/wink.gif border=0 alt=wink width=15 height=15>