I have a field with about 400 records in it containing dates. I made a query to show the number of days that have elapsed between the date in the record and the current date. e.g. If the record is April 11, the query will say 116 days have past. Then I modified it so it would show the number of records that are between certain days away: 31-60, 61-120, 121-180, etc up to 365. For each range though I had to make a different query. My 31-60 query looks like this:
Code:
31-60: Count(DateDiff("d",[Full Database]![Date of ATIA /PA submission/request],Date()))
Total: Expression, Show: Yes
Code:
DateDiff("d",[Full Database]![Date of ATIA /PA submission/request],Date())
Total: Where, Show: No, Criteria: Between 31 And 60
I put each one in it's own query because the criteria (Between 31 And 60) has to be alone in the query to work. Now I have 7 queries, but I'm wondering if there's a better way to do it (perhaps put it all in one query) to avoid having trouble when I make a report.
I'm pretty new with Access so I just keep trying stuff until I find a way that works, but like above, it's probably not the best way. Any help on a better way to do this would be greatly appreciated.
Khawar's example is fine if you want to have a horizontal view. However, lets say you wanted it to be in a vertical format, you would have to have a lot of iff's in the control. Another method would be to create a public function to handle this:
Code:
Public Function AgedDebtors(AnyDate As Date) As String
Select Case DateDiff("d",Date,AnyDate)
Case <= 30: AgedDebtors = "0 - 30"
Case <= 60: AgedDebtors = "31 - 60"
Case <= 90: AgedDebtors = "61 - 90"
etc...
End Select
End Function
Then in your query you would insert a calculated field, thus:
Phew you're lifesavers! Thanks for the quick response. I just used the IIF method first because I could change the rows/columns later in the report. I'll hang onto the public function method for future reference.
The sample query worked great and I hadn't used the IIF function before so it was good to learn. I ended up using it one way or another in about half of the queries I had to make.
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. If your ranges are never going to change well the previous members post can work just find.
1. 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.
Tbl_Students
StudentID (PK)
FName
LName
Grade
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.
tbl_Ranges
RangeID (PK)
StartRange
EndRange
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.
Code:
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 <=.
Code:
SELECT StudentID
, 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.
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.
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).
Code:
SELECT NewRange.StartRange
, NewRange.EndRange
, [StartRange] & " - " & [EndRange] AS RangeCategory
FROM
(SELECT StartRange, EndRange FROM tbl_Ranges
UNION
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.
