Need assistance with an advanced report query

[halewilson]

Hello good neighbors,

I desperately need assistance with creating an advanced report that sums points for each db_member then break out these members into 4 groups. My current report displays total points earned for each member and this works fine, but each time I run this single report I end up with thousands of pages to scroll through. To make things simpler I wish to group these members into four groups according to the amount of points earned ie. Novice(0-9,999), Junior(10,000-19,999), Senior(20,000-49,999), and Elite(50,000+).

I have two tables, tblMembership and tblPoints with a one to many relationship. After each members points are summed how do I go about sorting them into groups?

This is what I have working so far...
SELECT tblMembership.Name, tblMembership.Address, tblMembership.CityState, tblMembership.Zip, tblMembership.Number, tblMembership.Active, tblMembership.Selected, tblPoints.Date, tblPoints.Description, tblPoints.Points
FROM tblMembership INNER JOIN tblPoints ON tblMembership.Number = tblPoints.Number;

Do I need to build a new table to hold the sum values then work off of the new table?

Thanks in advance!

 

[dhookom]

Do I need to build a new table to hold the sum values then work off of the new table?

You don't need a new table since you already have on. Create a totals query qgrpNumberPoints:

SELECT Number, Sum(Points) as TotalPoints
FROM tblPoints
GROUP BY Number;

You can add qgrpNumberPoints to your existing report record source query so you can group by the total points.

I would create a small lookup table of point ranges:
[tt]
tblPointGroups
PointTitle PointMin PointMax
Novice 0 9,999
Junior 10,000 19,999
Senior 20,000 49,999
Elite 50,000 999,999
[/tt]
You could add this to your record source and set the criteria under [TotalPoints] to

Between PointMin and PointMax

This allows you to add PointTitle to the report. Sort and Group on the PointMin field but display PointTitle.

Duane
Hook'D on Access
MS Access MVP