Paid time off database

[roger556]

Hello,
Thanks a lot for quick response appreciate it.I will make the necessary changes and also create those new tables ,I will also post the updated version of new database for you to take a look at it.I will also follow single unit of measure that is recording PTO times only in hours.That way I will be able to get rid UOMID and also make the database a bit simpler and uniform across the tables.

Thanks,
Roger

[roger556]

Hello,
I have made the changes in the relationship and also got rid of UOMID as we will be following on hours as the standard time mode.
I am also attaching the database so you can check it if I have made correct changes.Please let me know.

Thanks,
Roger

[jzwp11]

The structure looks OK. I did see a problem in tblEmployeeTimeOff. you have the TimeTaken field as a date/time data type. The data type needs to be a either a single or double precision number datatype. I assume the Taken field is for the date on which the time off is taken.

[roger556]

Hello,
TimeTaken in the table represents number of hours,Taken is the date should I change it to DateTaken?I was wondering if I have to do something on the lines datefrom/datetill,Please let me know.

Thanks
Roger

[jzwp11]

Taken is the date should I change it to DateTaken?

Your call on that. If it helps make it clear for you then go ahead and change it. When you create forms, you can always change label to something more meaningful for your users so the actual fieldname will not matter.

I was wondering if I have to do something on the lines datefrom/datetill,Please let me know.

If you do from/until dates, what would happen if an employee takes partial days off say 36 hours (4.5 work days), if you just have the beginning and ending dates, you would not know which day they took the 0.5 days (4 hours), and you would be mixing your units of measure days versus hours. How you capture it also depends on your business rules. In the present setup, you would add 5 records to the employee time off table for the employee 4 dates at 8hr/day and then a 5th date with 4 hours. It might be more records to add, but I think it will give you fewer headaches in the long run.

BTW, you mentioned holidays at one point. You will need a table to capture those

tblHolidays
-pkHolidayID primary key, autonumber
-txtHoliday
-dteHoliday (the date of the holiday)

[roger556]

Hello,
Ah! some nice points it makes sense,I have no problem with the number of records as long as the records are clean,clear and consistent.Also one another point with regards to holidays each employee has to take PTO for public holidays.So I do not think I need a holiday table example: For christmas each employee has to take PTO for example this year it is on sunday so any employee working on sunday will have to log in a PTO to get a holiday.This applies to all the employees including executives.Please let me know if I still need to make tblHoliday

Thanks,
Roger

[jzwp11]

Ah! some nice points I will create that table and also it makes sense,I have no problem with the number of records as long as the records are clean,clear and consistent.

You already have a table for the time off. So if you do not need a holiday table, what table were you going to create?


If every employee has to take PTO for a holiday such as Christmas, you could have a holiday table and use it to do a global append query for all employees; this would save you the trouble of entering a record for each employee manually.

[roger556]

I have very limited knowledge with append query but I will give it a shot.I will keep you posted.

Thanks,
Roger

[jzwp11]

It is best to get the table(s) set up for it and the worry about the append query later.

The append query would not be too hard. You would add a record to the time off table for each employee listed in the employee table or more accurately all active employees in the employee table (you would use a query to select the active employees). That brings up a point, you will need to identify those people who are active employees versus non-active employees (i.e. no longer employees). Might I suggest adding another date field to the employee table that indicates the employee's end of employment date or inactive date (whatever you want to call it). If there is not date entered in this field, the employee is active.

[roger556]

Hello,
Ok I will go ahead and add a date field into tblEmployee.Also I will create tblHoliday.Did you get a chance to read my message that I sent out to you?

Thanks,
Roger

[roger556]

Hello,
Attach is the most updated database please let me know if it looks correct especially tblEmployeePositionGradeRelate.

Thanks,
Roger

[jzwp11]

Looks good. Relative to the table, it is saying that Linda (site manager) is Courtney's supervisor effective 1/7/2011 and Courtney is the ops manager.

[roger556]

Hello,
I have added most of the data that I could manually,I have deliberately left some data field empty as I would like to fill them up through a form.
Please whenever you get a chance please take a look at it and let me know if I have to make any changes.
Also needed some guidance with regards to building a form my intention is to build a form that will incoporate all the features of adding new data,editing data and deleting.I am trying out few ideas but finding it difficult to incorporate everything into 1 form.
I wanted to also include search menu on the same form including different reports that can be run through this form.
I am planning keeping the end user in mind as most of them are not computer savy so I need to keep everything on one form.
Please any suggestions or advice is more than welcomed.

Thanks,
Roger

[jzwp11]

So far so good.

Also needed some guidance with regards to building a form my intention is to build a form that will incoporate all the features of adding new data,editing data and deleting.

You cannot use 1 form to add/edit/delete data in all tables. I usually have a form for each function and then a menu form where the user chooses the function they want to perform and/or allow to perform. You may even set up priveleges so certain users are only allow to do certain functions.

I find that trying to put everything in 1 form is more confusing for the user (just my opinion). If a person can navigate a website to order stuff, they should be able to navigate through the database to do what they need to do. It is up to database designer (you) to provide an easy way for them to do that.

By the way, I never allow users the ability to delete records. It is better to just make them "inactive" in some way. In fact, in most of the databases I have designed, I do not even delete records unless there is a very specific problem with a record.

[roger556]

Hello,
Yep I agree that it may get complicated for a layman end user.I will keep them seperate and list them on a switchboard.
I also like to retain data in the database I could always do Frequent maintenance through some sort of delete query.
I have a question so in this case the add /edit form will actually be exactly the same only they are being run through different queries.
Judging from your reply I think the next step for me would be to create queries,Please any suggestions.

Thanks,
Roger