Page 3 of 6 FirstFirst 123456 LastLast
Results 31 to 45 of 85
  1. #31
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,


    Thanks a lot for clearing lot of stuff it has made things a bit easier.Yep I also since at this moment I am the only user I would like to put the data directly into the tables rather than creating any forms for it,Once I am done entering data than I can set up a overall form that will incorporate all the data that needs to be entered.
    I have a request to make if you can spare some time is it possible for you to list what specific data will go into each of the tables that we have in the database,I just want to be sure that I am adding correct data into each table.
    Also for calculations like amount of PTO etcetc I will accomplish that through queries please correct me if I am wrong?
    Once again thanks for having the patience in teaching me how to create this database really appreciate it.

    Thanks,
    Roger

  2. #32
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I generally start out with the outermost tables in the relationship diagram. In your database, that would be the following tables: tblGrades, tblEmployee, tblLimitType, tblSites, tblPositions, tblTimeOfService, tblUnitsOfMeasure, tblWorkHourCategory. Most of these you already have set up. I added data to tblTimeOfService for you (see attached). The tblTimeOfServiceLimits (also completed) is set up much like the limits for the work hour categories, just using the limits specified in your PTO chart (<1 year, 1-5 etc.). I did notice in tblPositions that you repeated the position several times. You only need to have 1 record for each position (I deleted the duplicates). Once you have the positions and grade tables populated, you need to relate the grades applicable to each position in tblPositionGrades. Once you have that table populated you can then bring that information together with the work hour categories in tblPosGrWkHrCategory. Since you can have any position/grade for which you can have any employment status, you need to capture all possibilities, so full-time for an exempt grade 1 position, for exempt grade 2 position etc. then 30-<40 hours exempt grade 1, exempt grade 2 etc.

    That will take some time to populate, so I will stop there for now. Please let me know if you have any questions.

  3. #33
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    In thinking about things overnight, I see a flaw in my logic. I'll post back in a couple hours with more details, so hold off doing any data entry for now.

  4. #34
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Ok I will put on hold until you instruct me to move forward.

    Thanks,
    Roger

  5. #35
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Sorry for the delay, but I reworked several things. Since your PTO chart is technically based on the job classifications (non-exempt, 2 exempt classifications and the executive) and only loosely based on the grades, I decided to add the job classifications to the database. Further, the time of service breakpoints in your PTO chart are different for each classification whereas the work hour categories are constant throughout. So I ended up relating the classifications to the time of service first and then joined that combination to the work hours to come up with the final PTO table. I assume that each position has a job classification, so you will have to add that information to the position table. I have added a combo box to frmPositions for easier data entry. Once you have added that information, then you can establish the relationship between the tblJobClassifications and tblPositions.

    I also added a table that relates the applicable grades to each job classification. (tblJobClassificationGrade). Of course, since the executives do not have grades, that required a different type of join in the relationship diagram.

    Regarding the Time of Service, I went ahead and added the additional times of service that I was missing and added the limits accordingly based on your PTO table.

    I then manually added the data to the tblJobClassTimeService junction table via the subform within frmJobClassifications.

    I then created a query that took all the records in tblJobClassTimeService and all of the records in tblWorkHourCategory (since all job classifications and their times of service have the same work hour categories). I then changed the query type from a select query to an append query and appended the key fields of both tables to tblPTO. I left the query for you to look at, but please do not run the query otherwise you will add duplicate records to tblPTO.

    I then added a subform to the subform in frmJobClassifications or in other words a subsubform. That subsubform is based on tblPTO. I then entered the max annual hours to each work hour category for each service time within each job classification thus completing the PTO chart data entry. You can look at frmJobClassifications and step through the various form and subform records to see the data. I also added another subform to frmJobClassifications which shows the applicable grades for each job classification. As I said earlier, this data will stay essentially stagnant, so you will not want any other users to edit data in this form (if and when you have other users).

    The revised database is attached.

  6. #36
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    First of all thanks a lot for helping me out with this database really appreciate all the work that you have put into it.Since you have made these changes I will try to go through it and grasp the concept it will take me atleast an hour I will get back to you with lots of questions.
    Please bare with me on that if you recall I had asked you a question whether I am correct in doing all the calculations through a query?
    Please let me know about it.
    As for other questions I will get back to you .

    Thanks,
    Roger

  7. #37
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Please bare with me on that if you recall I had asked you a question whether I am correct in doing all the calculations through a query?
    Yes, you will need to use queries to do the calculations (summing current hours taken, comparing to max hours allowed + any rollover, calculating rollover from year to year etc.)

  8. #38
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Couple of initial questions
    1) We do have tbl EmployeePositionGradeRelate that will identifybetween managers and regular employees now do I have to give unique numbers to executive and regular employee within the table to be able to distinguish them example:1-Manager,2-Supervisor,3-Balancer,4-Inclearing etcetc.
    2) Also I have incorporated 2 more tables tblShift and tblSchedule reason for that is in the near future this database could be modified to be used as a schedule planner etcetc.Also I wanted to give a option to the manager to be able to pull the schedules for each or group of employees.

    Please can you share your thoughts or opinions.

    Thanks,
    Roger

  9. #39
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    1) We do have tbl EmployeePositionGradeRelate that will identifybetween managers and regular employees now do I have to give unique numbers to executive and regular employee within the table to be able to distinguish them example:1-Manager,2-Supervisor,3-Balancer,4-Inclearing etcetc.
    No because the employee is tied to the position via other tables. I would include the executives in the employee table and the positions in the positions table and then relate them to their respective positions in the tblEmployeePositionGrade table. You will have to alter the join type in the tblPositionGrades since executives are not assigned a grade. Or you can just set up a dummy grade for all executives (no pun intended).

    2) Also I have incorporated 2 more tables tblShift and tblSchedule reason for that is in the near future this database could be modified to be used as a schedule planner etcetc.Also I wanted to give a option to the manager to be able to pull the schedules for each or group of employees.
    Since I do not have any other details, I do not know if just the two tables will be sufficient. I will let you proceed on your own. Hopefully you can use some of the knowledge gained to work out the structure on your own. I'll help if needed, of course.

  10. #40
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Thanks a lot for your response I will leave them as seperate and will follow your instructions ,once I am done I will post it so you can take a look at it.
    I do not really need to connect shift and schedule to our current database it is just some additional data I am providing so as long as I have the table I can always pull the data through the form to display that information.
    I will keep you posted as to how far I have reached and ofcourse will post questions as I proceed with loading the data into the tables.

    Thanks,
    Roger

  11. #41
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Sounds good.

  12. #42
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    I can use grade 12 as the paygrade for executive.That should resolve the problem.Please advice.

    Thanks,
    Roger

  13. #43
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    As long as it meets your business rules, then it is OK. You will have to change the join I made earlier between tblJobClassificationGrade and tblGrades. I had it set to type 3, you will need to change it to type 1 and then enforce referential integrity.

  14. #44
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Just wanted to give you an update as to where I have reached so far I have made the changes that you recommended in your previous reply.I have also started entering data into some of the tables.Still a bit confused with tblEmployeePositionGradeRelate but will come back to it when I really have to work on it.
    I have a question is it possible to incorporate some sort of table that will keep track of actual dates when a employee has taken PTO for a particular day/month and how many hours example: 30 mins.1 hours,8 hours.Also wanted to know I also have to incorporate leave such as sick leave,bereavement,Jury duty so how should go about doing it do I need to create a new table keeping track of type of leave.If so please any suggestions.
    I am sorry to be adding all these stuff at such later time but cannot help things change a lot with the requirements and do not have the liberty of saying no you know how it goes in a workplace.

    Thanks
    Roger

  15. #45
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I have a question is it possible to incorporate some sort of table that will keep track of actual dates when a employee has taken PTO for a particular day/month and how many hours example:
    There is already a table in the structure for this (tblEmployeePTOTaken)

    -EmployeePTOTakenID
    -EmployeeID
    -Taken (date field I assume)
    -TimeTake (express all values in hours)
    -UOMID

    Also wanted to know I also have to incorporate leave such as sick leave,bereavement,Jury duty so how should go about doing it do I need to create a new table keeping track of type of leave.
    No, you would not need another table for the other types of time off, I would just alter tblEmployeePTOTaken to simply tblEmployeeTimeOff. Of course you will need to identify what type of time off the employee is taking (PTO is just one type). I would create a table to hold all of the various types of times off

    tblTimeOffType
    -pkTimeOffTypeID primary key, autonumber
    -txtTimeOffType

    You would have a record for each type in the above table: sick leave,bereavement, Jury duty, PTO etc.

    I would alter the employee time off table as follows:

    tblEmployeeTimeOff
    -EmployeeTimeOffTakenID
    -EmployeeID
    -Taken (date field I assume)
    -TimeTake
    -fkTimeOffTypeID foreign key to tblTimeOffType
    -UOMID

    You may want to make a rule that all time off is recorded in hours or fractions of an hour rather than mixing units of meaure. If so, you would not technically need the UOMID or you may allow any Unit of measure, but then you will have to do the conversions ahead of any comparisons or calculations you may need to do. You will have to make the call on that. If you decide to have all time recorded in hours for all tables requiring a time value, then you would not need the tblUnitsOfMeasure and the associated linkages to other tables that we have already discussed.

Page 3 of 6 FirstFirst 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Overall price paid
    By MFS in forum Reports
    Replies: 1
    Last Post: 07-02-2011, 07:19 PM
  2. Obstruct database after A specified time
    By d_Rana_b in forum Programming
    Replies: 3
    Last Post: 04-04-2011, 07:35 AM
  3. Mark invoice paid
    By kylebmorris in forum Reports
    Replies: 1
    Last Post: 07-19-2010, 12:39 PM
  4. Real time database question
    By joet5402 in forum Forms
    Replies: 7
    Last Post: 04-01-2009, 09:00 PM
  5. PTO Time Management Database
    By Stanggirlie in forum Programming
    Replies: 1
    Last Post: 03-27-2009, 09:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums