AND criteria

[jfancy-Transport Canada]

Hi,

I'm building a subscribers database for a client. There are six
different categories. 0 to 6. I need to return those names that are
subscribed to ALL 7 of the categories. He/She cannot show up if, say,
he/she is subscribed to 5 categories. It needs to be a query so that
the client can see the subscribers in a table that are subscribed to
all 7 categories. This is the code I have come up with but doesn't
work. It returns nothing.
------------------------------------------------------------------
SELECT name
FROM SUBSCRIBER_LIST
WHERE category_id = 0 AND category_id = 1, AND category_id = 2, AND
category_id = 3, AND category_id = 4, AND category_id = 5, AND
category_id = 6;
------------------------------------------------------------------
I get the idea that the AND is wrong, because maybe it is comparing one
record to all these id's and returning nothing because in one record,
the category id does not equal 0,1,2,3,4,5 and 6.

Also, I need a query to display only those who are only subscribed to
ONE list only. Now, there are duplicates in the database due to
subscriber dates, so its difficult.

 

[Lynn Trapp]

First of all, your syntax is wrong. Remove all the commas from your where
clause. Second, that will never work. Since category_id is a single field,
you will NEVER find a situation where any single record will have that field
set to all seven values. It will always return no records.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[jfancy-Transport Canada]

So, is there any solution for this? And yes, I had the commas removed,
I don't know why they were in there.

 

[Lynn Trapp]

You might try this:

SELECT name
FROM SUBSCRIBER_LIST
WHERE category_id = 1
UNION
SELECT name
FROM SUBSCRIBER_LIST
WHERE category_id = 2
UNION
SELECT name
FROM SUBSCRIBER_LIST
WHERE category_id = 3
UNION
SELECT name
FROM SUBSCRIBER_LIST
WHERE category_id = 4
UNION
SELECT name
FROM SUBSCRIBER_LIST
WHERE category_id = 5
UNION
SELECT name
FROM SUBSCRIBER_LIST
WHERE category_id = 6;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[John Spencer (MVP)]

Need more information on your data.

Can a subscriber in the list have more than one record for a category? If not,
then you can use

SELECT [Name]
FROM Subscriber_List
GROUP BY [Name]
HAVING Count([Name]) = 7

However; if this set of records is possible then you need something more complex
Name CategoryID
John 1
John 1
John 3

Save the query below as QueryUnique
SELECT Distinct [Name], Category_ID
FROM Subscriber_List

Now use that in place of the Subscriber_List in the earlier query.
SELECT [Name]
FROM QueryUnique
GROUP BY [Name]
HAVING Count([Name]) = 7

Subscribed to only one list
SELECT [Name]
FROM QueryUnique
GROUP BY [Name]
HAVING Count([Name]) = 1

If you change your field name from Name ( a reserved word) to SubScriberName,
you could probably use

SELECT T.[SubscriberName]
FROM

[SELECT Distinct SubscriberName, Category_ID
FROM Subscriber_List]. as T

GROUP BY T.[SubscriberName]
HAVING Count(T.[SubscriberName]) = 7