Query to get the field name?

A

Amit

MS Access 2000, Windows XP
=======================
Hi,

Is there a way to run a query to get the field names from a table, instead
of hard-coding it?

An illustration will be helpful.

tblA has the following 5 fields, with [ID+Month] being the Primary Key.
====
ID
A_Process
B_Handout
C_Training
Month

The values in the table are:
ID A_Process B_Handout C_Training Month
-------------------------------------------------------
1 1 2 3
Jan
2 4 5 6
Jan
1 1 1 1
Feb
2 2 2 2
Feb
.....

What I'd like to get using a query is:

ID Q_Name Jan Feb
----------------------------------
1 A_Process 1 1
1 A_Handout 2 1
1 A_Training 3 1
2 A_Process 4 2
2 A_Handout 5 2
2 A_Training 6 2
......

I've tried designing a cross-tab query, but I have to hard-code the field
names. Is there a way to get the field names and the corresponding value
without hard-coding them?

Will appreciate any help.

Thanks.

-Amit
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You've designed the table incorrectly. It probably should be like this:

CREATE TABLE A (
id COUNTER , -- JET AutoNumber data type
item_month BYTE NOT NULL ,
item_type VARCHAR(10) NOT NULL , -- could be an Integer code
item_count INTEGER NOT NULL,
CONSTRAINT PK_A PRIMARY KEY (id, item_month, item_type)
)

Data would be like this:

id item_month item_type item_count
1 1 A_Process 1
1 1 B_Handout 2
1 1 C_Training 3
2 1 A_Process 4
2 1 B_Handout 5
2 1 C_Training 6
.... etc. ...

Cross-tab query like this:

TRANSFORM SUM(item_count) As theValue
SELECT id, item_type
FROM A
GROUP BY id, item_type
PIVOT item_month

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQhT2FoechKqOuFEgEQKjQACgmvy9Y7fHP1BTbrFSlMERllT0DFcAoN+L
opBWt0hKVJOnIRzDxyviZNTu
=0S1o
-----END PGP SIGNATURE-----
 
A

Amit

Hi,

I *did* design the table the way you mentioned. And, I can get what I want
using a cross-tab query. But, your response did not answer my main question.
How can I get the field names from one table as VALUES into another table or
query? The table has some 30+ fields, and I am looking for a way to not
hard-code/type in the field names into the new table.

-Amit

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You've designed the table incorrectly. It probably should be like this:

CREATE TABLE A (
id COUNTER , -- JET AutoNumber data type
item_month BYTE NOT NULL ,
item_type VARCHAR(10) NOT NULL , -- could be an Integer code
item_count INTEGER NOT NULL,
CONSTRAINT PK_A PRIMARY KEY (id, item_month, item_type)
)

Data would be like this:

id item_month item_type item_count
1 1 A_Process 1
1 1 B_Handout 2
1 1 C_Training 3
2 1 A_Process 4
2 1 B_Handout 5
2 1 C_Training 6
.... etc. ...

Cross-tab query like this:

TRANSFORM SUM(item_count) As theValue
SELECT id, item_type
FROM A
GROUP BY id, item_type
PIVOT item_month

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQhT2FoechKqOuFEgEQKjQACgmvy9Y7fHP1BTbrFSlMERllT0DFcAoN+L
opBWt0hKVJOnIRzDxyviZNTu
=0S1o
-----END PGP SIGNATURE-----

MS Access 2000, Windows XP
=======================
Hi,

Is there a way to run a query to get the field names from a table, instead
of hard-coding it?

An illustration will be helpful.

tblA has the following 5 fields, with [ID+Month] being the Primary Key.
====
ID
A_Process
B_Handout
C_Training
Month

The values in the table are:
ID A_Process B_Handout C_Training Month
-------------------------------------------------------
1 1 2 3
Jan
2 4 5 6
Jan
1 1 1 1
Feb
2 2 2 2
Feb
....

What I'd like to get using a query is:

ID Q_Name Jan Feb
----------------------------------
1 A_Process 1 1
1 A_Handout 2 1
1 A_Training 3 1
2 A_Process 4 2
2 A_Handout 5 2
2 A_Training 6 2
.....

I've tried designing a cross-tab query, but I have to hard-code the field
names. Is there a way to get the field names and the corresponding value
without hard-coding them?

Will appreciate any help.

Thanks.

-Amit
Click to expand...
Click to expand...
 
M

[MVP] S.Clark

I think the answer lies in better normalization. If your table were
structured as the following:

ID
Month
Code -> A, B, C, etc.
Value

Then it would be better geared for use within Access Queries.
 
A

Amit

Hi Steve,

Thanks for your response.
Well, it's too late to change the table design at this point, though I do
believe the table was designed correctly, even if it is not normalized (it's
based on a survey questionnaire). But, that is not the issue. Maybe the way I
presented my question was not clear. Let me re-try:

Is there a way to get the field names from one table into a second table
using a query without actually typing in the field names? The second table
has only one field called "fieldName", and its VALUES are the field names of
the first table. Maybe using a form and SQL statement?

Thanks.

-Amit
 
Top