Updating fields based on changing range of values

[Jessica]

Greetings!

I have a table that records depths for each entry. I want
every depth assigned to a category (bin) that is based on
a range of depths. I have placed another field in the
table to hold the name of the category (bin), let's call
it BinRange. Now for the tricky part. The category
ranges will change based on the users input and so I need
a way to update the BinRange based on what the user
inputed.

Here's what I have so far:
I have a form which allows the user to create 12 bins
(ranges of depths), but not all of these will always be
used. I had created a Select Case function which updated
the BinRange field based on the ranges, which also
included a case when the depth was outside the first bin
(< bin1's minimum) and when the depth was greater than the
12th bin's maximum.

And now for the problem:
When a user enters less than 12 bins (ranges) I don't know
how to quickly change the Select Case Method to stop after
the last used bin. For example if they have only three
bins (9-18, 19-30, and 30-100), everything below 9 would
be marked BIN<9, between 9 and 18 would be Range_1, 19-30
would be Range_2, and 30-100 would be Range_3. But
everything >100 would not be BIN>100 because that was set
up for the value greater than the 12th bin.

Maybe I am approaching this from the wrong angle with the
Select Case Method, but I am not sure how else to use it.
Another point is that the bins will not always be equally
distributed (as in equal interval), so I can not use the
partition function. Any help would be greatly appreciated!

Thanks!
Jessica

 

[Ted Allen]

Hi Jessica,

There are probably many ways that you could do this. One
way that I can think of would be to not store the bin
number in the depth table at all and just calculate the
bin number at run time in your queries/forms/reports
based on the bin ranges in a bin table.

The bin table could have any number of records. To
determine the Appropriate Bin ID for a particular record
in a query you could just use the DMax() function to look
up the Bin ID of the greatest depth from a subset of the
Bin records where the min depth is less than the depth of
the current record. For example:

BinNo: DMax("[BinID]","BinTable","[BinMinDepth] <= " &
[Depth])

Where [Depth] is the field from the depth table and
[BinMinDepth] is the lower bound value from the bin
table. This would return the number from the [BinID]
field in the Bin Table of the record with the greatest
min depth that is less than the current depth.

This would allow you to have any number of bins in the
table. You could use a continuous form or datasheet for
the user to enter their bin criteria if needed, or you
could have them enter it in the table directly.

Another way of handling this would be to use a custom
function. But if the DMax() function works I don't think
that it would be worth going through that effort.

Hopefully this will help.

-Ted Allen

 

[Jessica]

Thanks! That seems to be doing the trick and it is a lot
easier to implement then what I was trying to do!

Jessica

-----Original Message-----
Hi Jessica,

There are probably many ways that you could do this. One
way that I can think of would be to not store the bin
number in the depth table at all and just calculate the
bin number at run time in your queries/forms/reports
based on the bin ranges in a bin table.

The bin table could have any number of records. To
determine the Appropriate Bin ID for a particular record
in a query you could just use the DMax() function to look
up the Bin ID of the greatest depth from a subset of the
Bin records where the min depth is less than the depth of
the current record. For example:

BinNo: DMax("[BinID]","BinTable","[BinMinDepth] <= " &
[Depth])

Where [Depth] is the field from the depth table and
[BinMinDepth] is the lower bound value from the bin
table. This would return the number from the [BinID]
field in the Bin Table of the record with the greatest
min depth that is less than the current depth.

This would allow you to have any number of bins in the
table. You could use a continuous form or datasheet for
the user to enter their bin criteria if needed, or you
could have them enter it in the table directly.

Another way of handling this would be to use a custom
function. But if the DMax() function works I don't think
that it would be worth going through that effort.

Hopefully this will help.

-Ted Allen

-----Original Message-----
Greetings!

I have a table that records depths for each entry. I want
every depth assigned to a category (bin) that is based on
a range of depths. I have placed another field in the
table to hold the name of the category (bin), let's call
it BinRange. Now for the tricky part. The category
ranges will change based on the users input and so I need
a way to update the BinRange based on what the user
inputed.

Here's what I have so far:
I have a form which allows the user to create 12 bins
(ranges of depths), but not all of these will always be
used. I had created a Select Case function which updated
the BinRange field based on the ranges, which also
included a case when the depth was outside the first bin
(< bin1's minimum) and when the depth was greater than the
12th bin's maximum.

And now for the problem:
When a user enters less than 12 bins (ranges) I don't know
how to quickly change the Select Case Method to stop after
the last used bin. For example if they have only three
bins (9-18, 19-30, and 30-100), everything below 9 would
be marked BIN<9, between 9 and 18 would be Range_1, 19- 30
would be Range_2, and 30-100 would be Range_3. But
everything >100 would not be BIN>100 because that was set
up for the value greater than the 12th bin.

Maybe I am approaching this from the wrong angle with the
Select Case Method, but I am not sure how else to use it.
Another point is that the bins will not always be equally
distributed (as in equal interval), so I can not use the
partition function. Any help would be greatly appreciated!

Thanks!
Jessica
.

.