MSAccess/SQL query to retrieve value using a lookup on a SUM'd field in the same query.

Here's a very interesting one for the experts.  I've been battling this for the last week with many attempted solutions.

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

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

Open in new window

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:

100     3      Intermediate
101     5      Advanced
102     1      Beginner
... and so on.

Any ideas anyone?
DClarkeNZAsked:
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.

Phil DavidsonCommented:
When you say add, do you mean have data displayed in the result from two tables?  Or do you mean to arithmetically add some values?

The Dance Level table has no foreign key to relate to the other table.  One way to do this would be to create a temporary table.  Then you can add the designations (the words) based on the range of points into the table.  The temp table is viewed like a regular table (e.g., select * from #intermediate).

create temp table #intermediate (aggPoints, custID,) AS 
SELECT SUM(Points), CustomerID  FROM PointsAllocation GROUP BY CustomerID

alter table #intermediate add column DLevel varchar(20) 

insert val('BEGINNER') into #intermediate.Dlevel where aggPoints < 4
insert val('INTERMEDIATE') into intermediate.Dlevel where aggPoints < 3 AND aggPoints < 5
insert val('ADVANCED') into #intermediate.Dlevel where aggPoints < 4

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
I used a function to calculate the dance level.
Public Function Level(SumOfPoints As Long) As String
    If SumOfPoints <= 2 Then
        Level = "Beginner"
    ElseIf SumOfPoints = 3 Or SumOfPoints = 4 Then
        Level = "Intermediate"
    ElseIf SumOfPoints >= 5 Then
        Level = "Advanced"
    Else
        Level = "Level could not be determined"
    End If
End Function

Open in new window

Then used the function in the query:
SELECT PointsAllocation.CustomerID, Sum(PointsAllocation.Points) AS SumOfPoints, (Level([SumOfPoints])) AS DanceLevel
FROM PointsAllocation
GROUP BY PointsAllocation.CustomerID;

Which produced:
output
Sample db is attached

JeffCoachman
Database75.mdb
Jeffrey CoachmanMIS LiasonCommented:
...so you can now join this to your Customer table, to bring in the customer names.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jeffrey CoachmanMIS LiasonCommented:
Something like this perhaps:

SELECT Customers.CustomerName, PointsAllocation.CustomerID, Sum(PointsAllocation.Points) AS SumOfPoints, (Level([SumOfPoints])) AS DanceLevel
FROM PointsAllocation INNER JOIN Customers ON PointsAllocation.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerName, PointsAllocation.CustomerID;
Jeffrey CoachmanMIS LiasonCommented:
sample db attached
Database75.mdb
DClarkeNZAuthor Commented:
Jeffrey, the exercise is to access the data dynamically not hard code the values in a function.  I want to lookup the dance level dynamically based on the sum of points.  Here's some enhanced code from another site that meets the need perfectly. I needed to modify it to get it to work as required. Trick I found out is a different starting point.

Select Customer.Firstname,
  CustomerDanceLevels.Points,
  Customer.Dancelevel1,
  Dancelevel.DLevel
from (Customer
left join
  (select CustomerID, Points, Min(Threshold) as Threshold
  from
      (select CustomerID, sum(Points) as Points
      from PointsAllocation
      group by CustomerID
      ) PointsTotal
    left join DanceLevel
    on DanceLevel.Threshold > PointsTotal.Points
  group by CustomerID, Points
  ) as CustomerDanceLevels
on Customer.CID = CustomerDanceLevels.CustomerID )
left join DanceLevel
on CustomerDanceLevels.Threshold = DanceLevel.Threshold

Open in new window

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
PatHartmanCommented:
SELECT PointsAllocation.CustomerID, Sum(PointsAllocation.Points) AS SumOfPoints, DanceLevel.DLevel
 FROM PointsAllocation Left Join DanceLevel On Sum(PointsAllocation.Points) >= DanceLevel.Threshold
 GROUP BY PointsAllocation.CustomerID, DancelLevel.DLevel;

Note that this query cannot be created or viewed in QBE view since it contains a non-equi-join.
Jeffrey CoachmanMIS LiasonCommented:
the exercise is to access the data dynamically not hard code the values in a function.  I want to lookup the dance level dynamically based on the sum of points.  
The function does not "Hard code" anything.

The function is used to determine the Dance level.

I was under the impression that you wanted to lookup the dance level based on the Sum of points.
This is what the function does.
Again, nothing is "Hardcoded", ...if the sum of points changes, ...the function will determine the appropriate dance level.

You posted what you had, and you posted the results you wanted.
The function returns exactly what you requested.
If the sum of points changes, so will the Dance level.

Am I missing something?

JeffCoachman
DClarkeNZAuthor Commented:
Jeff, yes and no.  "If the sum of points changes, so will the Dance level."  Yes, but the thresholds and text (dance level) are hard coded into your function.  Whereas the dancelevel table stores that data and could be updated from time to time.

Originally I said "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...". The key word here is 'lookup'.  I was trying to avoid making a routine or function that has those values hard coded.  If they were to change, then the code would need to change and that's what I wanted to avoid.

Pat, you cannot have an aggregate function in JOIN operation.
PatHartmanCommented:
Then break it up into two queries.  One to do the aggregation.  The second uses the first query to join to the lookup table.
BitsqueezerCommented:
Hi,

what you need to do is partition the data into areas - you need a "from" and a "to" value, then you can get what you want in one query.

To successfully partition the data you need to add an ID (simply autonumber) as primary key to the DanceLevel table, if you've done that your DanceLevel table should look like this:

ID	DLevel	Threshold
1	Beginner	2
2	Intermediate	4
3	Advanced	6

Open in new window


After that you can get the values with this:

SELECT DL.DLevel,qryCP.SumPoints,qryCP.CustomerID
FROM (SELECT DL.ID,
		DL.DLevel,
		Nz((SELECT TOP 1 DL1.Threshold
			FROM DanceLevel AS DL1
			WHERE DL1.ID < DL.ID
			ORDER BY DL1.ID DESC
			), 0) AS ThresholdFrom,
		(DL.Threshold - 1) AS ThresholdTo
	  FROM DanceLevel AS DL
	  ) AS DL2
INNER JOIN (
	SELECT SUM(Points) AS SumPoints,CustomerID
	FROM PointsAllocation
	GROUP BY CustomerID) AS qryCP
	ON  qryCP.SumPoints >= DL2.ThresholdFrom
	AND qryCP.SumPoints <= DL2.ThresholdTo

Open in new window


Cheers,

Christian
DClarkeNZAuthor Commented:
I marked my as it was answered elsewhere and is absolutely correct. It simply approaches it from a different starting point and changes the nested queries to suit.  The answer from Bitsqueezer is another approach but I have not tested it.  It should work as well.
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.