Help with Crosstab Query

Hi Experts,

My Tables and fields are:

Customers
   CustomerID
   etc.

Purchases
   CustomerID
   Product
   - I have 4 products, each with a bunch of variants, e.g.
   Product1-Variant1
   Product1-Variant2
   ....
   Product2-Variant1
   ....
   Product4-Variant1.
   ....

   Note: Customers may have purchased same product on different dates

I am trying to come up with a Crosstab Query to create a recordset with a row for each Customer and a column with a count of Purchases for each Product (aggregating the variants of each Product).

CustomerID,       Product1,       Product2,       Product3,       Product4
1234                    4                    0                    1                    0

Regards,
Leigh
LVL 1
LeighWardleAsked:
Who is Participating?
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.

Dale FyeCommented:
Well, you have made this extremely difficult on yourself by having seperate fields for Product1-Variant1, Product1-Variant1, ...

What value do you have in each of those columns?  Is it a Quantity?  If you had your table structured like:

Product    Variant    Qty
      1                1           2
      1                2           3
      2                1           6
...

Then this would be an easy task.  So, what I would start out doing is creating a normalizing query:

SELECT CustomerID, 1 as Product, 1 as Variant, Product1-Variant1 as Qty
FROM your Table WHERE Product1-Variant1 IS NOT NULL
UNION ALL
SELECT CustomerID, 1 as Product, 2 as Variant, Product1-Variant2 as Qty
FROM your Table WHERE Product1-Variant2 IS NOT NULL
UNION ALL
SELECT CustomerID, 2 as Product, 1 as Variant, Product2-Variant1 as Qty
FROM your Table WHERE Product2-Variant1 IS NOT NULL
UNION ALL
SELECT CustomerID, 2 as Product, 2 as Variant, Product2-Variant2 as Qty
FROM your Table WHERE Product2-Variant2 IS NOT NULL
UNION ALL
...
UNION ALL
SELECT CustomerID, 4 as Product, 1 as Variant, Product4-Variant1 as Qty
FROM your Table WHERE Product4-Variant1 IS NOT NULL
UNION ALL
SELECT CustomerID, 4 as Product, 2 as Variant, Product4-Variant2 as Qty
FROM your Table WHERE Product1-Variant2 IS NOT NULL

Open in new window

Save this query as qry_Normalized_Customer_Products_Variants

Then you can easily create the crosstab you want using this query as your data source.
0
Annette Wilson, MSISSr. Programmer AnalystCommented:
SELECT
  t1.CUSTOMERID
 ,t1.CustName
 ,t2.Product1V1
 ,t2.Product1V2
 ,t2.Prodcut2
 ,t2.Product4 
 ,t2.COUNT(Product1V1) as NumProduct1V1_Sold
 ,t2.COUNT(Product1V2) as NumProduct1V2_Sold
 ,t2.COUNT(Product2) as NumProduct2_Sold
 ,t2.COUNT(Product4) as NumProduct4_Sold
FROM Customer_Table t1 
 LEFT JOIN ProductsTable t2 
     ON t1.[CustomerID]=t2.[CustomerID]
Group by 
      t1.CUSTOMERID
     ,t1.CustName
     ,t2.Product1V1
     ,t2.Product1V2
     ,t2.Prodcut2
     ,t2.Product4 

Open in new window



If you create a tabular report and have all created columns as detail and group by customer and customer name then include subtotals it should work.
0
LeighWardleAuthor Commented:
I am out of the office - so cannot test any queries.

A quick question for all.
Do your queries catch Customers that have no purchases? (That's a requirement).
Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Annette Wilson, MSISSr. Programmer AnalystCommented:
Yes. I created a left join on the products table in order to get the customers that have no purchases.
0
LeighWardleAuthor Commented:
Could the queries be simplified by using terms like:

Like "Product1*" etc. ?

That would save having many terms for each variant.
0
Annette Wilson, MSISSr. Programmer AnalystCommented:
Yes, actually, if you just do COUNT (Product) and include t2.Product, and group by CustomerId, name, and Product, it will pick up the variations of product.
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:
I have got a working Crosstab query now - but need a bit more help - see my new question:
http://www.experts-exchange.com/Database/MS_Access/Q_28669295.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.

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.