0

I've been battling this for the last week with many attempted solutions. I want to return the unique names in table with the sum of their points and their current dance level based on that sum. Ultimately I want compare the returned dance level with what is stored in the customer table against the customer and show only the records where the two dance levels are different (the stored dance level and the calculated dance level based on the current sum of the points.

The final solution will be a web page using ADODB connection to MSAccess DB (2013). But for starters just want it to work in MSAccess.

I have a MSAccess DB (2013) with the following tables.

PointsAllocation
CustomerID  Points
100            2
101            1
102            1
100            1
101            4

DanceLevel
DLevel           Threshold
Beginner            2
Intermediate        4
Advanced            6

Customer
CID   Firstname Dancelevel1
100   Bob      Beginner
101   Mary     Beginner
102   Jacqui   Beginner

I want to find the current DLevel for each customer by using the SUM of their Points in the first table. I have this first...

SELECT SUM(Points), CustomerID  FROM PointsAllocation GROUP BY CustomerID

Works well and gives me total points per customer. I can then INNER JOIN this to the customer table to get the persons name. Perfect.

Now I want to add the DLevel from the DanceLevel table to the results where the SUM total is used to lookup the Threshold and not exceed the value so I get the following:

(1)     (2)     (3)        (4)
Bob     3      Beginner   Intermediate
Mary    5      Beginner   Advanced

Where...
(1) Customer.Firstname
(2) SUM(PointsAllocation.Points)
(3) Customer.Dancelevel1
(4) Dancelevel.DLevel

Jacqui is not shown as her SUM of Points is less than or equal to 2 giving her a calculated dance level of Beginner and this already matches the her Dancelevel1 in the Customer table.

Any ideas anyone?

Dacam
  • 13
  • 5
  • Use a Tally Table (Google it for articles relevant to your dialect of SQL) to interpolate the DanceLevel table so your INNER JOIN works. – Pieter Geerkens May 09 '15 at 05:37

1 Answers1

0

You can start from the customer table because you want to list every customer. Then left join it with a subquery that calculates the dance levels and point totals. The innermost subquery totals the points and then joins on valid dance levels and selects the max threshold value from the dance levels. Then left join on the DanceLevel table again on the threshold value to get the level's description.

Select Customer.Firstname,
  CustomerDanceLevels.Points,
  Customer.Dancelevel1,
  Dancelevel.DLevel
from Customer
left join
  (select CustomerID, Points, Min(Threshold) Threshold
  from
      (select CustomerID, sum(Points) Points
      from PointsAllocation
      group by CustomerID
      ) PointsTotal
    left join DanceLevel
    on PointsTotal.Points <= DanceLevel.Threshold
  group by CustomerID, Points
  ) CustomerDanceLevels
on Customer.CID = CustomerDanceLevels.CustomerID
left join DanceLevel
on CustomerDanceLevels.Threshold = DanceLevel.Threshold
Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • I wondered if it would have been smarter to have a different starting point. Well done. Very impressive. It needed a little modification to get right but VERY close. Needed to swap 'On PointsTotal.Points > DanceLevel.Threshold ' with 'On DanceLevel.Threshold > PointsTotal.Points ' and use MIN not MAX. This enabled the correct dance level to be calculated. MSAccess also needs to bracket () the first LEFT JOIN. Then all worked successfully. – Dacam May 09 '15 at 11:47
  • Ah, sorry I misinterpreted how you wanted your thresholds to work. I edited my answer to fix the query. – Brian Pressler May 09 '15 at 17:16