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:
Code:
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.