Use crosstab column heading in a calculation

Is it possible to use the column name, which is a pivot of the first column
in the underlying table, in a calculation?

Essentially, I want to create a matrix, based on the values in the first
field of a recordset. There are 145 records in the rs, values 0-144 in the
first field, "SpotMonth". I want to create a matrix of 145 columns and 145
rows. For argument's sake, let's say I want to display the product of the 1st
field and each of the column headers:

Column: 0 1 2 3 4 etc...
SpotMonth: 0 0 0 0 0 0
1 0 1 2 3 4
2 0 2 4 6 8
3 0 3 6 9 12
4 0 4 8 12 16

Any suggestions?

I've managed to create a crosstab query that pivots the values in the
"SpotMonth" field and uses them as column headers, but I don't know how or if
I can reference that column name value in a calculation.

Thanks,

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com

First you need to check the help and see if a crosstab will handle 145 columns.

You should be able to calculate usinf an IIF statement. The column 'name'
will be from a field with a name of say MyField.
Iif([MyField] ="YourColumnValue", [Field1] * [Field2], 0)


"Bill R via AccessMonster.com" wrote:

Is it possible to use the column name, which is a pivot of the first column
in the underlying table, in a calculation?

Essentially, I want to create a matrix, based on the values in the first
field of a recordset. There are 145 records in the rs, values 0-144 in the
first field, "SpotMonth". I want to create a matrix of 145 columns and 145
rows. For argument's sake, let's say I want to display the product of the 1st
field and each of the column headers:

Column: 0 1 2 3 4 etc...
SpotMonth: 0 0 0 0 0 0
1 0 1 2 3 4
2 0 2 4 6 8
3 0 3 6 9 12
4 0 4 8 12 16

Any suggestions?

I've managed to create a crosstab query that pivots the values in the
"SpotMonth" field and uses them as column headers, but I don't know how or if
I can reference that column name value in a calculation.

Thanks,

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com

Does the contents of a field in a record "know" it's field name? In other
words, is there something logically similar to Int(Me.Fieldname) that I could
use to return an integer from the current record's field name?

I don't know if it was obvious from my example, but the field names (or
column headers) in the crosstab query consist of the values, 0-144, from the
"MonthMaturity" field of the underlying recordset.

KARL DEWEY wrote:
First you need to check the help and see if a crosstab will handle 145 columns.

You should be able to calculate usinf an IIF statement. The column 'name'
will be from a field with a name of say MyField.
Iif([MyField] ="YourColumnValue", [Field1] * [Field2], 0)

Is it possible to use the column name, which is a pivot of the first column
in the underlying table, in a calculation?
[quoted text clipped - 21 lines]

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com

My apologies. In my previous post I said the field name from which the column
headings were drawn was "MonthMaturity". It's "SpotMonth". Sorry about that.

KARL DEWEY wrote:
First you need to check the help and see if a crosstab will handle 145 columns.

You should be able to calculate usinf an IIF statement. The column 'name'
will be from a field with a name of say MyField.
Iif([MyField] ="YourColumnValue", [Field1] * [Field2], 0)

Is it possible to use the column name, which is a pivot of the first column
in the underlying table, in a calculation?
[quoted text clipped - 21 lines]

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com

I do not know where you plan on using the calculations. Post your SQL
statement for the query.

Iif([SpotMonth] = 0, [Field1] * [Field2], 0)
Iif([SpotMonth] = 1, [Field2] * [Field3], 0)


"Bill R via AccessMonster.com" wrote:

My apologies. In my previous post I said the field name from which the column
headings were drawn was "MonthMaturity". It's "SpotMonth". Sorry about that.

KARL DEWEY wrote:
First you need to check the help and see if a crosstab will handle 145 columns.

You should be able to calculate usinf an IIF statement. The column 'name'
will be from a field with a name of say MyField.
Iif([MyField] ="YourColumnValue", [Field1] * [Field2], 0)

Is it possible to use the column name, which is a pivot of the first column
in the underlying table, in a calculation?
[quoted text clipped - 21 lines]

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com

I am trying to construct a matrix based on the values in the 1st field of a
table. There are 145 records with 4 fields. The values in the 1st field range
from 0-144. Consequently, there will be an equal number of columns (145)
whose names are equal to the values in the 1st field of the table (0-144),
resulting in a recordset of 145 records and 145 columns (+ the 4 columns
already in the source table of 145 records).
I have no difficulty whatsoever in running a crosstab query that returns the
145X145 matrix grid. The problem is using the value now embedded in the field
name for calculations for each of the 145 additional fields for each of the
145 records.
The immediate problem is reading the column name of the active field. In
Excel, these field names would correspond to range A1:EO1 and would easily be
referenced for use in calculations. How do I mimic that in Access. How can I
use the values embedded in field names in a calculation, as I would use a
column header in Excel?
Is that clearer? I'm stuck on getting that value out of the field name for
each of the 145 records in the recordset for each of the additional 145
fields.

KARL DEWEY wrote:
I do not know where you plan on using the calculations. Post your SQL
statement for the query.

Iif([SpotMonth] = 0, [Field1] * [Field2], 0)
Iif([SpotMonth] = 1, [Field2] * [Field3], 0)

My apologies. In my previous post I said the field name from which the column
headings were drawn was "MonthMaturity". It's "SpotMonth". Sorry about that.
[quoted text clipped - 10 lines]

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com

OK, Plan "H":

I am now trying to create a temp table based on the crosstab query as follows:


TRANSFORM Sum(SpotMonth) AS CalcMonth
SELECT SpotMonth, Float, sigma
FROM tblSwapParameters
WHERE swapid=[Forms]![frmSwaps]![txtSwapID]
GROUP BY SpotMonth, Float, sigma
PIVOT SpotMonth;

I want to save this query as "qryMatrix" and use it in a query called
"qryMakeTblMatrix". However, when I run the above crosstab query, I get the
following msg:
"the Microsoft Jet Engine does not recognize '[Forms]![frmSwaps]![txtSwapID]'
as a valid field name or expression"
I was trying to do this in a slightly different scenario, using qryParameters
which filtered tblSwapParemeters using the same criteria. qryParameters ran
just fine and delivered a recordset based on the control txtSwapID on the
main form. However, when I ran a crosstab query as above, except using
qryParameters and leaving out the WHERE clause, I got the same msg. The above
query was a vain attempt to get the records more directly.

Any suggestions would be deeply appreciated.

KARL DEWEY wrote:
First you need to check the help and see if a crosstab will handle 145 columns.

You should be able to calculate usinf an IIF statement. The column 'name'
will be from a field with a name of say MyField.
Iif([MyField] ="YourColumnValue", [Field1] * [Field2], 0)

Is it possible to use the column name, which is a pivot of the first column
in the underlying table, in a calculation?
[quoted text clipped - 21 lines]

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com

PS:

The main form is open in form view and qryParameters runs perfectly using the
same criteria. But any crosstab query I design, whether it uses it's own
criteria or uses a query that contains the criteria, generates the same "...
does not recognize..." msg.

Bill R wrote:
OK, Plan "H":

I am now trying to create a temp table based on the crosstab query as follows:

TRANSFORM Sum(SpotMonth) AS CalcMonth
SELECT SpotMonth, Float, sigma
FROM tblSwapParameters
WHERE swapid=[Forms]![frmSwaps]![txtSwapID]
GROUP BY SpotMonth, Float, sigma
PIVOT SpotMonth;

I want to save this query as "qryMatrix" and use it in a query called
"qryMakeTblMatrix". However, when I run the above crosstab query, I get the
following msg:
"the Microsoft Jet Engine does not recognize '[Forms]![frmSwaps]![txtSwapID]'
as a valid field name or expression"
I was trying to do this in a slightly different scenario, using qryParameters
which filtered tblSwapParemeters using the same criteria. qryParameters ran
just fine and delivered a recordset based on the control txtSwapID on the
main form. However, when I ran a crosstab query as above, except using
qryParameters and leaving out the WHERE clause, I got the same msg. The above
query was a vain attempt to get the records more directly.

Any suggestions would be deeply appreciated.

First you need to check the help and see if a crosstab will handle 145 columns.

[quoted text clipped - 7 lines]

Bill


--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com

We may be looking at "Plan I" ;-)

Bill R wrote:
OK, Plan "H":

I am now trying to create a temp table based on the crosstab query as follows:

TRANSFORM Sum(SpotMonth) AS CalcMonth
SELECT SpotMonth, Float, sigma
FROM tblSwapParameters
WHERE swapid=[Forms]![frmSwaps]![txtSwapID]
GROUP BY SpotMonth, Float, sigma
PIVOT SpotMonth;

I want to save this query as "qryMatrix" and use it in a query called
"qryMakeTblMatrix". However, when I run the above crosstab query, I get the
following msg:
"the Microsoft Jet Engine does not recognize '[Forms]![frmSwaps]![txtSwapID]'
as a valid field name or expression"
I was trying to do this in a slightly different scenario, using qryParameters
which filtered tblSwapParemeters using the same criteria. qryParameters ran
just fine and delivered a recordset based on the control txtSwapID on the
main form. However, when I ran a crosstab query as above, except using
qryParameters and leaving out the WHERE clause, I got the same msg. The above
query was a vain attempt to get the records more directly.

Any suggestions would be deeply appreciated.

First you need to check the help and see if a crosstab will handle 145 columns.

[quoted text clipped - 7 lines]

Bill


--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com

You must always define the data type of all parameters in crosstab queries.
Select Query-Parameters and enter
[Forms]![frmSwaps]![txtSwapID] datatype

BTW: without reading all your other postings, the solution for your first
posting is to create a query with this sql
TRANSFORM
First([tblSwapParameters].[SpotMonth]*[tblSwapParameters_1].[SpotMonth]) AS
Expr1
SELECT tblSwapParameters.SpotMonth
FROM tblSwapParameters, tblSwapParameters AS tblSwapParameters_1
GROUP BY tblSwapParameters.SpotMonth
PIVOT tblSwapParameters_1.SpotMonth;



--
Duane Hookom
MS Access MVP
--

"Bill R via AccessMonster.com" u9289@uwe wrote in message
news:5a83cfa40ea4a@uwe...
PS:

The main form is open in form view and qryParameters runs perfectly using
the
same criteria. But any crosstab query I design, whether it uses it's own
criteria or uses a query that contains the criteria, generates the same
"...
does not recognize..." msg.

Bill R wrote:
OK, Plan "H":

I am now trying to create a temp table based on the crosstab query as
follows:

TRANSFORM Sum(SpotMonth) AS CalcMonth
SELECT SpotMonth, Float, sigma
FROM tblSwapParameters
WHERE swapid=[Forms]![frmSwaps]![txtSwapID]
GROUP BY SpotMonth, Float, sigma
PIVOT SpotMonth;

I want to save this query as "qryMatrix" and use it in a query called
"qryMakeTblMatrix". However, when I run the above crosstab query, I get
the
following msg:
"the Microsoft Jet Engine does not recognize
'[Forms]![frmSwaps]![txtSwapID]'
as a valid field name or expression"
I was trying to do this in a slightly different scenario, using
qryParameters
which filtered tblSwapParemeters using the same criteria. qryParameters
ran
just fine and delivered a recordset based on the control txtSwapID on the
main form. However, when I ran a crosstab query as above, except using
qryParameters and leaving out the WHERE clause, I got the same msg. The
above
query was a vain attempt to get the records more directly.

Any suggestions would be deeply appreciated.

First you need to check the help and see if a crosstab will handle 145
columns.

[quoted text clipped - 7 lines]

Bill


--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com