how to make a query based on matrix

[kbd]

I have a very complex matrix of 15 columns and 70 rows. The matirx has 49
potential results. Each of those results in one of 17 codes. I have 20000
lines of data that have the various factors that are part of the matirx and
need to eventually end up with one of the 17 codes. I am stumped as to how to
start. There are too many combinations in the matirx to consider iif
statements. I can't think of anything else to try. Any help out there???

 

[Allen Browne]

Normalize the data.

I am assuming that the 15 columns are 15 possible things (like prices of 15
products), and the 70 rows are 70 other entities (such as 70 countries), so
the matrix give you all the combinations.

If so you need to create these tables:
a) Price table
15 records (being the 15 things in the matrix header)

b) Country table
70 records (being the 70 things at the left of the matrix.)

c) ValueCode table
17 records (being the 17 possible results.)

d) CountryPrice table, with fields:
PriceID relates to primary key of Price table
CountryID relates to primary key of Country table
ValueCodeID relates to primary key of ValueCode table.

You can populate table (d) by running an Append query 15 times (one for each
column of your matrix.)

Once table (d) is populated like that, you can easily discover the ValueCode
for price and country, e.g. with DLookup().

Hope I have understood your data correctly. If these are ranges rather than
discrete values, you may need this as well:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html