Make field names a variable

[Doug]

I have a query (based on a parameter entered from a form)
which contains data showing survey questions which most
frequently have missing answers. Each row contains the
year and quarter the survey was conducted in addition to
the count of missing answers for each question. I would
like to construct a report showing in descending order for
each fielding period the most frequently missing
questions. I tried doing a straight crosstab, but Access
barked at me because a form parameter is driving the
resultant recordset (I think). Any suggestions?

Thanks....

 

[Michel Walsh]

Hi,

A crosstab requires that ach of its parameter has declared its data type
(under the query designer menu, Query, Parameters).


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I understand that, but how do I convert (dare I say
transpose) the data so that a data set which looks like
this:

A B C
1 2 3

Looks like this:
Var Value
A 1
B 2
C 3

 

[Michel Walsh]

Hi,



SELECT "A" as f1, A As f2 FROM xtab
UNION ALL
SELECT "B" , B FROM xtab
UNION ALL
SELECT "C" , C FROM xtab





Hoping it may help,
Vanderghast, Access MVP

 

[Doug]

Is there a way to do this which is dynamic? I have more
than 100 columns to reorient in this manner.

 

[Michel Walsh]

Hi,


Not really. Ctrl_C, Ctrl_V (cut and paste) or write the string in Excel
(or otherwise) and paste it in the SQL view.



Hoping it may help,
Vanderghast, Access MVP