Use crosstab column heading in a calculation

[Bill R via AccessMonster.com]

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

 

[Guest]

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]

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.

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 R via 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.

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

 

[Guest]

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.

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 R via 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.

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 R via 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.

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 R via 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.

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 R via AccessMonster.com]

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

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]

 

[Duane Hookom]

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
--

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.

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 R via AccessMonster.com]

Bingo!!!

Thanks a million, Duane.

Bill

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;

PS:

[quoted text clipped - 42 lines]

 

[Bill R via AccessMonster.com]

Duane,

Now that I've got the simple calculation working, is it possible to include a
value from a control on a form in the calculation "Expr1"? What are the
restrictions on calculations in a crosstab query? I've already come across a
problem in using a control on the main form as criteria in a crosstab query
or even in an underlying query for a crosstab query.

If I can't use data from a control on a form, I will fall back to "Plan I"
and run a make table query based on this crosstab query, and then populate
the records and fields using a calculation in VBA.

Thanks,

Bill

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;

PS:

[quoted text clipped - 42 lines]

 

[Duane Hookom]

You can use a reference to a control on a form in the same way that you
would hard-code a value. As I stated earlier, crosstabs expect you to
identify the data types of all parameters.

--
Duane Hookom
MS Access MVP
--

Duane,

Now that I've got the simple calculation working, is it possible to
include a
value from a control on a form in the calculation "Expr1"? What are the
restrictions on calculations in a crosstab query? I've already come across
a
problem in using a control on the main form as criteria in a crosstab
query
or even in an underlying query for a crosstab query.

If I can't use data from a control on a form, I will fall back to "Plan I"
and run a make table query based on this crosstab query, and then populate
the records and fields using a calculation in VBA.

Thanks,

Bill

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;

PS:

[quoted text clipped - 42 lines]

Bill

 

[Bill R via AccessMonster.com]

Thanks,

I finally figured out where to set the datatype of the parameter. I had been
looking in query properties, but it's in the Query menu in the toolbar.

Bill

You can use a reference to a control on a form in the same way that you
would hard-code a value. As I stated earlier, crosstabs expect you to
identify the data types of all parameters.

Duane,

[quoted text clipped - 36 lines]

 

[Bill R via AccessMonster.com]

Duane,

Now that I've got all the calculations working, I would like to use this
crosstab query as the datasource for a subform. I would prefer a continuous
subform to a datasheet. How do I display in a subform, a datasource whose
field names and the number of fields will change depending on the results of
the crosstab query?

Bill

You can use a reference to a control on a form in the same way that you
would hard-code a value. As I stated earlier, crosstabs expect you to
identify the data types of all parameters.

Duane,

[quoted text clipped - 36 lines]

 

[Duane Hookom]

You don't ask for much do you ;-)

You can only show records in a subform where you know the fields so you can
set the control sources. I have not seen solutions for dynamic subforms. I
have created solutions for dynamic fields in reports.

I do create a somewhat dynamic subform in the Query By Form applet available
for download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

Duane,

Now that I've got all the calculations working, I would like to use this
crosstab query as the datasource for a subform. I would prefer a
continuous
subform to a datasheet. How do I display in a subform, a datasource whose
field names and the number of fields will change depending on the results
of
the crosstab query?

Bill

You can use a reference to a control on a form in the same way that you
would hard-code a value. As I stated earlier, crosstabs expect you to
identify the data types of all parameters.

Duane,

[quoted text clipped - 36 lines]

Bill

 

[Bill R via AccessMonster.com]

I wouldn't keep asking if you guys weren't so damn helpful!!! ;-)

Thanks for the advice,

Bill

You don't ask for much do you ;-)

You can only show records in a subform where you know the fields so you can
set the control sources. I have not seen solutions for dynamic subforms. I
have created solutions for dynamic fields in reports.

I do create a somewhat dynamic subform in the Query By Form applet available
for download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane,

[quoted text clipped - 17 lines]

 

[Bill R via AccessMonster.com]

Duane,

I found a thread that purports to have solved the problem, but I can't seem
to make it work. Here's the thread:

http://www.accessmonster.com/Uwe/Fo...ay-crosstab-query-in-subform#5a8dd03e6294fuwe


Here's my code in the On Current event of the main form:

Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As QueryDef

Set db = CurrentDb

strSQL = "PARAMETERS [Forms]![frmSwaps]![SwapID] Long, [Forms]![frmSwaps]!
[Conf95] IEEEDouble;" & vbCrLf
strSQL = strSQL & "TRANSFORM ..." & vbCrLf
strSQL = strSQL & "SELECT ..." & vbCrLf
strSQL = strSQL & "FROM tblSwapParameters AS Params, Params2" & vbCrLf
strSQL = strSQL & "WHERE swapid=[Forms]![frmSwaps]![SwapID]" & vbCrLf
strSQL = strSQL & "GROUP BY swapid, ..." & vbCrLf
strSQL = strSQL & "PIVOT CalcMonth;"

Set qdf = db.QueryDefs("qryFwdPrices")
qdf.SQL = strSQL
Me!sfrmFwdPrices.SourceObject = "Query.qryFwdPrices"
Set db = Nothing

End Sub

Can you see where I have strayed from Jesper's example?

Thanks,

Bill

You don't ask for much do you ;-)

You can only show records in a subform where you know the fields so you can
set the control sources. I have not seen solutions for dynamic subforms. I
have created solutions for dynamic fields in reports.

I do create a somewhat dynamic subform in the Query By Form applet available
for download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane,

[quoted text clipped - 17 lines]

 

[Bill R via AccessMonster.com]

When I open the form I get the msg:

"You can't use a pass-through query or a non-fixed-column crosstab query as a
record source for a subform or a subreport.
Before you bind a subform or a subreport to a crosstab query, set the query's
ColumnHeadings property"

You don't ask for much do you ;-)

You can only show records in a subform where you know the fields so you can
set the control sources. I have not seen solutions for dynamic subforms. I
have created solutions for dynamic fields in reports.

I do create a somewhat dynamic subform in the Query By Form applet available
for download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane,

[quoted text clipped - 17 lines]

 

[Duane Hookom]

I would use code to set the parameter values in the sql like:
strSQL = strSQL & "TRANSFORM ..." & vbCrLf
strSQL = strSQL & "SELECT ..." & vbCrLf
strSQL = strSQL & "FROM tblSwapParameters AS Params, Params2 " & vbCrLf
strSQL = strSQL & "WHERE swapid=" & [Forms]![frmSwaps]![SwapID] & vbCrLf
strSQL = strSQL & " And somefield=" & [Forms]![frmSwaps]![Conf95] & vbCrLf
strSQL = strSQL & " GROUP BY swapid, ..." & vbCrLf
strSQL = strSQL & " PIVOT CalcMonth;"


--
Duane Hookom
MS Access MVP
--

Duane,

I found a thread that purports to have solved the problem, but I can't
seem
to make it work. Here's the thread:

http://www.accessmonster.com/Uwe/Fo...ay-crosstab-query-in-subform#5a8dd03e6294fuwe


Here's my code in the On Current event of the main form:

Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As QueryDef

Set db = CurrentDb

strSQL = "PARAMETERS [Forms]![frmSwaps]![SwapID] Long, [Forms]![frmSwaps]!
[Conf95] IEEEDouble;" & vbCrLf
strSQL = strSQL & "TRANSFORM ..." & vbCrLf
strSQL = strSQL & "SELECT ..." & vbCrLf
strSQL = strSQL & "FROM tblSwapParameters AS Params, Params2" & vbCrLf
strSQL = strSQL & "WHERE swapid=[Forms]![frmSwaps]![SwapID]" & vbCrLf
strSQL = strSQL & "GROUP BY swapid, ..." & vbCrLf
strSQL = strSQL & "PIVOT CalcMonth;"

Set qdf = db.QueryDefs("qryFwdPrices")
qdf.SQL = strSQL
Me!sfrmFwdPrices.SourceObject = "Query.qryFwdPrices"
Set db = Nothing

End Sub

Can you see where I have strayed from Jesper's example?

Thanks,

Bill

You don't ask for much do you ;-)

You can only show records in a subform where you know the fields so you
can
set the control sources. I have not seen solutions for dynamic subforms. I
have created solutions for dynamic fields in reports.

I do create a somewhat dynamic subform in the Query By Form applet
available
for download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane,

[quoted text clipped - 17 lines]

Bill