Query that searches by employee or item title located in different tables

[samvb]

Hi,

I have this database based on this logic: There are projects in a company. In each project, different employees different roles. One employee could have different roles in a single project. Also, can work in different projects at the same time. Some projects could be added but role of employees could be specified later on.

Employees: Empid,Empcode,E mpname (autonumber,tex t, text)
Projects: Projectid,Proje ctcode,ProjectN ame (autonumber, text,text)
Roles: roleid,rolename (autonumber,tex t)

EmployeesProjec ts (autoid,empid,p rojectid,roleid )

The database works fine. So far, the company was searching by project code or name. But recently, they want to enter employee name in the same textbox and that would return projects he participated on. Also, the projectname will be searched along with it.

So, say if projectname is named David's Residence and there is an employee named David (who participated in the project or not), it returns the project and all other projects that an employee whose name includes David has participated in under any role.

How can I do that? The query please.

[Rabbit]

If you join EmployeeProject s to the other three tables, that will give you all the fields you're looking for.

[samvb]

can u give me an example?

[Rabbit]

[CODE=SQL]Select fieldlist
From table1
Inner Join table2 on table1.FK = table2.PK
Inner Join table3 on table1.FK = table3.PK
Inner Join table4 on table1.FK = table4.PK[/CODE]