MS Access Query - is there an ONLY function

[bob8000]

Hi everybody

I would like to know how you define a query to return ONLY something
and not either, or.


Example


Company 1 supplies BOTH catagories (Domestic and Commercial)
Company 2 supplies ONLY Domestic


if you had two companies who supplied equipment and two catagories
(Domestic and Commercial). If these two companies supplied Commercial
equipment and one of these two ONLY supplied Domestic. How would you go

about displaying the one supplier that ONLY supplied both catagories.


But you don't know which company before you do the query so you would
not be able to have the company name as a criteria - if you see what I
mean


In this case the answer would be Company 1.


But how do you get Access to do this.


Any help would be great.


Thanks


BOB8000

 

[Ken Snell \(MVP\)]

SELECT DISTINCT TableName.CompanyName
FROM TableName
WHERE TableName.Category = "Domestic" AND
TableName.Category = "Commercial";

The above query assumes that TableName is a junction table that lists the
companies and has a field (Category) that identifies the category.

If this isn't what you seek, you'll need to tell us more details about your
table structure, because the query structure is completely dependent upon
your table structure.

 

[Douglas J. Steele]

Sorry, Ken, that won't work. It's not possible for a record to have two
separate values at the same time (Category = "Domestic" AND Category =
"Commercial")

SELECT CompanyName
FROM TableName
WHERE Category IN ("Domestic", "Commercial")
GROUP BY CompanyName
HAVING Count(*) = 2

If there are additional fields to Company Name, you might use

SELECT DISTINCT CompanyName, Field1, Field2
FROM TableName
WHERE CompanyName IN (
SELECT TableName.CompanyName
FROM TableName
WHERE Category IN ("Domestic", "Commercial")
GROUP BY CompanyName
HAVING Count(*) = 2
)

 

[Ken Snell \(MVP\)]

I'll claim memory failure... you are right. My mind was thinking of the IN
concept, and got lost in the fog of typing from mind's eye.... what a
strange journey it was.