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,Empname (autonumber,text, text)
Projects: Projectid,Projectcode,ProjectName (autonumber, text,text)
Roles: roleid,rolename (autonumber,text)
EmployeesProjects (autoid,empid,projectid,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.