Page 6 of 6 FirstFirst 123456
Results 76 to 85 of 85
  1. #76
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I see in the attached database that you added the TimeOfServiceID field to tblEmployee. That is incorrect. As I have said before, the time of service needs to be calculated since the value changes constantly. With your approach of having the TimeOfServiceID field in the employee table, you would have to check each employee EVERY DAY to see if their time of service changed from one category to the next. You might be able to accomplish this for a handful of employees, but when you have 10, 20, 40, 2000 employees the task gets much more difficult and time consuming.

    I did realize that we needed to define an employee's work hour category, so I did add that field to the employee table. I assigned everyone as full time; please change as necessary.

    I also noticed that you removed the frmEmployee I created the other day. I have imported that back into the attached copy of the database along with the related queries and subforms. I have added several new controls to frmEmployee. You will now see controls that show how the calculated time of service is used to lookup the PTO allowance for the employee (with the help of some other queries) along with the remaining PTO balance. Since the data related to the PTO is calculated or looked up, you cannot edit the values in those controls.

    For any new days taken, you would add a record in the frmEmployeeTimeOff subform within frmEmployee. The PTO controls will not update until you close & then reopen frmEmployee. We can add code to refresh the values but I did not want to add that complexity until you have a better understanding of where the controls are getting their data. The three controls labeled as Employee JobClassID, EmployeeGradeID and EmployeeTimeOFServiceID are just lookup values that are needed to get the PTO allowance. I would normally hide these controls from the user, but I left them visible for you to see them. Not all records will have values in the controls because you have insufficient test data in your database. (employee ID's 1 and 32 have sufficient data)





    Now based on your earlier post with the form you want to mimic, that form is definitely possible but it would be a stagnant form since all of the controls you show are marked as unbound (assuming that the form itself is not bound to a table but I think you will have to at least bind it to the employee table at a minimum). You would use the same approach as I used in frmEmployee using Dlookup() and DSum etc. to pull in the data you want to display. You would also replace the subforms I show with lookups as well.
    Last edited by jzwp11; 12-30-2011 at 04:03 PM.

  2. #77
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    I have a found a error frmEmployee it seems that subform (frmEmployeePositionGradeRelate) is not matching with EmployeeID in the form example: employeeID#32 should have manager information but instead it is getting picked up by EmployeeID#3.
    Please can you let me know if I have messed up something in tblEmployeePositionGrade,tblEmployeePositionGradeR elate,tblPositionGrades,tblPosition.
    I am trying to check it but so far not successful.Will keep you posted.

    Thanks,
    Roger

  3. #78
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    I think the error is arising because of the mother child link that has been created in the subform.

    Thanks,
    Roger

  4. #79
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You are correct. To correct the issue, I changed the record source for the subform. Instead of being based only on the tblEmployeePositionGradeRelate I changed to to a query that included that table and the tblEmployeePositionGrade and added the EmployeeID field and then corrected the form/subform linkage. The amended DB is attached
    Last edited by jzwp11; 12-30-2011 at 04:03 PM.

  5. #80
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    I have added remaining data for the rest of the employees,certain dates I have used as default so they are not correct but I think it should not effect our calculations.
    Following are the issues that I have come across while going to through verification of the data.
    1)Part time calculations for employeeID#3,4,12,20 are not correct somehow the formula is taking their hire date as the first year resulting in wrong PTOcount it is somehow igonoring the number of years they have worked.
    2)Employee#18 somehow PTO is getting calculated for that employee still trying to figure out why it is doing that.I think the issue is with employees with grade9.
    3) Also frmEmployeePositionGradeRelate is not picking supervisor-Employee relationship do you think I will have to create a seperate tbl for supervisors just to be able to create the relationship.
    4) Also should I create a table for recording carried forward PTO as they will also have to be included in the new default totals.

    Please let me know.

    Thanks,
    Roger

  6. #81
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    1)Part time calculations for employeeID#3,4,12,20 are not correct somehow the formula is taking their hire date as the first year resulting in wrong PTOcount it is somehow igonoring the number of years they have worked.
    The PTO data was actually incorrect. For the Non-exempt/grades 1-6 there were 2 sets of entries for 1-5 years with the the data for <1 year in one set and the correct data in the other set. I just changed the one 1-5 year set to <1 year.

    2)Employee#18 somehow PTO is getting calculated for that employee still trying to figure out why it is doing that.I think the issue is with employees with grade9.
    I did not link the time of service with the job class in the qryTimeOfServiceWithLimits. I corrected that. There also was a data issue that I took care of as well.

    3) Also frmEmployeePositionGradeRelate is not picking supervisor-Employee relationship do you think I will have to create a seperate tbl for supervisors just to be able to create the relationship.
    No need for another table. In qryManagers, I filtered to just show positions with the word "manager" in it. I have adjusted to include manager or supervisor. I did not however make changes to qryNonManagers, so supervisors are still included in that query. I did not know if you wanted this query to only reflect those employees who were not managers and not supervisors. You'll have to make the call there and adjust the query as necessary.

    4) Also should I create a table for recording carried forward PTO as they will also have to be included in the new default totals.
    No, it is a calculated value from the previous year, so you will have to input the PTO for 2011 and then in 2012 have a query calculate any carryover from 2011. Once you do that it should be automatic moving forward.

  7. #82
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    I have a question since we have started a new year do I have to save database for previous year and start a new database for the new year or is it going to remain continious.
    I haven't set up a database like this before so do not have much idea as to how would I handle this situation.
    With regards to carrying forward remaining PTO from 2011 since that is being tracked in a old database I will have to somehow add manually in to the current database.Any recommendations as to how should I go about doing it.
    Also I wanted to create something for the front end part of the database what would be the best approach in terms of creating different types of form ex:search form for employee's view,a form only for the supervisors to edit,add.update and delete data.Also I would like to give the options of running different type of reports and would like to include it either in to the switchboard or in the start up menu.
    Also, I do intend to create different layers of access for different employees.Would you recommend for me to use access own security features or should I go the route of creating a custom password user log in feature.Please advice.

    Thanks,
    Roger

  8. #83
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I have a question since we have started a new year do I have to save database for previous year and start a new database for the new year or is it going to remain continious.
    Only 1 database is needed. On the frmEmployee, I used a function that pulled the current year based on the system date. (using the date() function in Access)

    With regards to carrying forward remaining PTO from 2011 since that is being tracked in a old database I will have to somehow add manually in to the current database.Any recommendations as to how should I go about doing it.
    What type of database?
    One easy way is to import the data into a new table and then use append queries to move the data into the structure we created.


    Also I wanted to create something for the front end part of the database what would be the best approach in terms of creating different types of form ex:search form for employee's view,a form only for the supervisors to edit,add.update and delete data.Also I would like to give the options of running different type of reports and would like to include it either in to the switchboard or in the start up menu.
    That is just a matter of creating the forms. You would tie forms for which a user can use via their login credentials which typically would require a custom login routine of some sort. What I have done, is to create forms and then tie those forms to buttons on a main switchboard. When a person logs in and based on their privileges they would only see certain buttons on the switchboard that correspond to what they are allowed to do. You can assign privileges based on the person's job title or whatever scheme you want. I would typically have a table that holds all of the functions/buttons (button caption and button name). Then in a related table associate each button(s) which each type of job title (ie a junction table). Then when a person logs in, you check their title open the switchboard with their particular grouping of buttons.

  9. #84
    roger556 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    80
    Hello,
    Just wanted to update you I am currently working on creating forms for the end user only.Once I am done with that than I will start working on switchboard and definitely on some sort of Custom Userlog In form.

    Thanks,
    Roger

  10. #85
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    It sounds like you are progressing, so I think that it is time to mark this thread as solved.

    If you come across any specific form or custom user login issues in the future then it would be best to start a new thread so that you can have others offer their advice/experience as well.

    Best of luck on your project.

Page 6 of 6 FirstFirst 123456
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