Select statement (Multiple)

[lmossolle]

How do you join multiple Select Statements? Here is my example

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 (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

 

[Duane Hookom]

How would you want to join these separate queries? Do you want multiple
rows/records as the result of a union query or multiple columns/fields as the
result of a select query?

 

[lmossolle]

I would like to have these as a single query, with multiple cols/fields.

Thanks!!!!

How would you want to join these separate queries? Do you want multiple
rows/records as the result of a union query or multiple columns/fields as the
result of a select query?

--
Duane Hookom
Microsoft Access MVP

How do you join multiple Select Statements? Here is my example

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 (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

 

[Duane Hookom]

It looks like each of your queries returns a single record. If this is the
case you can use subqueries to create all but the first value/column. For
instance if you want a query in the sample Northwinds database that counts
the number of products, customers, and employees your SQL would look like:

SELECT Count(Products.ProductID) AS CountOfProductID, (SELECT Count(*) FROM
Customers) AS NumCustomers, (SELECT Count(*) FROM Employees) AS NumEmployees
FROM Products;

Your query might look something like:
SELECT Count(SCD.Status) AS [# Actions Returned],

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

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

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

FROM SCD
WHERE (SCD.Assigned)="Carla" And (SCD.Status)="returned";

I also think a crosstab might get you most of the way to your destination.
--
Duane Hookom
Microsoft Access MVP

I would like to have these as a single query, with multiple cols/fields.

Thanks!!!!

How would you want to join these separate queries? Do you want multiple
rows/records as the result of a union query or multiple columns/fields as the
result of a select query?

--
Duane Hookom
Microsoft Access MVP

How do you join multiple Select Statements? Here is my example

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 (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