Update mulitple employees in table based on Project#


G

Guest

I have a current form that selects a store from a drop down list that fills
the store info. The rest of the form is manually filled out with Project#,
Hours and employees working on the project. It is set up where the employees
are picked out of a drop down list and put into a list box. When saved, each
employee has a line in the table for this project.

Now instead of having just an Hours field they want a Projected Hours when
the project is first entered, and then a Total Hours which to be entered
after the project is completed. I'd like to set up a form where they could
select the Project# from a drop down list and then enter the Total Hours.

My question is how do I get it to update the total hours field of all the
employees that worked on the project? If I create a drop down list from the
Table, it will list the project number 4 times if there were 4 employees
working on this Project.

Is there a simple way to do this or is this going to involve a lot of
coding(which I don't have much experience in)?
 
Ad

Advertisements

G

Guest

Supe,

If I understood you correctly you have a table which has the following
fields project and employees.

The table looks like:
Projects Employee
1 x
1 y
1 z
2 x
2 y
2 z

What you would like to achieve is a combobox which list only the projects (1
and 2). To do so you can set up the rowsource of the combobox with the SQL
command DISTINCT. The SQL would be like this SELECT DISTINCT PROJECTS from
TABLE1.

By the way I would like to comment that for your database design it would be
better to have a table with projects and another table where the hours are
recorded.

HTH
 
G

Guest

That's only part of the issue. In your expample below, emplyee x, y, z are
working on project 1. On my entry form that are all given a Procted Hour
amount before saving so will look like below:

Project Employee Projected Hours Actual Hours
1 x 8
1 y 8
1 z 8


After the project is completed, the Actual Hours will need to be entered.
When I use SELECT DISTINCT it only lists the project number once, but it only
updates the Actual Hours on one employee. Need it to update all employee
that worked on Project 1.
 
G

Guest

Supe,

If all the employees should have the same number of actual hours you can use
an update query.

The SQL should look like this:
UPDATE Projects SET Projects.actual hours = 10
WHERE Projects.project =1));

This will update the actual hours for all records for project 1.
HTH
 
Ad

Advertisements

G

Guest

Yes!!! That worked. Thank you so much.

Brotha Lee said:
Supe,

If all the employees should have the same number of actual hours you can use
an update query.

The SQL should look like this:
UPDATE Projects SET Projects.actual hours = 10
WHERE Projects.project =1));

This will update the actual hours for all records for project 1.
HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top