Select case - never ending

[Annette]

I have a table that tracks start time and end time. I need to
calculate the number of minutes and translate the minutes to units.

1 to 5 minutes = .3 units
6 to 10 minutes = .7 units
11 to 15 minutes = 1 unit
16 to 20 minutes = 1.3 units

and so on.

Is there a way that I can create a select case statement, converting
to units, following the above structure without having to create a
case statement for every option (21-25, 26-30, 31-35 etc.) ?

 

[John W. Vinson]

I have a table that tracks start time and end time. I need to
calculate the number of minutes and translate the minutes to units.

1 to 5 minutes = .3 units
6 to 10 minutes = .7 units
11 to 15 minutes = 1 unit
16 to 20 minutes = 1.3 units

and so on.

Is there a way that I can create a select case statement, converting
to units, following the above structure without having to create a
case statement for every option (21-25, 26-30, 31-35 etc.) ?

Rather than embedding this in complex, hard to maintain code, I'd suggest
creating a translation table with two fields, TopOfRange and Units. You can
then use a Query using the minutes to find the record with the next "top of
range" value:

SELECT <whatever from your table>,
(SELECT Units FROM UnitsTable WHERE UnitsTable.TopOfRange = (SELECT
Min(U.TopOfRange) FROM UnitsTable AS U WHERE U.TopOfRange >=
yourtable.[minutes]));
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[stefan]

Hi Annette,
I think you should calculate the units value,
for example:

SELECT Table1.Minutes, Round(Round([Table1].[Minutes]/5+0.4,0)/3,1) AS unit
FROM Table1;

grts Stefan