field contents in one table as field name in another ?

[pancake]

Is it possible to use the contents of a field in one table to reference a field name in another. For example

T1
ID RefName Description
1 f1 Fruit
2 f2 Chocolate
3 f3 Coffee
etc...

T2
ID f1 f2 f3
1 a a b
2 b b a
3 b c b
etc...

my SQL statement should be something like

SELECT RefName
(SELECT count("f1") from t2 where f1='a'),
(SELECT count("f1") from t2 where f1='b'),
(SELECT count("f1") from t2 where f1='c')
from t1

The "f1" should change for each row so that I can count column f1, f2 and f3 per row

The results should look like this
CategoryA CategoryB CategoryC
Fruit 1 2 0
Chocolate 1 1 1
Coffee 1 2 0

Thanks in advance for any help.

 

[dhookom]

This can be done by first normalizing your T2 table with a union query (my play mdb already had T1 and T2 tables so I named yours T_1 and T_2):
== query.quniT_2 =====
[tt][green]
SELECT ID, f1 AS f, "f1" AS Ref
FROM T_2
UNION ALL
SELECT ID, f2, "f2"
FROM T_2
UNION ALL SELECT ID, f3, "f3"
FROM T_2;
[/green][/tt]
=======================
Then create a crosstab query based on T_1 and quniT_2
[tt][green]
TRANSFORM Val(Nz(Count([f]),0)) AS Expr1
SELECT T_1.Description
FROM T_1 INNER JOIN quniT_2 ON T_1.RefName = quniT_2.Ref
GROUP BY T_1.Description
PIVOT quniT_2.f;
[/green][/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pancake]

Wow, this looks trickier than I thought.

I have somewhere in the region of 100 fields to normalize so my first query will be very long....

I will have a go with a few and let you know what happens.

Thanks for your response, much appreciated.

 

[dhookom]

I doubt a union query with 100 fields/unions will work. You may need to append records to a normalized table using either a series of union queries or code.

I suppose you can't change your un-normalized structure...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]