CODEWITHSUNDEEP
sqlms-access
Icebreaker
Icebreaker

Reputation: 297

Need to pivot data on a access database

Say I have a table structure like this:

Table Pizza_Sales

 Customer_ID  | Total_Spent
 123456            45.50
 346432            60.31
 867432            100.67

Table Pizza

ID|   Customer_ID | Topping   | Percent
 1      123456       pepper.    50%
 2      123456       onions     50%
 3      346432       pineap.    25%
 4      346432       mushro.    25%
 5      346432       pepper.    25%
 6      346432       sausag.    25%
 7      867432       bacon      100%

Table Name

  Customer_ID     | Name
       123456       Jon    
       346432       Steve 
       867432       Mike

All are joined by Customer ID. Say I want a report that says their name, customer ID, toppings they eat and how much they spend. So the result would look something like this:

  ID     | Name  | T_1    || T_2   || T_3    || T_4   | Total                                                                                   
 123456    Jon    pepper.   onions                      45.50                                                                     
 346432    Steve  pineap.   mushro.  pepper.   sausag.  60.31
 867432    Mike   bacon                                 100.67

How could I do that? Also note in the case of 2 or more toppings whatever has the smaller ID aka first record for that customer ID group will always be first followed by whatever is second etc. I actually understand how to do everything except for grouping the toppings that's what I am stuck on. Seems like a pivot table or something is what I am looking for just dunno how to apply that to Access SQL.

Upvotes: 0

Views: 25

Answers (1)

Parfait
Parfait

Reputation: 107767

Consider MS Access' unique crosstab query with a running count using the domain function DCount:

TRANSFORM Max(main.topping) AS MaxOfTopping
SELECT main.Customer_ID, main.[Total_Spent] As [Total]
FROM 
   (SELECT t.ID, t.Customer_ID, t.Topping, t.[Percent], s.[Total_Spent],
          'T_' & DCount("*", "Toppings", "Customer_ID = " & t.Customer_ID & " 
                        AND Topping <='" & t.Topping & "' 
                        AND [Percent]<=" & t.[Percent]) As [Rank]
    FROM Pizza As t
    INNER JOIN Pizza_Sales As s ON t.Customer_ID = s.Customer_ID) As main  

GROUP BY main.Customer_ID, main.[Total_Spent]
PIVOT main.Rank;

-- Customer_ID  Total   T_1     T_2     T_3     T_4
-- 123456       45.5    onions  pepper.     
-- 346432       60.31   mushro. pepper. pineap. sausag.
-- 867432       100.67  bacon

Upvotes: 1

Related Questions