Timesheet Database

[James Bailey]

Hello all... Nice forum you have here.

I'm in the process of developing an Access 2003 Timesheet database. I'm basing it off of an excel spreadsheet the company already uses. This spreadsheet has the charge codes as columns, the date and hours worked as the rows.

I'm trying to work out the best way to design the timesheet details table. At first, I was going to have the following (condensed version, FK, PK left out):

Table: TimesheetDetails
Fields: Charge Code, Date, HoursWorked.

Hours Worked will be a number between 1 and 24.

The problem is that for each date the employees have at least two charge codes per day. I quickly realized that entering timesheet data would take too much time.

All that has led me to think about creating a new field for each day of the week called MonHours, TueHours, etc. Then I can setup an excel like form that has the days of the week as columns and the charge codes and hours worked as the rows. This way, they choose the charge code once and can enter hours under any day of the week for that code. Also, I can setup a combo box of pay periods and have the dates auto filled for them based on their selection.

I can't help thinking there is better way to design this. I've considered putting the charge codes across the top but then I run into the problem of horizontal growth if new charge codes are created. At this point I'm out of ideas so I thought I'd ask here.

Any and all opinions appreciated!

 

[Rabbie]

I am sure I will not be the only person to say this. Make sure you understand normalisation. It will make everything else much easier. Don't be afraid to have a lot of tables if necessary. Remember that a form can have data from more than 1 table.

 

[James Bailey]

I am sure I will not be the only person to say this. Make sure you understand normalisation. It will make everything else much easier. Don't be afraid to have a lot of tables if necessary. Remember that a form can have data from more than 1 table.

Rabbie,

Thanks for the reply.

I feel that I have normalized the data the best I could to this point. The TimesheetDetails table is the child of another table called Timesheet. I have another table for TimesheetChargeCodes, and another for Employees. A couple fields I haven't mentioned are location and ticket number. I don't think I can normalize the location field because the locations are so random, there are so many and vary so much that I believe it could be an unwieldy combo box in the near future. We also have a stack of about 10,000 Service Reports so the TicketNumber field would be HUGE. How should I handle fields like that?

Do you (or anyone) think that having the days of the week as fields in the TimesheetDetailID table could cause a problem in the future?


Thanks again!

 

[The_Doc_Man]

Whatever else you do, DON'T go with MonHours, TuesHours, etc. You will nail yourself to the wall with that one. It is a direct violation of first normal form to do that kind of table design. No, the database design cops won't arrest you. But your own design will be judge, jury, and executioner for you. And your sentence will be too many late night hours fighting with a totally impractical and intractible design.

If you have not read up on normalization before, do so now. If you have, go back and look up 1NF - the part about REPEATING GROUPS. Read it until you understand what you proposed and why it won't help you in the long run.

 

[James Bailey]

Thanks for the reply. I haven't read a whole lot on normalization yet. I'm reading a book called Inside Relational Databases with Examples in Microsoft Access, which is an excellent, easy to read book. It has brought a lot of stuff I knew about into sharper focus. This is my first database to design from the ground up so I want to get it right. I've maintained and worked with databases (SQL, Access) for about 6 years now. Normalization will be the next chapter I read about.

 

[Maximnl]

Split database design and form design

Database is made for efficiency of keeping the data , not for entering data.
You need to use form for this.

I was kind of busy with the same things . your original design is ok
Table: TimesheetDetails
Fields: Charge Code, Date, HoursWorked.
Just add a field employee id.

Entering form per week per day can be easier to realise the way you want it if you have a good date dimension, that would simplify conversion of week number and day of the week into the date.

Let me know if helps,
maximnl@gmail.com

Regards
Maxim

 

[Rabbie]

As minimum you will need an Employee Table and a TimesheetDetails table
Employee table will hold details of Employee that don't change on a weekly basis.
PK for table, Name,address, employee number, Salary etc

TimesheetDetails
Fields: Charge Code, Date, HoursWorked.
Just add a field employee id. This will hold the PK from employee table.

With this you can easily build a query that will give you the hours worked by each employee each week

 

[harleyskater]

I would take out Hoursworked and date and then I would use a clockin datetime and a clockout datetime. you can use datediff function to get the hours automated so there is no math. Each Charge Code is for a seperate clockin?

Hello all... Nice forum you have here.

I'm in the process of developing an Access 2003 Timesheet database. I'm basing it off of an excel spreadsheet the company already uses. This spreadsheet has the charge codes as columns, the date and hours worked as the rows.

I'm trying to work out the best way to design the timesheet details table. At first, I was going to have the following (condensed version, FK, PK left out):

Table: TimesheetDetails
Fields: Charge Code, Date, HoursWorked.

Hours Worked will be a number between 1 and 24.

The problem is that for each date the employees have at least two charge codes per day. I quickly realized that entering timesheet data would take too much time.

All that has led me to think about creating a new field for each day of the week called MonHours, TueHours, etc. Then I can setup an excel like form that has the days of the week as columns and the charge codes and hours worked as the rows. This way, they choose the charge code once and can enter hours under any day of the week for that code. Also, I can setup a combo box of pay periods and have the dates auto filled for them based on their selection.

I can't help thinking there is better way to design this. I've considered putting the charge codes across the top but then I run into the problem of horizontal growth if new charge codes are created. At this point I'm out of ideas so I thought I'd ask here.

Any and all opinions appreciated!

 

[James Bailey]

Each charge code is a different code that a technician uses while out on a job.

Charge codes include Straight Time Work, Straight Time Travel, Overtime work, OT Travel, holiday, sick, vacation.

Currently they find the column in the timesheet spreadsheet that they need to clock hours for, go to the date row, and put in the number of hours for that charge code they worked that day.

Here's how my initial design looks:

tblEmployees
fields: EmployeeID, FirstName, LastName, City, State

tblTimesheetChargeCodes
fields: TSChargeCodeID, TSChargeCodeDescription

tblTimeSheet
fields: TimesheetID, EmployeeID, PayPeriodID

tblTimesheetDetails
fields: TimeSheetDetailID, TimesheetID, TSChargeCodeID, Location, Date, TicketNumber, HoursWorked


I was under the impression that I could get the number of hours worked per employee without having EmployeeID in the tblTimesheetDetails table. Do I need to rethink this and put an employee id with each TimeSheetDetail record?

Thanks for all of the replies and help on this!

 

[James Bailey]

Here's a screenshot of the relationships I've got setup so far. The two tables to the side that start with AR are from the accounting software we use. I might try to put something together later on that will allow someone to click on a ticket number to see the details of the job from the accounting system.

 

[JEA]

I'm trying to do a similar thing, I spent ages on the 'old' db to no avail. I changed the table structure to the 'new' version. So far it's making things easier, can anyone spot any pit falls I might face?


Edit:
Have changed the new table structure since I posted this, nearly finished the whole db now. I'll post a copy of the finished project when it's ready.

 

[James Bailey]

I'm trying to do a similar thing, I spent ages on the 'old' db to no avail. I changed the table structure to the 'new' version. So far it's making things easier, can anyone spot any pit falls I might face?

JEA,

I can't answer your question but it might be in your best interest to start a new thread. While our questions are similiar in nature (at least that they pertain to timesheet design ), they are different enough that this thread could get cluttered very quickly. Also, people will be able to focus on your problem only which could help you get more answers.

James

 

[JEA]

JEA,

I can't answer your question but it might be in your best interest to start a new thread. While our questions are similiar in nature (at least that they pertain to timesheet design ), they are different enough that this thread could get cluttered very quickly. Also, people will be able to focus on your problem only which could help you get more answers.

James

I suffered similar problems to you with a similar database. My problems were solved by normalising my tables. The two .png's were intended to show you how I did this. I have not yet had any problems with this structure.

I asked people to check it as I didn't want to give you flawed advice. I'm satisfied it works, and will help with your original post, but I'm no expert.

I'm sorry if I gave the impression that I was trying to hijack your thread.

 

[James Bailey]

Thanks for the info. I thought you were asking for help with a problem.

 

[JEA]

Jea,

What did you do to ensure that one user couldn't access another users timesheet? Also what design did you decide on for your forms?

Thanks!

I'm replying to the pm here as it wouldn't let me send such a long pm.


In my db it's the supervisor that enters the data for everyone, so access to each other's data isn't an issue.


1.) My initial thoughts would be to crate a table of staff ID's and passwords.

2.) Create 2 forms. One's going to be the 'log on', the other's going to be the time sheet.

3.) Create a query that uses a perameter (staff ID) to get all the records for a specific staff member from your 'hours' table..

4.) If you want to rearange the format of the result of this query before displaying it, then crate a crosstab query on the original query.

5.) On the 'log on' form create 2 text fields. One for the employee ID, the second for their password. On the same form create an 'OK' button. In the code for this button, using the on click event, lookup the staffID/passwords table to see if they match the ones on the form. If they do match get it to open the second form.

6.) On the second (time sheet) form, use your query (or crosstab if you've used one) as a sub form, just drag and dorp it from the navigation menu on to the form. Use the staffID on the 'log on' form as the perameter.

I hope that makes sense.That's just off the top of my head. It wouldn't be secure, but if your users aren't computer literate it might be enough.

I've no idea if it'll work, but it's a start.

I've done something similar, on my 'Enter Hours' form but I use a perameter on the same form to display data for a specific date rather than staff member. Have a look at my 'enter hours' form. Look at the code for the 'Find Date' button. Look at the 'Hours Worked' query and the Hours worked crosstab query. Also Ive changed my table structure since I posted those .png's.

 

[JEA]

6.) On the second (time sheet) form, use your query (or crosstab if you've used one) as a sub form, just drag and dorp it from the navigation menu on to the form. Use the staffID on the 'log on' form as the perameter.

Sorry, I was talking crap there. You can't use a crosstab as a subform.

How are you getting on? I've done quite a bit more to mine.

 

[James Bailey]

Sorry, I was talking crap here. You can't use a crosstab as a subform.

How are you getting on? I've done quite a bit more to mine.

It's taken a back seat to some financial reports I've had to design over the past week for an audit, which is just as fun

I plan on hitting it once I get some more free time, though.

 

[James Bailey]

Sorry, I was talking crap there. You can't use a crosstab as a subform.

How are you getting on? I've done quite a bit more to mine.

I've finally got around to finalizing the design, creating the forms, and am hoping to put it to use soon. I don't have anything in place for security yet, but I think it's only going to be used by a data entry clerk for now. Later, if they decide to let the employees start using it, I'll go ahead and add security.

Thanks for your design. It was very helpful.