Select statement (Multiple)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lmossolle - 20 Mar 2008 14:18 GMT

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

Reply to this Message

Duane Hookom - 20 Mar 2008 14:27 GMT

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?

Signature

Duane Hookom
Microsoft Access MVP

> How do you join multiple Select Statements? Here is my example
>
[quoted text clipped - 18 lines]
> Thanks,
> Lee

Reply to this Message

lmossolle - 20 Mar 2008 15:08 GMT

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
[quoted text clipped - 22 lines]
> > Thanks,
> > Lee

Reply to this Message

Duane Hookom - 20 Mar 2008 15:37 GMT

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.

Signature

Duane Hookom
Microsoft Access MVP

> I would like to have these as a single query, with multiple cols/fields.
>
[quoted text clipped - 26 lines]
> > > Thanks,
> > > Lee

Reply to this Message