I have created a crosstab query based on this table.
VALUES
__________
Oil_ID (PK)
Property_ID (PK)
Col_Heading (PK) (*112 distinct values)
Row_Heading (PK)
Col_HeadingUnit
Row_HeadingUnit
Values
Oil Name
Property Name
The crosstab query looks like this so far.
PARAMETERS [Forms]![Values Form]![combo0] Text ( 255 ), [Forms]![Values Form]![combo52] Text ( 255 );
TRANSFORM Sum(VALUES.TV_VALUE) AS SumOfTV_VALUE
SELECT VALUES.TV_ROW_LABEL
FROM PROPERTIES INNER JOIN (OILS INNER JOIN [VALUES] ON OILS.O_ID = VALUES.TV_O_ID) ON PROPERTIES.TT_ID = VALUES.TV_TT_ID
WHERE (((PROPERTIES.TT_TITLE)=[Forms]![Values Form]![combo52]) AND ((OILS.O_NAME)=[Forms]![Values Form]![combo0]))
GROUP BY VALUES.TV_TT_ID, VALUES.TV_O_ID, VALUES.TV_ROW_LABEL
PIVOT VALUES.TV_COL_LABEL;
Everything works, but I need to create a report out of this. I get this error when I want to place the crosstab query in my form:
You can't us a passthrough query or a non-fixed-column crosstab query
as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's column headings property.
I know how to get around that error BUT......I don't want to hardcode the column headings, because they might have to be changed in the future and I only want the columns that are related to show up....not all the other ones as well.
For example. Right now if Oil Name = Adgo and Property Name = Density the query outputs this
http://img114.imageshack.us/img114/4082/crosstab4pu.jpg
I need to change this into a report somehow.
This is how the report looks now.
http://img151.imageshack.us/img151/7749/report1he.jpg
But I need to change it to look like the above crosstab query result. (The volume% is the columns).
Please help!
VALUES
__________
Oil_ID (PK)
Property_ID (PK)
Col_Heading (PK) (*112 distinct values)
Row_Heading (PK)
Col_HeadingUnit
Row_HeadingUnit
Values
Oil Name
Property Name
The crosstab query looks like this so far.
PARAMETERS [Forms]![Values Form]![combo0] Text ( 255 ), [Forms]![Values Form]![combo52] Text ( 255 );
TRANSFORM Sum(VALUES.TV_VALUE) AS SumOfTV_VALUE
SELECT VALUES.TV_ROW_LABEL
FROM PROPERTIES INNER JOIN (OILS INNER JOIN [VALUES] ON OILS.O_ID = VALUES.TV_O_ID) ON PROPERTIES.TT_ID = VALUES.TV_TT_ID
WHERE (((PROPERTIES.TT_TITLE)=[Forms]![Values Form]![combo52]) AND ((OILS.O_NAME)=[Forms]![Values Form]![combo0]))
GROUP BY VALUES.TV_TT_ID, VALUES.TV_O_ID, VALUES.TV_ROW_LABEL
PIVOT VALUES.TV_COL_LABEL;
Everything works, but I need to create a report out of this. I get this error when I want to place the crosstab query in my form:
You can't us a passthrough query or a non-fixed-column crosstab query
as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's column headings property.
I know how to get around that error BUT......I don't want to hardcode the column headings, because they might have to be changed in the future and I only want the columns that are related to show up....not all the other ones as well.
For example. Right now if Oil Name = Adgo and Property Name = Density the query outputs this
http://img114.imageshack.us/img114/4082/crosstab4pu.jpg
I need to change this into a report somehow.
This is how the report looks now.
http://img151.imageshack.us/img151/7749/report1he.jpg
But I need to change it to look like the above crosstab query result. (The volume% is the columns).
Please help!
