Using Query Results in a Table

[music_al]

I have a table which (to make things simple for now) has 2 fields in it...

Employee_Role
Employee_Name

I have another table (tbl_Quotation) where I want to enter various data, of which 2 fields are Employee_Role and Employee_Name.

Now it would be easy for me to Select the Employee_Name from a list of names using a ComboBox and to enter the 2nd column (Employee_Role) into a text box, but I want to do it the other way round.

I want the user to select the Employee_Role from a ComboBox (cbo_Emp_Role) and for a second ComboBox (cbo_Emp_Name) to list only the Employees of that type of role. I hope that makes sense.

I have tried a few different things to achieve this but none seem to work but some ALMOST do what I want them to do.

Can anyone help ?


Thanks in advance

Al

 

[namliam]

A usual design is to have 3 tables for this
1) Employee
2) Project
3) ProjectRole

As any given employee may not have the same role in each and every project ( or Quotation in your case ) This will (I think) resolve your issues.

 

[music_al]

Hi Mailman

No the employee will always have the same Role in each Quotation.

As Im a bit of a newbie to this, Im not sure I understand what you are suggesting.

Could you expand on this for me please ?

Thanks

Al

 

[DCrake]

Can Employee John Doe have role A in project ABC and have role C in project XYZ?

If an employee can only have one role at any one time then you need to add a field to your employee table which states their role ID

You would then have another table that contains all the known roles with a PK and description.

The use would select a role from the form based on this description, upon which your employee list would be filtered based on the role PK. Such As:

Select * From Employees Where RoleID = 3

This is known as cascading combos.

David

 

[music_al]

Ive solved it - almost accidentally