Page 2 of 6 FirstFirst 123456 LastLast
Results 16 to 30 of 85
  1. #16
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    I have incorporated all the changes that you mentioned in your previous reply.I have a few questions
    1) In the prev reply you have included value Limit in tblTimeofServiceLimits in "LimitType" you have mentioned Loweror Upper in parenthesis,I really did not understand how will I set that up as a criteria.Please can you clarify on it a bit more.


    2) Database has become so big that I am kind of loss please can you advice me how I should approach as far as entering data,creating various forms,switchboards,reports,queries etcetc is there anything that I should be aware of.
    I am still in the learning process of access so for this type of model is really a hard one to analyze but I am going to try my level best to make this work successfully.
    Also I will try to send you latest relationship chart just to be sure that I have connected them correctly.

    Thanks,
    Roger

  2. #17
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    I have attached the whole database that I have set up it includes the latest relationships that you mentioned.To be frank I am really lost with this database never done anything as complex as this. I have added some data in tbl employees and tbl position.
    I am trying to understand it as we build it but really not able to plan how I will go forward from here ,Please can you share your inputs as far how to set up forms etcetc.Also please can you check whether I have set the data type correctly also all the relationships.

    Thanks
    Roger
    Last edited by roger556; 12-15-2011 at 09:55 AM.

  3. #18
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    1) In the prev reply you have included value Limit in tblTimeofServiceLimits in "LimitType" you have mentioned Loweror Upper in parenthesis,I really did not understand how will I set that up as a criteria.Please can you clarify on it a bit more.
    For the text field you will only have one of two values: upper or lower
    This is the value you would enter in the text field. I opted to change that in the attached database. I created a table that holds the values upper and lower and just referenced a foreign key. This way you never have to type the words. This helps to prevent typing errors and reduces redundancy. To illustrate, I created a form with a subform that shows the work categoriess and the upper and lower limits for each. You would just select upper or lower from the combo box as it applies to the numerical value you are entering Just open up the form: frmWorkHourCategory. Based on your PTO chart you had the 20 to <30 hour category. So its lower limit is 20 and its upper limit is 30. We need these limit values to be numeric in order to do comparisons to actual hours worked by employees which in turn is used to determine the PTO rate.

    2) Database has become so big that I am kind of loss please can you advice me how I should approach as far as entering data,creating various forms,switchboards,reports,queries etcetc is there anything that I should be aware of.
    The tables have to be setup first because if you create forms and then change the tables on which they are based (as we have already done), the forms become worthless and you have to recreate them. The most important part of a successful relational database is the table structure (tables and relationships)!

    So, I would still wait on forms, we still need a table that will capture the actual paid time off used by employees. To that end, I assume that you typically capture the date, employee and the time taken, correct? If so, that table would look like this:

    tblEmployeePTOTaken
    -pkEmpPTOTakenID primary key, autonumber
    -fkEmployeeID foreign key to tblEmployee
    -dteTaken (date taken)
    -spTimeTaken
    -fkUOMID foreign key to tblUnitsOfMeasure

    And relative to my earlier question, if you do not keep track of hours worked, how can you determine the rate at which PTO accrues?

    I also cleaned up some other minor issues I saw in the database.

    You might want to take a look at this site, it has some good tutorials on not only table structure set up but also how you go about setting up forms based on that structure.

  4. #19
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Thanks a lot for fixing the relationship table now it is a lot better to understand the flow.
    And relative to my earlier question, if you do not keep track of hours worked, how can you determine the rate at which PTO accrues?
    I actually do not need to keep track of how many hours an employee has worked as I have use the PTO time as default which means irrespective of how many hours an employee has worked he/she is entitled for the default PTO as per grade and years of service.If you recall the word document I had attached in my earlier reply that has the matrix of PTO accured per pay period I will be using that as my rate of accural for different employees
    So in general terms even if the employee falls behind in number of hours he/she has worked they are still entitled for all their PTO's.
    I will create the table that you have mentioned in your previous reply.Please let me know if you need any further information.
    Thanks for all your help

    Roger

  5. #20
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Since I was under the impression that you were going to track hours, that was going to be the link to the appropriate PTO work hours category for the employee, so we will have to make that link in another way. Looking at the structure, a person is full-time if they work 40 hrs, if they don't then they would be considered part-time. Having full or part time is not as useful as having the actual hours category associated directly with the employee. So I think it would be best to replace the tblEmployeeTimeStatus table with tblEmployeeWorkHoursCategory. The basic structure would be the same as the tblEmployeeTimeStatus. I have made that change in the attached database.

    Now as to forms, you will need forms in order to input the predefined data that you had in your PTO chart. You can create a form/subform for the time of service and the associated year limits similar to what I did for the work hour categories. Essentially the form/subform would be identical with the exception of the tables to which the form/subform are bound. I usually use the wizard to create each form separately. For the subform, I usually create the form directly in a datasheet view. I would delete the control for the limittypeID and then use the combo box wizard to create a combo box based on the limit type table and the bind that combo box to the fkLimitTypeID field of the tblTimeofServiceLimits (which is the table to which the subform should be bound). Once you have the two forms created, open the main form (the one based on tblTimeOfService) in design view. Then highlight the form based on the tblTimeOfServiceLimits in the navigation pane and then drag and drop it into the main form. Access will automatically link the subform to the main form based on the relationship between the two tables that you set up in the relationship window.

    You can use a similar approach for positions and grades.

    You can use simple forms (no subform necessary) for tblSites, tblEmployee, tblGrades, tblUnitsOfMeasure. You will need a combo box in the employee form in order to populate the site for the employee.

  6. #21
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Thanks a lot for updating the database appreciate it.As per your instruction I have created tbls for employee,grades,sites.As usual I have questions.
    1) Since we are just using hours as the mode to calculate PTO time in tblunitofmeasure I would set it up as hours am I correct?
    2)In the database I have seen we have used tbl EmployeePositionGrade is that correct?
    3)If you are ok with it what I would like to do is built forms for the various tables and than I would like to post it so you can take a look and let me know if I doing it correctly.
    4) In tblWorkHrCatLimits Did you add the the different ID viz WorkHrCatID,LimitTypeID manually or the table picked it up due to relationship that was set between those table.
    5) I will also create tblEmployeePTOTaken.
    6) Do we need a some sort of calendar feature to keep as a tracker on some sort of dashboard or switch board.Please adivce.
    Please I would request you to bare with me as for me this is a complicated database so I have lots of questions and also I want to build it correct to avoid any future errors that may become impossible to fix.
    I will keep you posted and also upload the new database so that you can keep a track of what I have done.
    Once again thanks a lot for all your time and effort really appreciate it.

    Thanks
    Roger

  7. #22
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    As per your instruction I have created tbls for employee,grades,sites.As usual I have questions.
    I thought those tables were already in the database? Did you mean that you created forms based on those tables?

    Since we are just using hours as the mode to calculate PTO time in tblunitofmeasure I would set it up as hours am I correct?
    Hours would be just a record in tblUnitOfMeasure. If you need others down the road, it is easier to just add a record rather than having to redesign your table and then all associated forms, queries and reports.

    2)In the database I have seen we have used tbl EmployeePositionGrade is that correct?
    Yes, because you have to associate the employee with their position and grade (and work hour category) in order to know the PTO to which the employee is eligible.

    3)If you are ok with it what I would like to do is built forms for the various tables and than I would like to post it so you can take a look and let me know if I doing it correctly.
    Sounds good

    4) In tblWorkHrCatLimits Did you add the the different ID viz WorkHrCatID,LimitTypeID manually or the table picked it up due to relationship that was set between those table.
    When you set up the form/subform, Access will automatically populate the WorkHrCatID value when you start to add a record in the subform. The limittypeID is populated when you select a value from the combo box in the subform.

    6) Do we need a some sort of calendar feature to keep as a tracker on some sort of dashboard or switch board.Please adivce.
    That one is totally up to you and how you want to show the data to your users. I am guessing that it would be setup within a form. I personally have never set up a calendar feature, so that would be a good topic for a separate forum post.


    I typically have a main startup form (an unbound form-i.e. not tied to a table or query) with a bunch of buttons. Each button is named with a function that a user my want to perform such as "Enter a New Employee". When they click the button, it would take them to a form where they can do just that.

  8. #23
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    I am sorry I mean to say Forms.I will start working on various forms once I am done I will post hopefully by late evening today.

    Thanks
    Roger

  9. #24
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Just wanted to give you an update I have created most of the forms but I am kind of confused with some of the forms with regards to what data will go in to them example tblPosGrWkHrService,tblPosGrWkHrCategory,tblPosGrW kHrServicePTO,tblTimeofService,tblTimeof ServiceLimits.
    Also It seems to me that we might be repeating data in different tables please correct if I am wrong.
    Do I need to create forms for each table?
    The reason for me not posting earlier is I want to create all the forms and also if possible fill the tables with some data just to test it looks correct before I proceed any further.
    I attempted to create form and subform but it somehow did not work I am attempting to do it again today will post you with an update.
    Thanks a lot for all your help I can see the database coming out way better than I thought I could do.

    Thanks,
    Roger

  10. #25
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Please can you elaborate more on tblRestrictionDescription what should I incorporate as data in to this table if possible can you give me an example that would great.Also when I am updating a table it gives me a window whereby it wants me to select childID and mother ID I am not sure whether I should be linking any of the fields or tables please if you can direct me as to how should I handle this?
    The window I was referring to about is the link subdatasheet window I am not sure how should I use it? could you please shed some light on this.I do not want to link wrong tables and mess up the database.

    Thanks,
    Roger
    Last edited by roger556; 12-19-2011 at 11:31 AM. Reason: Update

  11. #26
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    tblPosGrWkHrService,tblPosGrWkHrCategory,tblPosGrW kHrServicePTO,tblTimeofService,tblTimeof ServiceLimits.
    These tables will be holding the details of your PTO chart that you presented earlier. You will the data in these table as a check to the employee data.

    Also It seems to me that we might be repeating data in different tables please correct if I am wrong.
    How so?

  12. #27
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Please can you elaborate more on tblRestrictionDescription what should I incorporate as data in to this table if possible can you give me an example that would great.
    From your PTO Description chart you had the following restrictions for a particular time status/years of service combination

    8 hrs/pay
    208 hrs max
    26 day max


    I was envisioning that the tblRestrictionDescriptions would have the following 3 records, that would just describe each restriction and then you would join that to the respective position/grade/work hour category to have a place to put the 3 values.

    max hours/pay
    max annual hours
    max annual days

    But, looking at the numbers, it is apparent that you only need 1 since the other two values can be calculated. 8 hours/pay * 26 pay periods = 208 hours and 208 hours/8 hours/day= 26 days. This changes the relationship from a one-to-many to a one-to-one.

    So I would get rid of the table tblRestrictionDescriptions and further you can eliminate the table tblPosGrWkHrSericePTO and move the PTO value and Unit of measure up to table tblPosGrWkHrService.

    I have done that in the attached DB.

  13. #28
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    I am a bit confused should I be using the latest database file that you have attached as I see they are missing lot of tables that we had previously.
    I just want to make sure I am on the same page as you are just to avoid any confusion.Also that way I can go about building the various forms to fill data into the tables fyi...if you check tblemployee and tblPositions I have already filled them with data please can you take a look and let me know it looks good.
    This PTO matrix is quite challenging how to incorporate all these variables between yrs of service,grades,hoursof servrice and PTO accurred.
    I would like to make a suggestion I am going to try to address to each table and than I will post it so that way you can let me know I am moving in the right direction,I just want to avoid any confusion and error.

    Thanks,
    Roger

  14. #29
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Attahced you will find copy of the latest database,Please can you check the realtionship table and check that it is correct.Also I have created some forms and added some data and also tried to connect some of the tables as subdatasheet.
    Please let me know if it is correct or not.

    Thanks,
    Roger

  15. #30
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Sorry for any confusion I may have caused with the databased I posted earlier. It was just to illustrate the table changes I was discussing.

    I did notice that you took care of those changes in the database you posted.

    When it comes to the PTO table data, it will remain essentially stagnant once it is entered. Typically if I were the only user, I would input the data directly into the tables and not do any forms for that part of it, but in a multi-user environment, your users should not have access to the tables; all interaction with the data has to be through forms.

    I typically do not use the subdatasheet feature of Access at all; I usually create the forms and leave the tables alone.

    For your position/grades I would typically use a main form based on either the position or grade table with a subform based on the tblPositionGrades. Assuming that binding the main form to tblPosition makes more sense, I would use a combo box in the subform to populate the grade. You can use this form to add new positions or edit existing ones and assign existing grades to the position. You would still need a separate forms to add new grades (which you already have). I used the same approach when I create the work hour category form.

Page 2 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