show data from two tables with some calculation in a from

[Jihad Al-Jarady]

Hello guys,

I am trying to build a small database for my business,

I have 3 tables:

Projects:
- ProjID
- ProjName

Employees:

- EmpID
- EmpName
- EmpRate

Work:

-ID
-ProjID
-EmpID
-Date
-WorkingHours
-overtime
-deposit
-credit

The relationship between table as in the image 

As a first step, I need to create a form as in the attached image:

at the top, we can select a project by Number, then the form shows only the employees who assigned for this project, once I choose the employee from the combo box (employee ID), it shows the employee name in the text box next to it, and in the table blow it show all the records from table work, and add a column balance that shows the difference between the credit and deposit

Many thanks 

[#2]

@Jihad Al-Jarady 

The appropriate design for this is a main form with a subform in it.

The main form can be bound to either the Project table or the employee table, but I would expect it to be more useful to bind it to the Project table.

The subform is bound to the Work table. The ID and ProjID fields in the subform do not need to be displayed, but do need to be in the subform. The employeeID in the subform is selected with a combo box and the other fields are entered as appropriate.

The design you sketched out, with both the employeeID and ProjectID on the main form won't work out in practice due to the relationships between the tables.

[#3]

thank George for your quick answer, what is your suggestion regarding this:

The design you sketched out, with both the employeeID and ProjectID on the main form won't work out in practice due to the relationships between the tables.

[#4]

My suggestion is that you use the main form/sub form design approach instead.