Records Within A Defined Range
||This page requires general cleanup in formatting or text to better fit the UA Wiki Guidelines
Database: Range Sample DB (Access 2000)
This tutorial shows how to identify records which fall into a particular range.
For example, if you have a table with customers' information and you want to know which customers 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.
Opt.1:Hard Code your values into the SQL
AgeRange: IIf(Age Between 21 And 30,”21-30”, IIf(Age Between 31 And 40,”31-40”,……….)
Opt. 2:Create a User Defined Function (UDF) to calculate range
The function should be placed in a stand-alone 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
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.
Option 3 : Create a table to hold the ranges
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.
Using a different scenario for option 3 but the concept is the same.
Lets say you have a table named tbl_students which holds student information along with their grades.
- StudentID (PK)
NOTE: The field Grade should really be in a separate table to be properly normalized but for the purpose of this example this field has been included 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, we start working this solution with some SQL.
Step 1 - Build 1st Query
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 - Build 2nd Query
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 "<=".
SELECT StudentID, FName, LName, Grade, qry_Ranges.RangeCategory
FROM tbl_Students LEFT JOIN qry_Ranges
ON (tbl_Students.Grade<= qry_Ranges.EndRange) AND (tbl_Students.Grade >= qry_Ranges.StartRange)
And there it is, you have a query.
Save it and Name it: 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 qry_StudentsGradeRanges in order to get the count of all students that fall within a particular range.
- 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
GROUP BY Q1.RangeCategory;
UNION Query: Display Records Above Max Range
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?
- 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).
However, this qry_Ranges is a bit more involved and for this reason wewill call this query qry_RangesPlus.
This query you would have to write directly into the SQL View.
Following are the steps to create this query.
After creating this query, we 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). This is called a UNION Query.
SELECT NewRange.StartRange, NewRange.EndRange, [StartRange] & " - " & [EndRange] AS RangeCategory
FROM (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 we did in Option 3.
This page was originally ported from the UtterAccess Forums. It is based heavily or in part on a post by dallr.
Identifying & Counting Records that are within a Range