Using a variable to decide which field to get a value from

[Michael Slade]

I have two tables, T1 and T2.

Table T1 has F1 and F2



Table 2 has additional fields F3, F4, F5, F6, F7.

Fields F1 and F3 are the same so that I can form a relation between the two
Tables.

In the field F2 in Table1, the only values are 'F4', 'F5', 'F6' and 'F7'.

Depending on the value in Table1, F2, I want to get the value in Table2 from
the field indicated by F2.

Is there a query that I can build which will use F2 contents as a variable
field to choose?

If a query won't do this, any pointers to a simple VB function which will
work or can be modified to do this?

thanks
Michael Slade

 

[Douglas J. Steele]

Is it too late to change your database design so that it's properly
normalized, instead of what you've described?

What would make far more sense is for T2 to have 4 separate rows for each
separate value in F3. It should have 3 fields: F3, FieldName and FieldValue.

Then, you can easily have something like:

SELECT T1.F1, T2.FieldValue
FROM T1 INNER JOIN T2
ON T1.F1 = T3.F3
AND T1.F2 = T3.FieldName

 

[Michael Slade]

Far too late to change - it is a database supplied to me.

From the standpoint of how the data is collected, the present design is very
efficient and effective.

But now I have to get the data out that I need!

Perhaps I didn't describe the situation properly.

Each field - F4, F5,F6, and F7in Table2 all have data in them. I need to
develop a query that will read the appropriate field in Table2 based on
which the data (field name) in F1 in Table1. F1 from Table1 and F3 in Table2
have the same value in each corresponding record so that I can form the
relation between the two tables.

Any other ideas?

thanks
Michael Slade

 

[Andreas]

Use a nested IIF function:
IIF(Table1.F2 = "F4", Table2.F4, IIF(Table1.F2 = "F5", Table2.F5,
IIF(Table1.F2 = "F6", Table2.F6, IIF(Table1.F2 = "F7", Table2.F7,
"Invalid Value"))))

Regards,
Andreas

 

[Michael Slade]

Definitely would work but..

I simplified the problem for my example - there are about 50 possible
columns!

Any other ideas?

I'm starting to think function now!

thanks again
Michael Slade

 

[John Vinson]

I have two tables, T1 and T2.

Table T1 has F1 and F2



Table 2 has additional fields F3, F4, F5, F6, F7.

Any chance you could use a Normalizing Union Query to migrate this
spreadsheet into something more resembling a table?

You'll need to build the SQL of your query in VBA code. There's no way
to do this in a Query, AFAIK.

John W. Vinson[MVP]

 

[Andreas]

3 possible solutions:

1)
Use the IIF function for sets of 7 columns, returning Null if it is not
a match. Then use another IIF function to pick the result where the
result is not Null.

2)
Use a function.
Yuk!

3)
User a Union query to "Normalize" Table2.
Then use a query with Table1 and the result of the union query, linking
by the key fields and Table1.F2 to Query1.(the new column which is the
name of the field).

Regards,
Andreas

 

[Van T. Dinh]

Check Access Help on the Switch function.

There will be other problems with the present strcuture later. I would
recommend restructure your Tables as per Douglas' advice.

 

[Douglas J. Steele]

Create a query that normalizes the data for you, then.

SELECT F3, "F4" As FieldName, F4 As FieldValue
FROM T2
UNION
SELECT F3, "F5" As FieldName, F5 As FieldValue
FROM T2
UNION
SELECT F3, "F6" As FieldName, F6 As FieldValue
FROM T2
....

 

[Marshall Barton]

I have two tables, T1 and T2.

Table T1 has F1 and F2

Table 2 has additional fields F3, F4, F5, F6, F7.

Fields F1 and F3 are the same so that I can form a relation between the two
Tables.

In the field F2 in Table1, the only values are 'F4', 'F5', 'F6' and 'F7'.

Depending on the value in Table1, F2, I want to get the value in Table2 from
the field indicated by F2.

Is there a query that I can build which will use F2 contents as a variable
field to choose?

If a query won't do this, any pointers to a simple VB function which will
work or can be modified to do this?

Well, if you can't change the table design, you can use the
DLookup function to get the related field's value:

SELECT T1.F1, DLookup(T1.F2, "T2", "F3=" & T1.F1) As F
FROM T1

That's going to have rather poor peformance, so if you have
a lot of data in these tables, you would probably be better
off creating an additional table with a normalized version
of the data in table two.