Please help me develop this query that searches projects by their title or employees

[crazycat503]

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.

Using access query wizard, I was able to get this done:

SELECT title
FROM projects INNER JOIN (roles INNER JOIN (employees INNER JOIN EmployeesProjects ON employee.employeeid=EmployeesProjects.empid) ON roles.roleid=EmployeesProjects.roleid) ON projects.projectid=EmployeesProjects.projectid;

it works fine but returns projects who have information saved in EmployeesProjects table. That is, any project whose inforamtion about employees and their roles in it is not returned.



As an e.g., i like to have something like the search enginine of beemp3.com where user can search for a song thru its title, album or artist. but the result is always songs.

[weekend00]

If you mean that when you type in "David's Residence" and search for all David's projects, it's hard to to identify which word is a name of a person (David in this example), maybe you want to search each word in your employee table? do you want to just match any word of a name (first name, last name, mid name)? I think it's too hard and doesn't make any sense doing that if I understand it right.

[crazycat503]

If you mean that when you type in "David's Residence" and search for all David's projects, it's hard to to identify which word is a name of a person (David in this example), maybe you want to search each word in your employee table? do you want to just match any word of a name (first name, last name, mid name)? I think it's too hard and doesn't make any sense doing that if I understand it right.

you actually didnt get me but i managed to solve it. thank you.