Group by 2 columns ?

  • Thread starter Thread starter chemicals
  • Start date Start date
C

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
 
chemicals said:
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
Click to expand...

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
Click to expand...

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
 
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.
 
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
 
Back
Top