Group by 2 columns ?

[chemicals]

I am trying to show Project Managers on a report using Group By...which
worked great because the records had only one field to group on "PM".

Now we have switched to a primary (PM1) and secondary (PM2) Project Managers
and I would like to still show on the report all project records that each PM
is assigned to (whether their name is in the Primary or Secondary field)

I have tried this SQL but It doesn't Group by each person:
SELECT ProgramName, ClientName, LiveDate, ShutdownDate, PD1, PD2,
ProductStage, ProgramStage
FROM Programs
WHERE (ProgramStage <> 'Closed') AND (ProgramStage <> 'Ops Phase 1')
AND (ProgramStage <> 'Ops Phase 2')
GROUP BY ProgramName, ClientName, LiveDate, ShutdownDate, PD1, PD2,
ProductStage, ProgramStage

 

[Bob Barrows [MVP]]

I am trying to show Project Managers on a report using Group
By...which worked great because the records had only one field to
group on "PM".

Now we have switched to a primary (PM1) and secondary (PM2) Project

Do you mean PD1 and PD2?

Managers and I would like to still show on the report all project
records that each PM is assigned to (whether their name is in the
Primary or Secondary field)

I have tried this SQL but It doesn't Group by each person:
SELECT ProgramName, ClientName, LiveDate, ShutdownDate, PD1, PD2,
ProductStage, ProgramStage
FROM Programs
WHERE (ProgramStage <> 'Closed') AND (ProgramStage <> 'Ops Phase
1') AND (ProgramStage <> 'Ops Phase 2')
GROUP BY ProgramName, ClientName, LiveDate, ShutdownDate, PD1, PD2,
ProductStage, ProgramStage

First use a saved union query to fold the data:

SELECT ProgramName, ClientName, LiveDate, ShutdownDate
, PD1 Manager, "Primary" As ManagerType,
ProductStage, ProgramStage
FROM Programs
UNION ALL
SELECT ProgramName, ClientName, LiveDate, ShutdownDate
, PD2 Manager, "Secondary" As ManagerType,
ProductStage, ProgramStage
FROM Programs


Save the query as "qryFoldedManagers". Then do this:

SELECT ProgramName, ClientName, LiveDate, ShutdownDate
,Manager,ProductStage, ProgramStage
FROM qryFoldedManagers
WHERE (ProgramStage <> 'Closed') AND (ProgramStage <> 'Ops Phase
1') AND (ProgramStage <> 'Ops Phase 2')
GROUP BY ProgramName, ClientName, LiveDate,
ShutdownDate,Manager,ProductStage, ProgramStage

 

[KARL DEWEY]

Now we have switched to a primary (PM1) and secondary (PM2) Project Managers
How does PM1/PM2 relate to PD1/PD2? Are they the same thing?
If so then use --
GROUP BY PD1, PD2, ProgramName, ClientName, LiveDate, ShutdownDate,
ProductStage, ProgramStage

It would be better if you have one field for their name and another for
their designation.

 

[chemicals]

This works...THANKS.

One last question... If the PD2 field is empty I don't want to return a
record for it in the UNION query. I have tried "WHERE <> ISNULL(PD2)" and
WHERE PD2 <> NULL which both don't seem to work... I am actually using a
view in SQL Server not a quesry in Access

Chip