index function

[aleb]

Can anyone help me or direct to ...
I need the function in access which will act similar to the index function in excell .

 

[WayneRyan]

aleb,

Just read the Excel help on Index. I don't see how it applies to Access.
Tables aren't spreadsheets where you have row/column coordinates.

Maybe you need something like the DLookUp function, which retrieves a
column from the row that matches the criteria.

More info needed ...

Wayne

 

[aleb]

Here is the history of an issue
There are 4 potential threats
Financial
Environmental
Pulic concern
Safety

Two criteria to assess the threat
severity of consequencies (A)
probability of occurence (B)
Both criteria has values from 1 to 4

So crossection of (A) and (B) gives you the value.
I know that I can do it with iif function
but there will be 16 cases.
is there any function or can I create a function which will

a1(b1,b2,b3,b4)
a2(b1,b2,b3,b4) ...

4 3 2 1
4 1 1 1 2
3 1 2 3 4
2 1 3 4 5
1 2 4 5 5

This is the table

 

[Pat Hartman]

I don't understand your explaination but I think the problem is that your table is not normalized. You seem to be showing a matrix and you want to find the "cell" that is the intersection of a row and column. Tables don't work that way. They do NOT have "cells". Only columns are addressable in a table. The rows returned by a query are defined based on the queries selection criteria.

Instead of having a 4x4 matrix, a table will have 16 rows with a two-field primary key. You supply fldA and fldB and a query will return the correct ROW. So your table will look like:

[COLOR=Red]Primary Key[/COLOR], [COLOR=RoyalBlue]Answer[/COLOR]
[COLOR=Red]Financial         4[/COLOR], [COLOR=RoyalBlue]4[/COLOR] 
[COLOR=Red]Financial         3[/COLOR], [COLOR=RoyalBlue]1[/COLOR]
[COLOR=Red]Financial         2[/COLOR], [COLOR=RoyalBlue]1[/COLOR]
[COLOR=Red]Financial         1[/COLOR], [COLOR=RoyalBlue]1[/COLOR]
[COLOR=Red]Environmental  4[/COLOR], [COLOR=RoyalBlue]3[/COLOR]
[COLOR=Red]Environmental  3[/COLOR], [COLOR=RoyalBlue]1[/COLOR]
[COLOR=Red]Environmental  2[/COLOR], [COLOR=RoyalBlue]2[/COLOR]
[COLOR=Red]Environmental  1[/COLOR], [COLOR=RoyalBlue]3[/COLOR]
[COLOR=Red]Pulic concern   4[/COLOR], [COLOR=RoyalBlue]2[/COLOR]
[COLOR=Red]Pulic concern   3[/COLOR], [COLOR=RoyalBlue]1[/COLOR]
[COLOR=Red]Pulic concern   2[/COLOR], [COLOR=RoyalBlue]3[/COLOR]
[COLOR=Red]Pulic concern   1[/COLOR], [COLOR=RoyalBlue]4[/COLOR]
[COLOR=Red]Safety            4[/COLOR], [COLOR=RoyalBlue]1[/COLOR] 
[COLOR=Red]Safety            3[/COLOR], [COLOR=RoyalBlue]2[/COLOR]
[COLOR=Red]Safety            2[/COLOR], [COLOR=RoyalBlue]4[/COLOR]
[COLOR=Red]Safety            1[/COLOR], [COLOR=RoyalBlue]5[/COLOR]

 

[Jon K]

I think Wayne has pointed to the right direction. From a simple matrix of 4 by 4, the DLookup() function should be efficient enough to get the value at any cross section.

I once posted a solution to a similar matrix using DLookup().
http://www.access-programmers.co.uk/forums/showthread.php?t=40382

This has the advantage that, if the data in the matrix changes from time to time and comes from an outside source such as a spreadsheet or a text file, you don't need to make any changes to the source table whenever a new one arrives.
.