X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Closed TopicStart new topic
> Identifying & Counting Records that are within a Range    
post Aug 22 2008, 06:28 AM

UtterAccess VIP
Posts: 3,085
Joined: 4-November 04
From: The Land of the Access Ninja's

This tutorial shows how to identify records which fall into a particular range. For example, if I have a table with customers information and I want to know which customer’s fall within a particular age range i.e (21-30 , 31- 40, 41-50).
There are a number of techniques that you can apply to achieve this.
1. Option 1: Hard Code your values into the SQL.
e.g AgeRange: IIf(Age Between 21 And 30,”21-30”, IIf(Age Between 31 And 40,”31-40”,……….)

2. Option 2 : Create a User Defined Function (UDF) to calculate the range
The function should be placed in a standalone module and the syntax should be as follows:
Public Function udf_Range(theValue As Variant) As String
Dim strRange As String
Select Case theValue
    Case 21 To 30: strRange = "21 - 30"
    Case 31 To 40: strRange = "31 - 40"
    Case 41 To 50: strRange = "41 - 50"
End Select
udf_Range = strRange
End Function

Now you can use the function in your query as follows.
SELECT Age, udf_Range(Age) FROM YourTableName

Both of the aforementioned techniques can work but they are very static in nature. Meaning end users cannot dynamically change the range values except they get access to your module and SQL, and of course we don’t want user to be manipulating our SQL or Modules.
This brings us to the 3rd option which gives the greatest level of flexibility and allows users to dynamically change the ranges as they see fit.
3. Option 3 : Create a table to hold the ranges
I am going to change things up here a bit and use a different scenario for option 3 but the concept is the same. Lets say I have a table (tbl_students) which holds student information along with their grades.
StudentID (PK)
PS: The field Grade should really be in a separate table to be properly normalized but for the purpose of this example I have put everything in one table.
Now we create a table to store our range values.
RangeID (PK)
Right, now that we have gotten the preliminaries out of the way lest start working this solution with some SQL.
Step 1
Build a query (qry_Ranges) from your ranges table (tbl_Ranges) and concatenate the starting and ending ranges into a new field.

SELECT StartRange
     , EndRange
     , [StartRange] & "-" & [EndRange] AS RangeCategory
FROM tbl_Ranges

Step 2
To build the second query you cannot use design view but instead you need to go to SQL View.
In SQL View of this new query we are going to use a LEFT JOIN to show all the records from tbl_Students that are related to the query qry_Ranges. If you DON”T want records outside of the range to show then use an INNER JOIN.
The key part to observe in this query is how the ON clause uses >= and <=.
     , FName
     , LName
     , Grade
     , qry_Ranges.RangeCategory
FROM tbl_Students
   LEFT JOIN qry_Ranges
     [color="blue"]ON (tbl_Students.Grade<= qry_Ranges.EndRange)
        AND (tbl_Students.Grade >= qry_Ranges.StartRange);[/color]

And there it is, you have a query (qry_StudentsGradeRanges) which identifies all the records that are within a particular range. If you now want to change your range from (21 -30) to (18-30) you can simply change it in the table (tbl_Ranges) and then run the query above to get the appropriate results.
You can even take this a step further and build another query (qryRangeCategoryCount) which uses the query (qry_StudentsGradeRanges) in order to get the count of all students that fall within a particular range. E.g
Range       RangeCount
45 - 55          89
56 - 65          24
66 – 75          12

The SQL for this would be.
SELECT Q1.RangeCategory
     , Count(Q1.RangeCategory) AS CategoryCount
FROM qry_StudentsGradeRanges AS Q1
WHERE (((Q1.RangeCategory)<>"-"))
GROUP BY Q1.RangeCategory;

NOW Let’s put a different twist on this
What about situations where you want to have a range but you also want to show records above the max range value that is stored in your table. For example.

StartRange  EndRange
21             30
31           40
41           50
51    and greater.

To handle this requirement we are still going to create a qry_Ranges and link it to the relevant table (tbl_Students). But this qry_Ranges is a bit more involved and for this reason I will call this query qry_RangesPlus.
This query you would have to write directly into the SQL View. I will mention the steps for this query but I will build everything into one query at the end.
Step 1 :Have a query that shows the startRange and EndRange fields from the tbl_Ranges.
Step 2 : Build another query which gets the highest EndRange value of the tbl_Ranges and adds 1 (one) to it as a field. Along with showing the MAX Grade from the tbl_Students table as another field.
Step 3 : Create a UNION query which is built upon steps 1 & 2 above but make sure to write the SQL for step one first before putting the word UNION when creating this query.
Step 4 : Build a new query using the UNION query in step 3 and concatenate the starting and ending values.
This is all those steps above combined into one query (qry_RangesPlus).
SELECT NewRange.StartRange
     , NewRange.EndRange
     , [StartRange] & " - " & [EndRange] AS RangeCategory
   (SELECT StartRange, EndRange FROM tbl_Ranges
   SELECT MAX(EndRange)+ 1, (SELECT MAX(Grade) FROM tbl_Students) FROM tbl_Ranges) AS NewRange;

Now you can link this query (qry_RangesPlus) to the table tbl_students using the >= and <= as I did in option 3.
PS: please see the attached 2000 database for a working example of all of the above.
Attached File(s)
Attached File  Range_Sample.zip ( 20.15K )Number of downloads: 104
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th July 2020 - 04:34 AM