Help with some SQL

Hi Experts,

My Tables and fields are:

Customers
   CustomerID
   etc.

Purchases
   CustomerID
   ProductName   - can be Product 1 or Product 2.
   
Note: Customers may have purchased same product on different dates.

I am trying to come up with a Query that will output all customers including 2 Boolean fields, let's call them boolProduct1 and boolProduct2.

boolProduct1 will be True if the customer has purchased Product 1.
boolProduct2 will be True if the customer has purchased Product 2.

To help you test your queries, I have uploaded an Access database with sample data:


Regards,
Leigh
LVL 1
LeighWardleAsked:
Who is Participating?
 
PatHartmanCommented:
You can use a crosstab query to create a recordset with a row for each customer and a column with a count of purchases for each product.

CustID, Prod1, Prod2, Prod3, Prodn
1234, 4, 0, 1, 0

To do this use a left join from the product table to your order details table.  This will ensure that you always have a column for each product even if no selected customer has ordered one.

Keep in mind that this solution only works if you have a smallish set of products.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Leigh,

1. Create a query that returns one row for each customer/product1.

 a. add your product table.
 b. pull down the customer and product field.
 c. Set the unique values property to true (gives you select distinct).
 d. Save the query

2. Create another query:
 
  a. add your customer table and 1st query
  b. Join the two on the customerid
  c. Double click on the join and select "All customer records" (should be option two).  This gives you an outer join; it will return one row for every customer whether or not there is a matching product row.
  d. Pull down the customer ID into the grid.
  e. in a second column type:

    hasProduct1:IIF(IsNull([Product]),"False","True")

3. Once you have that working, duplicate query from step 1 but for product 2.  Then add in like you did in step 2 the first time around.

Note that this is only one way to do it and there may be better ways depending on the number of products you want to do this with.

Jim.
0
 
LeighWardleAuthor Commented:
Thanks, Jim, for your suggested solution.
I actually have 4 products.  I used 2 products to define the problem, hoping it would be easy to generalize solutions to 4 products.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I guessed that more or less, which is why I put the comment in.   Generally when you ask a question like this, it's best not to minimize the situation.  Sometimes that does help get to a solution, but also understand that minimizing can change the solution you get.

 The other thing that works into this is how your going to use the result. i.e. as part of report, in processing or doing other work, as a display in a form, etc.

 That also can change the solution.

 So give a little more detail on what the end result actually is.

Jim.
0
 
LeighWardleAuthor Commented:
Thanks, Jim.
My goal is to export my customer list to MailChimp.
I want to include what products customers have purchased or enquired about.
MailChimp will only import a flat customer list.
Hope that clarifies things..
0
 
LeighWardleAuthor Commented:
Hi Pat,

Thanks for your suggestion.

My requirment is a little more complicated, so I have posted another question:
http://www.experts-exchange.com/Database/MS_Access/Q_28669156.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.