| re: Help needed with ONLY VBA Code or Query
"Tony Ciconte" <tonyc219@comcast.net> wrote in message
news:39jar11uv2vou1s0sp7iqc4dk9kdbvbb1a@4ax.com...[color=blue]
>I have a table of customers who may have purchased numerous types of
> products. Finding out who purchased what is easy. However, what I need
> to determine is which customer has purchased ONLY one or two
> particular products and NOTHING else.
>
> There is a customers table where each customer has a unique ID, a
> product table where each product has a unique ID, and a sales table
> that has records for each customer and the products they purchased. I
> would like to find out which customers purchased only widget1 and/or
> widget2 and no other widget types in the product line.
>
> I cannot get my mind around how to either write a query and/or VBA
> code to determine which customer may have purchased only one product
> regardless of how many of the one item they purchased.
>
> Any and all help is greatly appreciated.
>
> JM[/color]
This seems to have been posted a couple of days ago to
'microsoft.public.access.modulesdaovba' and the question was answered. Did
you not see that answer, or was there another reason it was ignored?
The answer was:
You could do it using subqueries. We don't know table and field names, but
the query should be something like:
SELECT tblCustomer.* FROM tblCustomer WHERE
tblCustomer.CusID IN (SELECT tblSales.CusID FROM tblSales WHERE
tblSales.ProdID IN (5,9))
AND
tblCustomer.CusID NOT IN (SELECT tblSales.CusID FROM tblSales WHERE
tblSales.ProdID NOT IN (5,9))
.... this assumes that 5 and 9 represent the ID's of the 2 products you are
interested in.
| 
