By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Help needed with ONLY VBA Code or Query

P: n/a
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

Dec 30 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Tony Ciconte" <to******@comcast.net> wrote in message
news:39********************************@4ax.com...
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

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.


Dec 30 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.