If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Use crosstab column heading in a calculation
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 |
#3
|
|||
|
|||
Use crosstab column heading in a calculation
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 |
#4
|
|||
|
|||
Use crosstab column heading in a calculation
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 |
#5
|
|||
|
|||
Use crosstab column heading in a calculation
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 |
#6
|
|||
|
|||
Use crosstab column heading in a calculation
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 |
#7
|
|||
|
|||
Use crosstab column heading in a calculation
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 |
#8
|
|||
|
|||
Use crosstab column heading in a calculation
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 |
#9
|
|||
|
|||
Use crosstab column heading in a calculation
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 |
#10
|
|||
|
|||
Use crosstab column heading in a calculation
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to Improve Code Copying/Pasting Between Workbooks | David | General Discussion | 1 | January 6th, 2006 03:56 AM |
creating a bar graph | Johnfli | General Discussion | 0 | October 26th, 2005 08:16 PM |
Crosstab Column Heading Sort | MJatAflac | Running & Setting Up Queries | 3 | June 29th, 2005 01:54 AM |
How do I set up a report using dates as my report header? | Robin | Setting Up & Running Reports | 16 | November 13th, 2004 01:00 PM |
Using Validation to force entry into cells? | Mark | General Discussion | 16 | October 27th, 2004 09:23 PM |