Help with querying multiple entries into a group

[weevil]

I need to query for projects and show all accountable employees. I have the following table structure.

---tblProj-----
proj_id > Primary key
project

sample
1 build road
2 replace culvert

---tblEmp-----
emp_id > Primary key
employee

sample
1 Williams
2 Smith

---tblprojemp-----
proj_id > foreign key linked to tblProj.proj_id
emp_id > foreign key linked to tblEmp.emp_id

sample
1 1
1 2
2 2

My problem is that on project 1 Smith and Williams are working on it and on project 2 just Smith. I can query this and get a list of all projects that each employee is working on, but I need help on figuring out how to query for each project and get a list of employees on that project.

how do I get the following results

project responible responsible

build road Williams Smith
replace culvert Smith

rather than

build road Williams
build road Smith
replace culvert Smith



Any help or suggestions would be appreciated if I need to change my structure I am at a point that I still can.

thanks rt

 

[GoDawgs]

This seems like a crosstab query to me, although I'm not sure if that would work or not. What are you doing with this data? I ask this because if it is going into a report or something then what you ask isn't too tough, but if you want to just see the results formatted the way you ask in a query that will be a little tougher it seems like. Just checking...

Kevin

 

[rooterr]

Yes the only reason I need the information in this format is so that I can display it in a report. If there is an easy way to get this formatted in a report please let me know.

Thanks for the response

 

[GoDawgs]

Real quick...hopefully this will be enough...with the data you have just use that for the report, then in the report group by your project id...this will give you something like:

build road
williams
smith

replace culvert
smith

not quite sure how you would get williams and smith to be listed right next to each other actually, maybe someone else can help with that...but this is the way i go about making reports. hope that helps.

kevin