I have three tables, each with 10 columns and the same number of rows. Table Indicator has either a 1 or a 2 in each cell. Tables 1 and 2 have numerical data.
The position of the rows is consistent between tables. Row m column n in the Indicator table indicates whether to select row m column n from Table 1 or row m column n from Table 2.
I want to make a new table with 10 columns and the same number of rows as the other tables. For each cell, if Indicator has a 1 for that cell, pull the value from Table 1, and if Indicator has a 2 for that cell, pull the value from Table 2.
In Excel if I had three tabs with 10 columns and some number of rows, it would be something like
=if(Indicator!B1=1, Table1!B1, Table2!B1)
copied across the whole range.
How do I this in Access?
Join the two tables and use an iif statement.
Use a join predicate to match the corresponding lines.
Indicator (id, col1, ..., col10)
DataA (indicatorId, col1, ..., col10)
DataB (indicatorId, col1, ..., col10)
SELECT i.id,
iif(i.col1=1, a.col1,b.col1) as col1,
...,
iif(i.col10=1, a.col10,b.col10) as col10
FROM (Indicator AS i
INNER JOIN DataA AS a ON (i.id=a.indicatorId))
INNER JOIN DataB AS b ON (i.id=b.indicatorId))
ORDER BY i.id
This syntax is Access-specific and won't port to other SQL engines; e.g. MS SQL will need WHEN...THEN...ELSE...END instead of iif.
Such queries will have bad performance because of the conditionals in the select part.