Sort on range

  • Thread starter Thread starter Jason Silva
  • Start date Start date
J

Jason Silva

Hello,

I have a report that is sorted on Credit Score. I would like to group ranges of scores together on the report because individually, there is a very varied array of scores.

For Instance I would like to see something like this:

0-569
Account Date Field Field Field

570-599
Account Date Field Field Field

And So On...

Thank You,
 
Jason said:
I have a report that is sorted on Credit Score. I would like to group ranges of scores together on the report because individually, there is a very varied array of scores.

For Instance I would like to see something like this:

0-569
Account Date Field Field Field

570-599
Account Date Field Field Field
Click to expand...


You should probaly have a table with the ranges of credit
scores, then you could use a query to Join that table to
your data to get the speciific range for each record.

Lacking that you might be able to use the Switch function
either in the report's record source query or in Sorting and
Grouping:

Switch([credit score] < 570,"000-569", [credit score] <
600,"570-599", . . .)

But that expression might get to be long and difficult to
manage, so you could be better off creating a public
function (in a standard module) to contain the logic.

Public Function GetRange(cs As Long) As String
Select Case cs
Case Is < 569
GetRange = "000-569"
Case Is < 600
GetRange = "570-599"
Case . . .

Case Else
GetRange = "Unknown"
EndSelect
End Function
 
I'm not familiar with functions or VB coding but I use the
following to work out grouping on a range.

You can use the grouping functionality in the report
design view to sort and group by a range. A little work is
involved but it is workable. In the field/expression area
of the sorting and grouping screen, list the ranges that
you want to sort/group by using an expression similar to =
[CreditScore] Between X And Y. Make sure that you list the
expressions from the smallest to the largest and make each
of the groups sort ascending. Set the OrderBy property of
the report to sort by the same field to maintain an
ascending order within each group. So you end up with
something like:

=[CreditScore] Between 0 And 100
=[CreditScore] Between 101 And 200
=[CreditScore] Between 201 And 300
etc....

If you want a label at the beginning of each range you can
create a header for the largest range to be included on
the report. Within the header area create an unbound
textbox with an expression similar to this:

=iif([CreditScore] Between 0 And 100, "0-100", iif
([CreditScore] Between 101 And 200, "101-200", iif
([CreditScore] Between 201 And 300, "201-300","")))

This might not be the best or most versatile answer but it
works for someone like me who's an avid user of Access but
not an expert.
-----Original Message-----
Hello,

I have a report that is sorted on Credit Score. I would
Click to expand...
like to group ranges of scores together on the report
because individually, there is a very varied array of
scores.
 
Thank you Marshall & Bob. Bob, I ended up using your suggestion. It worked
fine and made the report much, much cleaner than I had it.

--
Jason Silva
IT Manager
BS-IT Student - UOP
Bob said:
I'm not familiar with functions or VB coding but I use the
following to work out grouping on a range.

You can use the grouping functionality in the report
design view to sort and group by a range. A little work is
involved but it is workable. In the field/expression area
of the sorting and grouping screen, list the ranges that
you want to sort/group by using an expression similar to =
[CreditScore] Between X And Y. Make sure that you list the
expressions from the smallest to the largest and make each
of the groups sort ascending. Set the OrderBy property of
the report to sort by the same field to maintain an
ascending order within each group. So you end up with
something like:

=[CreditScore] Between 0 And 100
=[CreditScore] Between 101 And 200
=[CreditScore] Between 201 And 300
etc....

If you want a label at the beginning of each range you can
create a header for the largest range to be included on
the report. Within the header area create an unbound
textbox with an expression similar to this:

=iif([CreditScore] Between 0 And 100, "0-100", iif
([CreditScore] Between 101 And 200, "101-200", iif
([CreditScore] Between 201 And 300, "201-300","")))

This might not be the best or most versatile answer but it
works for someone like me who's an avid user of Access but
not an expert.
-----Original Message-----
Hello,

I have a report that is sorted on Credit Score. I would
Click to expand...
like to group ranges of scores together on the report
because individually, there is a very varied array of
scores.
For Instance I would like to see something like this:

0-569
Account Date Field Field Field

570-599
Account Date Field Field Field

And So On...

Thank You,
Click to expand...
Click to expand...
 
Back
Top