Dynamic fields of query

[Tim]

Hi folks,

I have a tough question which I spent a lot of time but I couldn't find
figure out how to solve it. I have two tables.

TableA:
Name, Product1, Product2, Product3
Joe,15,6,8

TableB:
Des,Flag
Name,x
Product1
Product2,X
Product3

I need to create a query for TableA. The query will only show the fields of
TabaleA if TableB has x in Flag field. If "X" in TableB is change, the field
of query will be changed, too.

Could anyone show me the SQL?

Thanks in advace.
Tim.

 

[KARL DEWEY]

You need to change the structure of tableA to look like this --
Name Product
Joe 15
Joe 6
Joe 8

Use a union query to do that --
SELECT Name, Product1 AS Product
FROM tableA
UNION ALL SELECT Name, Product2 AS Product
FROM tableA
UNION ALL SELECT Name, Product3 AS Product
FROM tableA;

TableB needs to look like the new tableA. There is no easy solution that I
know of to move your old tableB data to the new version.

If your products are loaded as numbers as shown for tableA then it could be
done. You could create a translation table for product title to number and
run an update or make temp table.