Group by

N

neeraj

Order # Service
1 A
1 B
2 A
3 A
3 B
3 C
.....
I have the above query in which orders are grouped . There are a few
services like A,B,C, D and each order can have a combination of these
servies. I am trying to create a query by which I want to find all orders
(along with all the services that they have) which have Service A included
but not Service B. How can I do it
 
E

Eric D via AccessMonster.com

SELECT Table.[Order#], Table.Service
FROM Table
WHERE ((Not (Table.Service)="B"));
 
J

John Spencer (MVP)

One method.

SELECT [Order #], Service
FROM Table
WHERE [Order #] Not IN
(SELECT [Order #]
FROM Table
WHERE Service = "B")
AND [Order #]
IN
(SELECT [Order #]
FROM Table
WHERE Service = "A")

Second method involves 3 queries.

QueryA
SELECT [Order #]
FROM Table
WHERE Service = "B"

QueryB
SELECT [Order #]
FROM Table
WHERE Service = "A"

QueryC
SELECT Table.*
FROM (Table INNER JOIN QUERYB
ON Table.[Order #] = QueryB.[Order#])
LEFT JOIN QueryA
 
M

Michel Walsh

Hi,


....and the requirement that the Order# is to have service A.


Vanderghast, Access MVP

Eric D via AccessMonster.com said:
SELECT Table.[Order#], Table.Service
FROM Table
WHERE ((Not (Table.Service)="B"));
Order # Service
1 A
1 B
2 A
3 A
3 B
3 C
....
I have the above query in which orders are grouped . There are a few
services like A,B,C, D and each order can have a combination of these
servies. I am trying to create a query by which I want to find all orders
(along with all the services that they have) which have Service A included
but not Service B. How can I do it
Click to expand...
Click to expand...
 
N

neeraj

That did it, the second method involving 3 queries was simpler to understand

John Spencer (MVP) said:
One method.

SELECT [Order #], Service
FROM Table
WHERE [Order #] Not IN
(SELECT [Order #]
FROM Table
WHERE Service = "B")
AND [Order #]
IN
(SELECT [Order #]
FROM Table
WHERE Service = "A")

Second method involves 3 queries.

QueryA
SELECT [Order #]
FROM Table
WHERE Service = "B"

QueryB
SELECT [Order #]
FROM Table
WHERE Service = "A"

QueryC
SELECT Table.*
FROM (Table INNER JOIN QUERYB
ON Table.[Order #] = QueryB.[Order#])
LEFT JOIN QueryA
Order # Service
1 A
1 B
2 A
3 A
3 B
3 C
....
I have the above query in which orders are grouped . There are a few
services like A,B,C, D and each order can have a combination of these
servies. I am trying to create a query by which I want to find all orders
(along with all the services that they have) which have Service A included
but not Service B. How can I do it
Click to expand...
Click to expand...
 
Top