Need to build array of ranges - Partition Function not enough

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

I want to be able to generate a set of ranges to group data with in a
query at run-time. I want the start value to be the mimimum and the
last end value to come from min and max values from a field in the
dataset that I am grouping on. I want the end value for each range to
be a user-defined interval and the start value to be the next smallest
increment above the preceding range's end interval. The partition
function doesn't get me all the way there.

Range Example:
4.625 to 5
5.001 to 5.5
5.501 to 6
6.001 to 6.5
6.501 to 7
7.001 to 7.128

Any help on how I can right a routine to generate these ranges would
be appreciated!

Thanks,
Christine
 
Place the ranges in a table.
Use DLookup() to get the primary key for the range, e.g.:
DLookup("RangeID", "tblRange", "RangeMin > " & [MyField])

Once you have the RangeID, you can get the defined range from that.
 
Allen, thanks for your reply to my question. That is a good solution
but I will be working with changing data and foresee having to rebuild
such a table with each update to the recordset. The min and max
values will not be static.

- Christine

Allen Browne said:
Place the ranges in a table.
Use DLookup() to get the primary key for the range, e.g.:
DLookup("RangeID", "tblRange", "RangeMin > " & [MyField])

Once you have the RangeID, you can get the defined range from that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

CJ said:
I want to be able to generate a set of ranges to group data with in a
query at run-time. I want the start value to be the mimimum and the
last end value to come from min and max values from a field in the
dataset that I am grouping on. I want the end value for each range to
be a user-defined interval and the start value to be the next smallest
increment above the preceding range's end interval. The partition
function doesn't get me all the way there.

Range Example:
4.625 to 5
5.001 to 5.5
5.501 to 6
6.001 to 6.5
6.501 to 7
7.001 to 7.128

Any help on how I can right a routine to generate these ranges would
be appreciated!

Thanks,
Christine
Click to expand...
Click to expand...
 
Hi Christine.

In may way of thinking, a table would be about the easiest way to update
this when you need it. Better than hard-coding the values, IMHO.

If these values are the result of calculations, you could create a query
that generates the values for you, and then DLookup() the query instead of
having a table?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

CJ said:
Allen, thanks for your reply to my question. That is a good solution
but I will be working with changing data and foresee having to rebuild
such a table with each update to the recordset. The min and max
values will not be static.

- Christine

"Allen Browne" <[email protected]> wrote in message
Place the ranges in a table.
Use DLookup() to get the primary key for the range, e.g.:
DLookup("RangeID", "tblRange", "RangeMin > " & [MyField])

Once you have the RangeID, you can get the defined range from that.

CJ said:
I want to be able to generate a set of ranges to group data with in a
query at run-time. I want the start value to be the mimimum and the
last end value to come from min and max values from a field in the
dataset that I am grouping on. I want the end value for each range to
be a user-defined interval and the start value to be the next smallest
increment above the preceding range's end interval. The partition
function doesn't get me all the way there.

Range Example:
4.625 to 5
5.001 to 5.5
5.501 to 6
6.001 to 6.5
6.501 to 7
7.001 to 7.128

Any help on how I can right a routine to generate these ranges would
be appreciated!

Thanks,
Christine
Click to expand...
Click to expand...
Click to expand...
 
Hello again,

I have gone the way of creating the range tables and wrote a nifty
little routine that will generate them. Just one more question?

Using this would require two queries, would it not?

DLookup("RangeID", "tblRange", "RangeMin > " & [MyField])

Once you have the corresponding range ID for each record, you then
need to join the table directly to retrieve the range itself.

Thanks,
Christine

Allen Browne said:
Place the ranges in a table.
Use DLookup() to get the primary key for the range, e.g.:
DLookup("RangeID", "tblRange", "RangeMin > " & [MyField])

Once you have the RangeID, you can get the defined range from that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

CJ said:
I want to be able to generate a set of ranges to group data with in a
query at run-time. I want the start value to be the mimimum and the
last end value to come from min and max values from a field in the
dataset that I am grouping on. I want the end value for each range to
be a user-defined interval and the start value to be the next smallest
increment above the preceding range's end interval. The partition
function doesn't get me all the way there.

Range Example:
4.625 to 5
5.001 to 5.5
5.501 to 6
6.001 to 6.5
6.501 to 7
7.001 to 7.128

Any help on how I can right a routine to generate these ranges would
be appreciated!

Thanks,
Christine
Click to expand...
Click to expand...
 
Does not need 2 queries.

If the ranges are in order, the DLookup() will be sufficient.

If you are thinking of specifying both a min and max value for the range,
the 3rd argument can do that:

=DLookup("RangeID", "tblRange", "(RangeMin > " & [MyField] &
") AND RangeMax <= " & [MyField] & ")")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

CJ said:
I have gone the way of creating the range tables and wrote a nifty
little routine that will generate them. Just one more question?

Using this would require two queries, would it not?

DLookup("RangeID", "tblRange", "RangeMin > " & [MyField])

Once you have the corresponding range ID for each record, you then
need to join the table directly to retrieve the range itself.

Thanks,
Christine

"Allen Browne" <[email protected]> wrote in message
Place the ranges in a table.
Use DLookup() to get the primary key for the range, e.g.:
DLookup("RangeID", "tblRange", "RangeMin > " & [MyField])

Once you have the RangeID, you can get the defined range from that.

CJ said:
I want to be able to generate a set of ranges to group data with in a
query at run-time. I want the start value to be the mimimum and the
last end value to come from min and max values from a field in the
dataset that I am grouping on. I want the end value for each range to
be a user-defined interval and the start value to be the next smallest
increment above the preceding range's end interval. The partition
function doesn't get me all the way there.

Range Example:
4.625 to 5
5.001 to 5.5
5.501 to 6
6.001 to 6.5
6.501 to 7
7.001 to 7.128

Any help on how I can right a routine to generate these ranges would
be appreciated!

Thanks,
Christine
Click to expand...
Click to expand...
Click to expand...
 
Back
Top