Help with Table Design for Employee Task Database

[shelbsassy]

Hello,
I am fairly new to Access and have been trying to create a relatively simple database that will track how many hours employees spend on a specific task. Each employee has a billing rate so for the task I would need a calculation (hours * rate).



I have tried a few templates but nothing seems to work or when I try to customize it, I break something. Ive done a lot of online reading for the last month on this and just bought a huge Access book, but I am wondering if someone can tell me if I am on the right path.

These are my tables:
Employee Table with EmpID, EmpLastName, EmpFirstName, EmpRate
Task Table with TaskID, TaskName

I need a Form where I select the Employee and enter the date, select the Task from the List, enter the number of hours, and it will calculate automatically the cost based on that Employee's billing rate.

I'm trying to figure out the WorkDone table that will link everything together and store the data that is input into the form.

I was thinking EmpID, TaskID, Date, Hours, Cost

Would a join from Employee to WorkDone and a join from Task to Work Done work? How does the data that is entered into the form get into a table?

Any guidance would be appreciated. Thank you!

Shel

[orange]

Take a look at this one.
http://www.databaseanswers.org/data_...home/index.htm

Change the Project to Task, and you could drop the Addresses and possibly others.
What do you need that isn't ther?

[shelbsassy]

Thanks for the link, that looks great! I guess what I am trying to figure out is how the input data gets written to a table. I was thinking I have a new table called WorkDone which would have the fields EmpID, TaskID, Date, Hours, Cost but I don't know how to get that updated.

[orange]

What data do you have?

WorkDone is determined via Table Employee_On_Project
You will need info for
Employees and Tasks(Projects)

How did you see Employees recording their time ( what process??)

[shelbsassy]

Thank you for your quick response. This is how I envision it:
I have a form with Employee Name (concatenate EmpFirstName and EmpLastName) as a drop down. When the employees name is selected, in a box called Rate, the Employee's Rate automatically populates. Then there is a dropdown box with the TaskName in it, a box for the date the work was done, and then a box to input how many hours was spent on a task. Then there would be a box that calculates the hours worked on that task by the employees rate. Then the user clicks an Update button or something like that to commit to another (?) table. This table I was thinking was called WorkDone which would hold the EmpID, TaskID, WorkDate, and Cost (the calculation from Employees rate x Hours on that task).
Does that make sense?

[kennejd]

I'm trying to figure out the WorkDone table that will link everything together and store the data that is input into the form.

I was thinking EmpID, TaskID, Date, Hours, Cost

Would a join from Employee to WorkDone and a join from Task to Work Done work? How does the data that is entered into the form get into a table?

I think you're on the right track here. Create your work table the way you envision it. If you use the lookup wizard in the table when you add the EmployeeId and TaskId fields, you can link back directly to those tables.

You can create a new form based on this new table and these lookup fields will automatically be created as combo boxes. You can use either the bound form that is the default or you can turn it to an unbound form. The unbound form method will require a fair bit of coding to make your record updates.

[kennejd]

To populate the rate box on your fomr will require a bit of code, but not too much. Let me know when you reach that point and I'll help you.