Solved Managing times in a database (1 Viewer)

james7705

Registered User.
Local time
Today, 06:53
Joined
Aug 2, 2012
Messages
36
Good day...
I am hoping someone can assist or point me in a direction with regards to managing times in a database.
Allow me to explain...
I have 3x tables...
tblHolidays which contains "HolidaysID", "HolidayName" and "HolidayDate"
tblEmployees which contains "EmployeesID", "EmployeeName" and "EmployeeLevel"
tblWorkingHours which contains "WorkingHoursID", "EmployeeName", "Date", "StartTime", "EndTime", "@1", "@1,5", "@2" and "LostTime"
What I am attempting to do is...

For level 1 Staff
On a weekday i.e. Monday thru Friday any time worked other than between 08h00 and 16h30 needs to be captured in a separate column i.e. "@1"
unless the date matches a date in tblHolidays, in this case if hours worked is less than 8 then 8 hours of worked time needs to go into a column "@1".
If hours worked is more than 8 then total hours of worked time needs to go into a column "@1".

Any hours worked on a Saturday needs to go into the "@1" column

On a Sunday, if hours worked is less than 4 but more than 0, then 4 hours will be logged in the "@" column, else total hours worked will be logged in "@1" column

For level 2 Staff
On a weekday i.e. Monday thru Friday any time worked other than between 08h00 and 16h30 needs to be captured in a separate column i.e. "@1,5"
unless the date matches a date in tblHolidays, in this case if hours worked is less than 8 then 8 hours of worked time needs to go into a column "@1".
If hours worked is more than 8 then 8 hours of worked time needs to go into a column "@1" and the rest must go into a column "@2".

Any hours worked on a Saturday needs to go into the "@1,5" column

On a Sunday, if hours worked is less than 4 but more than 0, then 4 hours will be logged in the "@2" column, else hours worked will be logged in "@2" column.

TIA
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 04:53
Joined
Jul 9, 2003
Messages
16,245
It's best to keep it all in one column.

For more details see:-

 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:53
Joined
Oct 29, 2018
Messages
21,358
Hi. Total hours worked shouldn't be in the table because it's a calculated field.
 

james7705

Registered User.
Local time
Today, 06:53
Joined
Aug 2, 2012
Messages
36
Hi. Total hours worked shouldn't be in the table because it's a calculated field.
So I should split that table into a new table containing only the calculated times? And it will relate to the tblWorkingHours table I assume?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:53
Joined
Oct 29, 2018
Messages
21,358
So I should split that table into a new table containing only the calculated times? And it will relate to the tblWorkingHours table I assume?
No. You would simply remove the number of hours worked from the table and don't store it in any other table. Instead, you would create a query to calculate the hours and then use that query in your forms or reports.
 

james7705

Registered User.
Local time
Today, 06:53
Joined
Aug 2, 2012
Messages
36
Ok...simple enough...
Now the question is...what do I use? I tried if statements, but that doesn't work...too many variables
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:53
Joined
Oct 29, 2018
Messages
21,358
Ok...simple enough...
Now the question is...what do I use? I tried if statements, but that doesn't work...too many variables
You may have to create a function to do the calculation.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
think first before you dive into coding.
ask yourself first.
what is the Existing system you have?
if you are doing it manually or by worksheet (excel), how do you accomplished each time?
if somebody is doing it before, talk to the guy about the process.
what are the papers (forms) used.
how it is processed?

this is called System Analysis.
you don't do a Slum dunk approach.
take a paper and pencil and scribble the current system.

if there is an existing excel file, thats the good starting point.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:53
Joined
Feb 19, 2002
Messages
42,981
Also, before you go any further, you might consider changing your column names if the names you posted are actual column names.
Date is the name of a function and therefore will cause strange problems if you use it in code without proper qualification.
Naming fields @1, @1.5, @2 might be cute but is really poor practice. Column names should not contain special characters or embedded spaces nor should they ever be the names of functions or reserved words.

Storing calculated values is not a good idea. As others have mentioned, you should probably create three functions and do the calculations on the fly. However, this could lead to historical anomalies though if the rules have to change, therefore, in this case, it could make sense to store calculated values. That allows your function to always only calculate based on current rules. The alternative is to make the calculation based on a date range. That way even if the rules are different today than they were last year, your code would have two sets of rules. One used for timesheets entered prior to 1/1/20 and one used for timesheets entered after that date. I do not like having the four calculated columns as separate fields. Total, NEVER needs to be stored since it is always a simple subtraction of end date - start date in minutes. You then convert the minutes to hours. So, 90 minutes = 1.5 hours

So, the options for the other three rates are three columns or 1- three rows (standard, time and a half, double time) But, other companies pay triple time so I would think that rows ultimately works better because it gives you more flexibility. I would keep the timecard table separate from the hours worked table. So, when the timecard table (this is the record of the start and end time) is processed, your code creates 1-n records depending on how many different categories of time you have in the RecordedTime table. The recorded time table would be used for payroll and other processing.
 

james7705

Registered User.
Local time
Today, 06:53
Joined
Aug 2, 2012
Messages
36
Also, before you go any further, you might consider changing your column names if the names you posted are actual column names.
Date is the name of a function and therefore will cause strange problems if you use it in code without proper qualification.
Naming fields @1, @1.5, @2 might be cute but is really poor practice. Column names should not contain special characters or embedded spaces nor should they ever be the names of functions or reserved words.

Storing calculated values is not a good idea. As others have mentioned, you should probably create three functions and do the calculations on the fly. However, this could lead to historical anomalies though if the rules have to change, therefore, in this case, it could make sense to store calculated values. That allows your function to always only calculate based on current rules. The alternative is to make the calculation based on a date range. That way even if the rules are different today than they were last year, your code would have two sets of rules. One used for timesheets entered prior to 1/1/20 and one used for timesheets entered after that date. I do not like having the four calculated columns as separate fields. Total, NEVER needs to be stored since it is always a simple subtraction of end date - start date in minutes. You then convert the minutes to hours. So, 90 minutes = 1.5 hours

So, the options for the other three rates are three columns or 1- three rows (standard, time and a half, double time) But, other companies pay triple time so I would think that rows ultimately works better because it gives you more flexibility. I would keep the timecard table separate from the hours worked table. So, when the timecard table (this is the record of the start and end time) is processed, your code creates 1-n records depending on how many different categories of time you have in the RecordedTime table. The recorded time table would be used for payroll and other processing.
Hi Pat

Is it possible for you to assist me with the code...I have no idea how to even start as the current format I am using is on an excel spreadsheet with a bunch of statements in the formula bar that does all the calculations for me.
The problem with this is that I have a ton of excel forms for each of my departments and for each month.

My biggest confusion is doing the calculations.

TIA
 

jdraw

Super Moderator
Staff member
Local time
Today, 00:53
Joined
Jan 23, 2006
Messages
15,364
james7705,
There are several articles in different formats in the Database Planning and Design link in my signature. The videos by BA_Experts are short and humorous and hit the key points of analysis.
Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:53
Joined
Feb 19, 2002
Messages
42,981
James,
We aren't anywhere near code. Settling on a valid design for your tables needs to be done before we can move on. We're up to three experts telling you to back up to the tables and make sure they work. I mentioned some other possibilities for time which would not be accommodated by your table. Just because you don't pay triple time today, doesn't mean the company won't have to next week if their situation changes.

Taking off your spreadsheet hat and trading it for a relational database hat can be a challenge but it is where you need to start.
 

james7705

Registered User.
Local time
Today, 06:53
Joined
Aug 2, 2012
Messages
36
Thanks for all the advice...
I have managed to create working functions for what I have in mind
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:53
Joined
Oct 29, 2018
Messages
21,358
Thanks for all the advice...
I have managed to create working functions for what I have in mind
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom