Tuff Query help

[Boss]

I have a master table with details of projects running in my company. A
project has four member team. My table looks like this...

Project ID/Project Champion/Project Leader/First Member/Second Member/Status

Under project champion, project leader and member field I put emp ID.
Status has three options, Under process, Finished and Certified.

A emp can be a project champion in one project and project leader in second
project or a member in third project.

I need to run one query which tells me the count of the project an emp is
part of and the status of those projects. Is this possible?

I am looking for somthing like this...

Emp ID participated/champion/Leader/member/Under process/finished/certified
71796 count count count count count count count
71854 count count count count count count count


O tried crosstab query but it only works for one criteria... please help me
get up to the result..
Thanks a lot..

Thanks!
Boss

 

[John W. Vinson]

I have a master table with details of projects running in my company. A
project has four member team. My table looks like this...

Project ID/Project Champion/Project Leader/First Member/Second Member/Status

Under project champion, project leader and member field I put emp ID.
Status has three options, Under process, Finished and Certified.

A emp can be a project champion in one project and project leader in second
project or a member in third project.

But no project can ever possibly have five members, with your design. That's
both a real limitation on your flexibility, and a non-normal design; you're
storing data (a person's role) in a *fieldname*, instead of as the value of a
field.

A more normalized and more flexible design recognizes that you have a many to
many relationship between projects and employees:

Projects
ProjectID
ProjectName
<description of the project>

Roles
Role <Text, Primary Key, e.g. "Champion", "Leader", "First Member", "Second
Member", "Squeaky Wheel", "Devil's Advocate">

Eomployees
EmployeeID
<all the usual fields, you've go this>

Membership
ProjectID
EmpID
Role

A team with four members has four *records*.

I need to run one query which tells me the count of the project an emp is
part of and the status of those projects. Is this possible?

I am looking for somthing like this...

Emp ID participated/champion/Leader/member/Under process/finished/certified
71796 count count count count count count count
71854 count count count count count count count


O tried crosstab query but it only works for one criteria... please help me
get up to the result..

Only works for one criterion because of your non-normalized design! You can
"untangle" your current structure using a Normalizing Union Query:

SELECT ProjectID, "Champion" AS Role, [Champion] AS EmpID, Status
FROM yourtable
WHERE [Champion] IS NOT NULL
UNION ALL
SELECT ProjectID, "Leader" AS Role, [Leader] AS EmpID, Status
FROM yourtable
WHERE [Leader] IS NOT NULL
UNION ALL
SELECT ProjectID, "First Member" AS Role, [First Member] AS EmpID, Status
FROM yourtable
WHERE [First Member] IS NOT NULL
UNION ALL
SELECT ProjectID, "Second Member" AS Role, [Second Member] AS EmpID, Status
FROM yourtable
WHERE [Second Member] IS NOT NULL

You can then base a Crosstab query on this (using the row sum as
Participated).

 

[Boss]

This makes lot of meaning to me..

Thanks a lot for your help..

Thanks!
Boss

I have a master table with details of projects running in my company. A
project has four member team. My table looks like this...

Project ID/Project Champion/Project Leader/First Member/Second Member/Status

Under project champion, project leader and member field I put emp ID.
Status has three options, Under process, Finished and Certified.

A emp can be a project champion in one project and project leader in second
project or a member in third project.

But no project can ever possibly have five members, with your design. That's
both a real limitation on your flexibility, and a non-normal design; you're
storing data (a person's role) in a *fieldname*, instead of as the value of a
field.

A more normalized and more flexible design recognizes that you have a many to
many relationship between projects and employees:

Projects
ProjectID
ProjectName
<description of the project>

Roles
Role <Text, Primary Key, e.g. "Champion", "Leader", "First Member", "Second
Member", "Squeaky Wheel", "Devil's Advocate">

Eomployees
EmployeeID
<all the usual fields, you've go this>

Membership
ProjectID
EmpID
Role

A team with four members has four *records*.

I need to run one query which tells me the count of the project an emp is
part of and the status of those projects. Is this possible?

I am looking for somthing like this...

Emp ID participated/champion/Leader/member/Under process/finished/certified
71796 count count count count count count count
71854 count count count count count count count


O tried crosstab query but it only works for one criteria... please help me
get up to the result..

Only works for one criterion because of your non-normalized design! You can
"untangle" your current structure using a Normalizing Union Query:

SELECT ProjectID, "Champion" AS Role, [Champion] AS EmpID, Status
FROM yourtable
WHERE [Champion] IS NOT NULL
UNION ALL
SELECT ProjectID, "Leader" AS Role, [Leader] AS EmpID, Status
FROM yourtable
WHERE [Leader] IS NOT NULL
UNION ALL
SELECT ProjectID, "First Member" AS Role, [First Member] AS EmpID, Status
FROM yourtable
WHERE [First Member] IS NOT NULL
UNION ALL
SELECT ProjectID, "Second Member" AS Role, [Second Member] AS EmpID, Status
FROM yourtable
WHERE [Second Member] IS NOT NULL

You can then base a Crosstab query on this (using the row sum as
Participated).