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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.