Joining Multiple Select statements

[lmossolle]

How do you join multiple Select Statements, here is what I would like to join;

SELECT Count(SCD.Status) AS [# Actions Returned]
FROM SCD
WHERE (SCD.Assigned)="Carla" and WHERE (SCD.Status)="returned";

SELECT Count(SCD.Status) AS [# Actions Returned]
FROM SCD
WHERE (SCD.Assigned)="Carla" And (SCD.Status)="returned";

SELECT Count(SCD.Status) AS [# Actions Pending]
FROM SCD
WHERE (SCD.Assigned)="Carla" And (SCD.Status)="pending";

SELECT Count(SCD.Status) AS [# Actions Solicited]
FROM SCD
WHERE (SCD.Assigned)="Carla" And (SCD.Status)="solicited";

SELECT Count(SCD.[SUP CON A&E UTL SVC]) AS [# MOD Actions]
FROM SCD
WHERE (SCD.Assigned)="Carla" And (SCD.[SUP CON A&E UTL SVC])="mod";

Thanks,
Lee

 

[KARL DEWEY]

Try this ---
SELECT Sum(IIf([SCD].[Status]="returned" And [SCD].[Assigned]="Carla",1,0))
AS [# Actions Returned], Sum(IIf([SCD].[Status]="pending" And
[SCD].[Assigned]="Carla",1,0)) AS [# Actions Pending],
Sum(IIf([SCD].[Status]="solicited" And [SCD].[Assigned]="Carla",1,0)) AS [#
Actions Solicited], Sum(IIf([SCD].[SUP CON A&E UTL SVC]="mod" And
[SCD].[Assigned]="Carla",1,0)) AS [# MOD Actions]
FROM SCD;

 

[Marshall Barton]

How do you join multiple Select Statements, here is what I would like to join;

SELECT Count(SCD.Status) AS [# Actions Returned]
FROM SCD
WHERE (SCD.Assigned)="Carla" and WHERE (SCD.Status)="returned";

SELECT Count(SCD.Status) AS [# Actions Returned]
FROM SCD
WHERE (SCD.Assigned)="Carla" And (SCD.Status)="returned";

SELECT Count(SCD.Status) AS [# Actions Pending]
FROM SCD
WHERE (SCD.Assigned)="Carla" And (SCD.Status)="pending";

SELECT Count(SCD.Status) AS [# Actions Solicited]
FROM SCD
WHERE (SCD.Assigned)="Carla" And (SCD.Status)="solicited";

SELECT Count(SCD.[SUP CON A&E UTL SVC]) AS [# MOD Actions]
FROM SCD
WHERE (SCD.Assigned)="Carla" And (SCD.[SUP CON A&E UTL SVC])="mod";

I think you can do that in a single query:

SELECT
Sum(IIf(Status="returned",1,0)) AS [# Actions Returned],
Sum(IIf(Status="pending",1,0)) AS [# Actions Pending],
Sum(IIf(Status="solicited",1,0)) AS [# Actions Solicited],
Sum(IIf([SUP CON A&E UTL SVC]="mod",1,0)) AS [# MOD
Actions]
FROM SCD
WHERE Assigned="Carla"