Group by query

[The Joker via AccessMonster.com]

Hello!

I'm working on a query that I think may have to be done in SQL but would
rather do in Access if possible.

I have a table similar to the one below

tblCustomerPurchases:

CustomerID | ProductType |
1 DVD
1 CD
1 Book
1 DVD
2 CD
2 CD
3 Book
3 DVD
4 CD
5 Game
5 CD

I want a query where I can see what customers have bought CDs AND NOT bought
any books or DVDs. So in effect we could solicit our other products to
customers who haven't bought any other items. I think what I'll need to do
is group by CustomerID and do a where statement for CDs and NOT books or DVDs.


I hope this makes sense. Any help will be greatly appreciated!

Thank you very much!!

 

[Krzysztof Naworyta]

Juzer The Joker via AccessMonster.com <u17775@uwe> napisa³

| I'm working on a query that I think may have to be done in SQL but would
| rather do in Access if possible.
|
| I have a table similar to the one below
|
| tblCustomerPurchases:
|
| CustomerID | ProductType |
| 1 DVD
| 1 CD
| 1 Book
| 1 DVD
| 2 CD
| 2 CD
| 3 Book
| 3 DVD
| 4 CD
| 5 Game
| 5 CD
|
| I want a query where I can see what customers have bought CDs AND NOT
| bought any books or DVDs. So in effect we could solicit our other
| products to customers who haven't bought any other items. I think what
| I'll need to do is group by CustomerID and do a where statement for CDs
| and NOT books or DVDs.
|
|
| I hope this makes sense. Any help will be greatly appreciated!

Select
Distinct CustomerID
From tblCustomerPurchases cp0
Where
exists
(select * from tblCustomerPurchases cp1
where ProductType = 'CD'
and cp1.CustomerID = cp0.CustomerID)
and
not exists
(select * from tblCustomerPurchases cp2
where ProductType <> 'CD'
and cp2.CustomerID = cp0.CustomerID)

 

[golfinray]

In the query criteria for product type, Like "dvd" and not like "book" and
not like"cd".
That would check for someone who bought a dvd but no book or cd. Just
reverse those for the rest of the combinations you want.

 

[The Joker via AccessMonster.com]

Thank you very much for your response.

Can you explain a little of what that is doing. I have a basic understanding
of SQL but the parts like cp1.CustomerID = cp0.CustomerID are confusing to me.
What is that saying? Where did you get cp1 and cp0 from? Thanks again :-D

 

[The Joker via AccessMonster.com]

Thanks for the response golfinray.

I don't think that would work because that would bring back the instance of
the dvd purchased but that wouldn't tell me if the customer has also not
purchased any books or CDs. I want a query to match all the criteria.

In essence I'm looking to search within each customer IDs purchases to see if
they have bought a DVD AND have not purchased any books or CDs.

In the query criteria for product type, Like "dvd" and not like "book" and
not like"cd".
That would check for someone who bought a dvd but no book or cd. Just
reverse those for the rest of the combinations you want.

Hello!

[quoted text clipped - 26 lines]

Thank you very much!!

 

[Krzysztof Naworyta]

"cp0", "cp1" and "cp2" are aliases of tableName, defined in SQL after
every table below "From" statement.

Select
Distinct CustomerID
From tblCustomerPurchases cp0
^^^
Where
exists
(select * from tblCustomerPurchases cp1
^^^
where ProductType = 'CD'
and cp1.CustomerID = cp0.CustomerID)
and
not exists
(select * from tblCustomerPurchases cp2
^^^
where ProductType <> 'CD'
and cp2.CustomerID = cp0.CustomerID)

Do you see them?

--
KN




Juzer The Joker via AccessMonster.com <u17775@uwe> napisa³
| Thank you very much for your response.
|
| Can you explain a little of what that is doing. I have a basic
| understanding of SQL but the parts like cp1.CustomerID = cp0.CustomerID
| are confusing to me. What is that saying? Where did you get cp1 and
| cp0 from? Thanks again :-D
|
|| Krzysztof Naworyta wrote:
|| Select
|| Distinct CustomerID
|| From tblCustomerPurchases cp0
|| Where
|| exists
|| (select * from tblCustomerPurchases cp1
|| where ProductType = 'CD'
|| and cp1.CustomerID = cp0.CustomerID)
|| and
|| not exists
|| (select * from tblCustomerPurchases cp2
|| where ProductType <> 'CD'
|| and cp2.CustomerID = cp0.CustomerID)
||
|| --
|| KN

--
KN

archiwum grupy:
http://groups.google.pl/advanced_search
(grupa: pl*msaccess)

 

[golfinray]

I think you could put all on one line:
Like "cd" and not like "book" and not like "dvd"
and that will check all 3.
--
Milton Purdy
ACCESS
State of Arkansas

Thanks for the response golfinray.

I don't think that would work because that would bring back the instance of
the dvd purchased but that wouldn't tell me if the customer has also not
purchased any books or CDs. I want a query to match all the criteria.

In essence I'm looking to search within each customer IDs purchases to see if
they have bought a DVD AND have not purchased any books or CDs.

In the query criteria for product type, Like "dvd" and not like "book" and
not like"cd".
That would check for someone who bought a dvd but no book or cd. Just
reverse those for the rest of the combinations you want.

Hello!

[quoted text clipped - 26 lines]

Thank you very much!!

--



.

 

[vanderghast]

I would use a simple total query:

SELECT customerID
FROM customerPurchases
GROUP BY customerID
HAVING COUNT(*) = SUM( iif(productType="CD", 1, 0))
AND 0 < SUM( iif(productType="CD", 1, 0))


where SUM(iif(productType="CD", 1, 0)) returns the number of records, for a
given customer, where the productType= "CD". If that is >0, then the
customer has bought at least one CD. Furthermore, if the number of records
about this customer is equal to the number of CD, then that customer has
bought nothing else than CD.

Maybe a more direct formulation, with a subquery (but probably slower) :

SELECT DISTINCT customerID
FROM customerPurchases AS x
WHERE "CD" = ALL(SELECT y.productType
FROM customerPurchases AS y
WHERE x.customerID = y.customerID)


which simply looks if "CD" is found in ALL records, as productType, for that
given customer.




Vanderghast, Access MVP