Query to get a field's name

  • Thread starter Thread starter Boon
  • Start date Start date
B

Boon

Hi,

I am creating a query that I want to get the field name. How can I do this.

I have a table with 2 fields. (Loc_ID (TEXT) ; Rate (DOUBLE))

I want to create a qeury that shows the result in this format (Loc_ID (TEXT)
; ITEM (TEXT) ; NUMBER (DOUBLE))

in the ITEM field I want every row has a word "Rate" which is the same name
as the field's name in my original table. The point is I do not want to
manually type "Rate" in the query every time. I want to have the query
extract the field's name automatically.

Thanks a lot.
Boon
 
Here is my interpretation of what you have and what you want:
you have:
Loc_ID Rate
xxx 1.2
yyy 3.4

you want

Loc_ID ITEM NUMBER
xxx Rate 1.2
yyy Rate 3.4

if this is correct, then the SQL for the query if your table name is table1
is:

SELECT table1.Loc_ID, "Rate" AS ITEM, table1.Rate AS [NUMBER]
FROM table1;
 
Thanks for your answer.You are absolutely right. However, your query
requires to hardcode the word "Rate" in the SQL code. I want the SQL code to
be robust and can detect the name of the 3rd column in table1.
Loc_ID Price
xxx 1.2
yyy 3.4

you want

Loc_ID ITEM NUMBER
xxx Price 1.2
yyy Price 3.4
Click to expand...

For example, the SQL code must be work with this table as well without any
adjustment.

Thanks,
Boon


Bruce Meneghin said:
Here is my interpretation of what you have and what you want:
you have:
Loc_ID Rate
xxx 1.2
yyy 3.4

you want

Loc_ID ITEM NUMBER
xxx Rate 1.2
yyy Rate 3.4

if this is correct, then the SQL for the query if your table name is
table1
is:

SELECT table1.Loc_ID, "Rate" AS ITEM, table1.Rate AS [NUMBER]
FROM table1;


Boon said:
Hi,

I am creating a query that I want to get the field name. How can I do
this.

I have a table with 2 fields. (Loc_ID (TEXT) ; Rate (DOUBLE))

I want to create a qeury that shows the result in this format (Loc_ID
(TEXT)
; ITEM (TEXT) ; NUMBER (DOUBLE))

in the ITEM field I want every row has a word "Rate" which is the same
name
as the field's name in my original table. The point is I do not want to
manually type "Rate" in the query every time. I want to have the query
extract the field's name automatically.

Thanks a lot.
Boon
Click to expand...
Click to expand...
 
Back
Top