IIf statement or DLookup or something else

[darren]

I have a table with text parameters (table 1) and a second table with vlaues
that relate to each parameter from table 1 (table 2). I want to design a
query that will look up the parameters in table 1 and return the assigned
value from table 2.

Example of table 1:
ID soil colour texture
1 sand brown fine
2 clay red coarse



Example of table 2:
ID paramater score
1 sand 10
2 clay 100
3 brown 1
4 red 10
5 fine 10
6 coarse 100

The result of the query will look like this:
ID soil colour texture
1 10 1 10
2 100 10 100

Darren

 

[Duane Hookom]

One SQL method would be a normalizing union query followed by a crosstab query:
[quniOne]
SELECT ID, "Soil" as ColHead, [Soil] As ThePara
FROM Table1
UNION ALL
SELECT ID, "Colour", [Colour]
FROM Table1
UNION ALL
SELECT ID, "Texture", [Texture]
FROM Table1;

Then join quniOne with Table2
[qselTwo]
SELECT quniOne.ID, ColHead, ThePara, Score
FROM quniOne JOIN Table2 On ThePara = [Parameter];

Then create a crosstab based on the qselTwo using ID as the Row Heading,
ColHead as the Column Heading, and Sum(Score) as the Value.