Paid time off database

[jzwp11]

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.

A form can only be based on 1 record source; that record source can be either a table or a query.

You can change a form from data entry only to not data entry only (view/edit/enter) as it opens. You just alter the data entry property. This takes one line of simple code.

I generally split my database into front and back ends. The back end contains only the tables while the front end contains everything else. This effectively allows you to double the size of the database. An Access 2010 database has a 2GB max size if I remember correctly so splitting it gives you 4GB. If you are not saving any files in the database, it will probably take quite a while to reach the 2GB limit. In terms of deleting old records, it may be better to archive them in another database (with the same structure), but you would probably only have to do this every 5-10 years.



You will need a few queries in order to take care of some of the other forms you will need. Have you tried to create any SELECT queries yet to see how they work?

[roger556]

Hello,
Yes I did try to create one I used the simple query feature but used fields from multiple table to pull data together but it did not work.
I was trying to pull data from tblemployee viz last name,firstname,from tbl employeepositionrelate I was trying to pull the executiveemployee information.Idea is to able to search for employee info that will include manager's name,schedule,timetaken,datetaken,typeofPTO.
I did tried to pull above set of information but it did not work.I am still trying to play around with it so far no success apparently it is not picking up the data.Also since the executive employeeid has numbers so when I create a view form ,under manager it gives me the number but it does not pick up the executive name.
Any ideas

Thanks,
Roger

[jzwp11]

To get the name as well as the number you have to include the name field in the query.

I typically use the query design mode rather than the simple query wizard. You can add the tables and it will show the join lines. In the lower grid you select the fields you want to see in the final query. A point of caution, if a related record is not present in a particular table, the query will not return a result. That is why having enough test data is important. You will want to make sure that you have populated all of the tables with say one or two employees' data in order to end up with a query that will return a result.

[roger556]

Hello,
I have been trying to create a add data form and so far it has been tough to come up with ideas.Please any suggestions or ideas would be very helpful.
No rush whenever you get a chance.

Thanks,
Roger

[roger556]

Hello,
I have a question keeping tblEmployeePositionGrade and tblEmployeePositionGradeRelate please can you explain to me or show an example how will I pull the manager's or supervisor's name for a particular employee.If possible if you could create a form showing the information of a employee with its supervisor that would be awesome.

Thanks,
Roger

[jzwp11]

I have been trying to create a add data form and so far it has been tough to come up with ideas

For what table is the form giving you a problem? Can you be a little more specific?

I have a question keeping tblEmployeePositionGrade and tblEmployeePositionGradeRelate please can you explain to me or show an example how will I pull the manager's or supervisor's name for a particular employee.If possible if you could create a form showing the information of a employee with its supervisor that would be awesome.

In the attached database, I have a form called frmManagersAndTheirEmployees that shows the managers in the main form with the employee who report to them in the subform. I just added some test data to a couple of tables in order to get some data to show in the form. I had to create 3 queries in order to bring the data together that I needed.

[roger556]

Hello,
Awesome now that makes sense I was just not able to get those table together to pull the necessary data you made it look pretty simple.
I am going to add some more data into the table so I can work with them more in detail I will keep you posted.

Thanks,
Roger

[roger556]

Hello,
I have planned to create 3 forms to address to end users requirements.
1) Add data Form,2)Edit data Form,3) SearchForm that will also have capabilities of creating different forms.
Now with regards to first form this is most likely data I intend to incorporate to create the form.
1)Employee Information viz:firstname,lastname,dateofhire,position,grade,y earsofservice,Supervisor'
s name,schedule,shift,site,totaleligiblePTO,totalcar riedforwardPTO,totalPTOtaken,totalPTOremaining.
2)Edit Form will include all the above information plus it has to be able to change the employee status in terms of employment position,grade change,change in years of service,change in number of accured PTO,change in supervisor.
3)Search Form should be capable of searching all the information that we have included in the parameter form and also should be able to print a report stating that data.
Please I have been trying to incorporate all these information but so far I have not been successful in one form I am able to retrieve part of the information but cannot get all of them incorporated it looks like it may be due to relationship has not been set up in some of the data.
I did try to pull that data together in a query form but that also fails.I tried to create a Add form incorporating all the fields that I have mentioned above but it does not work.
Please any advice or suggestion in the meanwhile I will try to research further and will keep you posted with my progress.

Thanks,
Roger

[roger556]

Hello,
Attach is the new copy of the database please when ever you get a chance take a look at it and please feel free to make corrections.
I have created few queries please let me know if they look correct.I have also created forms as per your previous reply.

I have quite a few questions it seems there are lots of tables that need to be connected through queries I have been playing around with them but I am not very successful especially creating queries between tblTimeofService,tblJobClassTimeofService,tblTimeo fServiceLimits,tblLimitType,tblJobClassifications, tblJObClassificationGrade,tblworkhourCategoryLimit s,tblworkhourcategory,tblpto.
Correct me if I am wrong I think I have to work on the lines of how you created queries for tblEmployeePositionGradeRelate.Please let me know if my thinking is correct.
I have some other questions but I do not want to go ahead with it as it will create lot of confusion I think first I need to create all the necessary queries before I even attempt to create the main forms.
Looking forward to your reply.

Thanks,
Roger

[jzwp11]

I'm not really sure what you are after with the queries you created, so you will have to explain what data you really wanted to see.

I did see an issue in your structure that I did not catch before. You assign a shift, site and a schedule to a person but a schedule is also tied to a shift and site. Is that intentional? If you assign a schedule to someone, do you still need to assign them a shift & a site as well?

3) SearchForm that will also have capabilities of creating different forms.

A form cannot create another form, but you can use the information selected in one form to find the applicable record(s) and show those result records in another form.

1)Employee Information viz:firstname,lastname,dateofhire,position,grade,y earsofservice,Supervisor'
s name,schedule,shift,site,totaleligiblePTO,totalcar riedforwardPTO,totalPTOtaken,totalPTOremaining.

I have captured most of the above items in frmEmployee in the attached revised DB.

Of the above items, total eligible PTO is pulled from another table(s) (a Dlookup() function should help with that). Total PTO carried forward, total PTO taken, PTO remaining and years of service are all calculated values. I've done a couple for you in frmEmployee.

[roger556]

Hello,
Thanks a lot for your response there is no special intention other than I just wanted to be specific with sites,schedules and shift that a particular employee is working just keeping in future needs as we may include our other site data also later on in the future.
I have attached a pdf file that is showing a sample of a form in our old database I was thinking of building something on similar lines just to keep the continuity.
Please let me know if that is something that can be done with our database and current relationship that we have set up.

Thanks,
Roger

[jzwp11]

If the site and the shift are related to the schedule and the schedule is tied to the employee, then you do should not have the site and shift in the employee table. Doing so would violate normalization rules. So I would suggest removing those fields from the employee table.

[roger556]

I have made the change in tblemployee now I still need to leave scheduleID as fk in tblemployee. Please let me know.

[jzwp11]

now I still need to leave scheduleID as fk in tblemployee

You are correct

[roger556]

Hello,
Attach you will find the most updated database.Please let me know if I have to make any changes or updates.

Thanks,
Roger