Change from days to hours

[jj72uk]

Ok, the title doesnt do justice to the question but its tricky to capture the problem in a line.

Attached is a simple DB that has been created for the purpose of managing holiday entitlment and booking for a small group of people. It also allows you to book appointments for each team leader, all which works correctly.

Within our business there has been a change of how holidays are worked, instead of taking a day off or a half day off its now down to hourly entitlement, so instead of getting a yearly daily entitlement they now get a yearly hourly entitlement.

All I want to change is 2 things.

i) On the right box, named holidays, it has the date, half day, maternity etc etc

What I want is the half day box to be changed to hours taken and then the entitlment recalulated correctly.

I cannot seem to figure this out.

ii) Not as important, but would be nice, is if a team leader tries to book a holiday when there is already 2 off on that day it will come up with a warning message or something similar.

Thanks for your quick responses.

 

[jzwp22]

In order to change from days (or partial days) to hours, it looks like only this query has to be changed: Qry_HolidayStatement. As shown in red, I changed the 0.5 to 4 and as shown in green I changed the 1 to 8.


SELECT BaseData.ID, Entitlement.Yearfld, HolidayBookings.DateBooked, HolidayBookings.HalfDay, HolidayBookings.[M/Paternity], HolidayBookings.Bereavement, IIf([Halfday]=-1,"4",IIf([M/Paternity]=-1,"0",IIf([Bereavement]=-1,"0",IIf([DateBooked] Is Null,"0","8")))) AS CalcData
FROM (BaseData LEFT JOIN Entitlement ON BaseData.ID = Entitlement.Id) LEFT JOIN HolidayBookings ON (Entitlement.Yearfld = HolidayBookings.Yearfld) AND (Entitlement.Id = HolidayBookings.ID);

I've attached a modified database that has the changes above incorporated as well as code to check for others booked on the same date in response to your 2nd question.

 

[NigelShaw]

Hi

I would have though a basic math equation would suffice as in

1 day = 24 hours
10 days = 240 hours

so

1 x 24 = 24
10 x 24 = 240
.5 x 24 = 12
.25 x 14 = 6

you could even calculate the minutes

divide by 60

and divide by seconds

divide again by 60

you can use the round() function to round up or down to the nearest whole number.

Nidge

 

[jj72uk]

In order to change from days (or partial days) to hours, it looks like only this query has to be changed: Qry_HolidayStatement. As shown in red, I changed the 0.5 to 4 and as shown in green I changed the 1 to 8.


SELECT BaseData.ID, Entitlement.Yearfld, HolidayBookings.DateBooked, HolidayBookings.HalfDay, HolidayBookings.[M/Paternity], HolidayBookings.Bereavement, IIf([Halfday]=-1,"4",IIf([M/Paternity]=-1,"0",IIf([Bereavement]=-1,"0",IIf([DateBooked] Is Null,"0","8")))) AS CalcData
FROM (BaseData LEFT JOIN Entitlement ON BaseData.ID = Entitlement.Id) LEFT JOIN HolidayBookings ON (Entitlement.Yearfld = HolidayBookings.Yearfld) AND (Entitlement.Id = HolidayBookings.ID);

I've attached a modified database that has the changes above incorporated as well as code to check for others booked on the same date in response to your 2nd question.

Very helpful... and that is what i did myself intially.

Like i said in my orginal post my company has changed from a daily holiday entitlement to an hourly one. So a employee could book for example 2 hours off.

Is there an easy way to lets say getting rid of the half day coloum, changing for a coloum which has amount of hours taken and then the overall entitlement recalculated??

I can't seem to find the additional coding for my second question and when I put in lets say 3 holidays on the same day no warning is made?

EDIT: Ok i've found the code for the more than 2 people booked on the same day but it doesn't seem to be kicking in? The DB is for access 2000 , is it becasue I am now home viewing it on 2007...?

 

[jzwp22]

Like i said in my orginal post my company has changed from a daily holiday entitlement to an hourly one. So a employee could book for example 2 hours off.

Sorry I misread your post. The only way to capture a variable number of hours is with a field, preferably with a number datatype (long or single precision). You cannot use a yes/no datatype as in the case of the Halfday field. This will require a you to change many of your forms, queries and reports sorry to say.


I have Access 2007 here at home too and did not have a problem with the code working.

 

[jj72uk]

How many forms queries etc would need to be changed? Alot? Or just a handful?

I've attached a test DB with 3 people all having the same holiday booked, I didn't get a warning message?

 

[jzwp22]

I'm still not having a problem with the warning. The warning will show when you actually try to enter a record with the same bookingdate if at least 2 already exist. You have to tab through all of the other controls on the subform or shift your focus to one of the other controls on the form outside of the booking subform for the code to trigger.

As to which other forms, queries, reports that will be impacted, I don't know without going through your entire database. I am guessing, but it sounds like you were not the person who designed this database. Am I correct?

 

[jj72uk]

Unfortuntly it was inherited :s Not to worry, I know of some work arounds which i can include.

Are you able to see on my second example the fact I'm not getting an error?

Maybe I'm doing something wrong.

One the holiday main sheet I'm choosing the employee, then in the right box I am entering the date required - eg - 20/09/2009

I hit enter and check the calanader, the entry has been entered.

I then repeat this process for an additioanl 2 employees and no warning ?

 

[jzwp22]

I think I know what's happening. Access stores dates in the mm/dd/yyyy format no matter what formats you use or what your computer settings are. Being in the US, I see the date you entered as 9/10/2009 (mm/dd/yyyy), so if I enter another record as 9/10/2009, the code triggers. I think you will have to use some formatting in the code I provided for it to trigger correctly based on your regional settings.

 

[jj72uk]

Ahhh explains the confusion!

Thanks

 

[jzwp22]

You're welcome. If you need some help redesigning the database to accommodate the hours, please let me know.

 

[jj72uk]

Will do! I'll look into what needs to be amended, hopefully not to much.

 

[jj72uk]

You're welcome. If you need some help redesigning the database to accommodate the hours, please let me know.

Looking into it I will consume to much of my time and my workstack is big as it is.

I was thinking of a quick fix and wonder whether this will be viable.

How about using the current check boxes ( maternity, half day etc) and adding some more to the table and amending the 'maths' part of the query to accomodate these additional enteries, so I could have a box for 1hr, 2hrs etc

 

[jzwp22]

I think you will be better off in the long run to just add 1 field to capture the actual number of hours. I think your queries will end up being easier than if you had a series of check boxes.

 

[neileg]

I think I know what's happening. Access stores dates in the mm/dd/yyyy format no matter what formats you use or what your computer settings are. Being in the US, I see the date you entered as 9/10/2009 (mm/dd/yyyy), so if I enter another record as 9/10/2009, the code triggers. I think you will have to use some formatting in the code I provided for it to trigger correctly based on your regional settings.

This isn't actually true. Access stores dates as a decimal number with the integer part being the number of days since 31 Dec 1899 and the fractional part, the time as a fraction of a day. It then formats this as a date 'on the fly'.

Access tries to handle dates according to the regional setting on your PC. However, SQL expects dates in US format, and this can sometimes cause an issue. I tend to use and unambiguous format such as dd-mmm-yyy to get around this.

 

[Atomic Shrimp]

Hi

I would have though a basic math equation would suffice as in

1 day = 24 hours
10 days = 240 hours...

People don't work 24 hours a day - I think this question is about allocation of working hours, rather than just hours of the day.

 

[jj72uk]

Thats right, if you download the db you can see the managers allocation.

EG they have 180 hours of holiday to take.

Looking into it I would have to chnage from the check boxes to an input a number as not all managers work and 7 hour day, some work a 6 hour day one week, a 8 hour day another week...

Looks like I'd need to strip the db back bare :s

 

[jj72uk]

I think you will be better off in the long run to just add 1 field to capture the actual number of hours. I think your queries will end up being easier than if you had a series of check boxes.

Any pointers where to start?

 

[jzwp22]

I restructured the tables based on my earlier suggestions and the suggetions of others. I also included primary key fields in all tables. I also added a leave type table to take care of how the time off is to be categorized rather than having multiple yes/no fields. This will allow future flexibility without having to modify your forms/queries and reports when management wants to capture some new leave type. I added a main form and a report to get you started. I added the code to check if anyone else has booked time off on the same date. If it doesn't trigger when you try to add a new record to the bookings table, you'll have to change the format of the date field in the code to match the long date format I set up in the table. The new DB is attached.

 

[jj72uk]

Wow...just...wow.

I don't know what to say!

Would it be easy for me to re add in the calander feature?

I know the report you made shows all the details in regards to that managers entitlement, but can i also add something on the main form to show what that managers entitlement is left without having to run the query to get the report up?

And I'm guessing to add in some of the old reports is easy enough!

EDIT
One thing...I dont know if its me... but on leave type if i put it as vacation/holiday then try and generate the report it says no holidays have been booked, though if I put it down as sick it happy accepts it? typo maybe?