Access Queries and CASE (or perhaps choose?) 1

[ScottishFencer]

Hi There,

I am trying to evaluate a matrix.

Down the left we have nunmber in the group, across the top we have number of units, the intersection gives me the "banding".

So the matrix looks something like this:
Livestock Units
GroupMem,<=5,<=10,<=110,<=250,>250
<=5,43,63.5,101.8,133.7,143.8
6,63,83.5,121.8,153.7,163.8
7,83,103.5,141.8,173.7,183.8
8,103,123.5,161.8,193.7,203.8
9,123,143.5,181.8,213.7,223.8
10,143,163.5,201.8,233.7,243.8

Contact me if that wasn't clear; there's not many formatting options in TGML (unless there are some I don't know about).

I need to work out a way of inserting this into a query. I'm trying to work out fees, and so, the more members you have in the group, the higher the fee. Any help would be appreciated.

 

[Golom]

[tt]
GroupMem,<=5, <=10, <=110, <=250, >250
5, 43, 63.5, 101.8, 133.7, 143.8
6, 63, 83.5, 121.8, 153.7, 163.8
7, 83, 103.5, 141.8, 173.7, 183.8
8, 103, 123.5, 161.8, 193.7, 203.8
9, 123, 143.5, 181.8, 213.7, 223.8
10, 143, 163.5, 201.8, 233.7, 243.8
[/tt]
Build a table like this ([ignore]Use the [tt]...[/tt][/ignore] tags for this formatting.)
[tt]
tblGroups
GroupMem UpperLimit Band
5 5 43
5 10 63.5
5 20 101.8
etc.
[/tt]
Then run a query something like this

Select A.GroupNum, A.Units, G.Band
From myTable A, tblGroups G
Where IIF(A.GroupNum <= 5, G.GroupMem = 5, G.GroupMem = A.GroupNum)
  AND G.UpperLimit = 
     (Select MIN(G1.UpperLimit) From tblGroups G1
      Where G1.UpperLimit >= A.Units)

 

[ScottishFencer]

Thanks for the TT tage tip Golom. Much appreciated.

Ill also give your suggestion a try.

 

[Golom]

Needs some tweaking ... missed one of the conditions.

Select A.GroupNum, A.Units, G.Band
From myTable A, tblGroups G
Where IIF(A.GroupNum <= 5, G.GroupMem = 5, G.GroupMem = A.GroupNum)
  AND G.UpperLimit = 
     (Select IIF(A.Units >= 250, 250, MIN(G1.UpperLimit))
      From tblGroups G1
      Where G1.UpperLimit >= A.Units OR A.Units >= 250)