query to identify the column headings

  • Thread starter Thread starter Ranjit kurian
  • Start date Start date
R

Ranjit kurian

I have a table like the below formate
Month Amt
JAN 10
FEB 20
JUL 30
Through crosstab query i changed the formate of above table like below
AMT JAN FEB JUL
10 10
20 20
30 30

Now i would like to write a if function to the same crosstab query, if
funtion is something like this if 'JAN is <>0 then AGE(new column) is jan'
And 'if FEB is <>0 then Age is feb' And 'if JUL is <>0 then AGE is Jul'.

The problem iam facing is the column heading are not constant, it keep
changes based on month, but the column count remains same, so is it possible
to identify the columns based on numbers, for example: column JAN always will
be the second column, next month if the second column name changes to Dec,
the above if function should work in the second column irrespective of column
name/heading changes, something like the one we use in excel R1C1 References.
 
SELECT Format(MAX(CDate( " 1 " & [month] & " 2000")), "mmm") FROM tableName


should return one record with one field with the result you want.

Vanderghast, Access MVP
 
Iam little confused of the below select function, i don't want to count the
months i need to count the columns, my column headings are in Month Names and
these month name keep changes because of which iam unable to write a 'IF'
function

(if funtion is something like this if 'JAN is notequal to 0 then AGE(new
column) is jan'
And 'if FEB is notequal to 0 then Age is feb' And 'if JUL is notequal to 0
then AGE is Jul'.)

for example: column JAN always will be the second column, next month if the
second column name changes to Dec, the above if function should work in the
second column irrespective of column name/heading changes,

iam not that expert in query, could you please explain me the below select
query what i need to type in "1" [month] , "2000", "mmm" etc....
SELECT Format(MAX(CDate( " 1 " & [month] & " 2000")), "mmm") FROM tableName

Michel Walsh said:
SELECT Format(MAX(CDate( " 1 " & [month] & " 2000")), "mmm") FROM tableName


should return one record with one field with the result you want.

Vanderghast, Access MVP



Ranjit kurian said:
I have a table like the below formate
Month Amt
JAN 10
FEB 20
JUL 30
Through crosstab query i changed the formate of above table like below
AMT JAN FEB JUL
10 10
20 20
30 30

Now i would like to write a if function to the same crosstab query, if
funtion is something like this if 'JAN is <>0 then AGE(new column) is jan'
And 'if FEB is <>0 then Age is feb' And 'if JUL is <>0 then AGE is Jul'.

The problem iam facing is the column heading are not constant, it keep
changes based on month, but the column count remains same, so is it
possible
to identify the columns based on numbers, for example: column JAN always
will
be the second column, next month if the second column name changes to Dec,
the above if function should work in the second column irrespective of
column
name/heading changes, something like the one we use in excel R1C1
References.
Click to expand...
Click to expand...
 
"1 " & [month] & " 2000"


generates a valid date as a string (the date is in year 2000, but that is
irrelevant, any year will do, but we must have a year to have a valid date).


CDate( ) effectively converts the string into a date-value.


MAX( ) over all the original table find the latest date we just generated,
over all the possible values of the initial table. The MAX effectively
occurs over all the possible value of the initial table since there is no
explicit GROUP BY.


Format( ..., "mmm" ) gives back the month name (3 letter) of the said
date.


SELECT ... return the single record, single field, but as a query does
(not directly accessible to VBA, as any query is, nothing new here). You can
try to run the query from the user interface to see if it really works,
though, returning the 3 letters month name of the 'latest' month present in
the initial data.


Vanderghast, Access MVP




Ranjit kurian said:
Iam little confused of the below select function, i don't want to count
the
months i need to count the columns, my column headings are in Month Names
and
these month name keep changes because of which iam unable to write a 'IF'
function

(if funtion is something like this if 'JAN is notequal to 0 then AGE(new
column) is jan'
And 'if FEB is notequal to 0 then Age is feb' And 'if JUL is notequal to
0
then AGE is Jul'.)

for example: column JAN always will be the second column, next month if
the
second column name changes to Dec, the above if function should work in
the
second column irrespective of column name/heading changes,

iam not that expert in query, could you please explain me the below select
query what i need to type in "1" [month] , "2000", "mmm" etc....
SELECT Format(MAX(CDate( " 1 " & [month] & " 2000")), "mmm") FROM
tableName

Michel Walsh said:
SELECT Format(MAX(CDate( " 1 " & [month] & " 2000")), "mmm") FROM
tableName


should return one record with one field with the result you want.

Vanderghast, Access MVP



Ranjit kurian said:
I have a table like the below formate
Month Amt
JAN 10
FEB 20
JUL 30
Through crosstab query i changed the formate of above table like below
AMT JAN FEB JUL
10 10
20 20
30 30

Now i would like to write a if function to the same crosstab query, if
funtion is something like this if 'JAN is <>0 then AGE(new column) is
jan'
And 'if FEB is <>0 then Age is feb' And 'if JUL is <>0 then AGE is
Jul'.

The problem iam facing is the column heading are not constant, it keep
changes based on month, but the column count remains same, so is it
possible
to identify the columns based on numbers, for example: column JAN
always
will
be the second column, next month if the second column name changes to
Dec,
the above if function should work in the second column irrespective of
column
name/heading changes, something like the one we use in excel R1C1
References.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top