Crosstab query

[BMusic]

I have a base query (qselDEQExport2_Test) with the following SQL statement

SELECT qselChemDetail_W_Const.autWellChemDetailID,
qselChemDetail_W_Const.autWellChemID, qselChemDetail_W_Const.autConstID,
qselChemDetail_W_Const.txtConstituent,
IIf([ysnNotDetected]=-1,"ND",[sglValue]) AS [Value],
qselChemDetail_W_Const.sglValue, qselChemDetail_W_Const.autUnitID,
qselChemDetail_W_Const.ysnNotDetected
FROM qselChemDetail_W_Const;


All basic stuff, except for the one Iif statement that creates a column
called "Value"

When I try to build the crosstab query, it will not let me use "Value" in
the SQL

TRANSFORM First(qselDEQExport2_Test.Value) AS FirstOfConst
SELECT qselChemDetail_W_Const.autWellChemID
FROM qselChemDetail_W_Const
GROUP BY qselChemDetail_W_Const.autWellChemID
PIVOT qselChemDetail_W_Const.autConstID;

returns the following error
"The Microsoft Jet database engine does not recognize
'qselDEQExport2_Test.Value' as a valid field name or expression.

How can I get around this?

I have set the parameter "Value" to data type "text" in the query parameter
box in the original query "qselDEQExport2_Test"

Thanks much for the help!

Beth

 

[KARL DEWEY]

Try using another name for your [Value] - Price, Cost, Cost_Per_Item, etc.

 

[BMusic]

OK, I tried changing [Value] to [ConstValue] and [Const] but still doesn't
work.

Also, just discovered that when I assign the parameter in the Query
Parameter dialog box, it asks for Const (or Value previously).

Try using another name for your [Value] - Price, Cost, Cost_Per_Item, etc.
--
KARL DEWEY
Build a little - Test a little

I have a base query (qselDEQExport2_Test) with the following SQL statement

SELECT qselChemDetail_W_Const.autWellChemDetailID,
qselChemDetail_W_Const.autWellChemID, qselChemDetail_W_Const.autConstID,
qselChemDetail_W_Const.txtConstituent,
IIf([ysnNotDetected]=-1,"ND",[sglValue]) AS [Value],
qselChemDetail_W_Const.sglValue, qselChemDetail_W_Const.autUnitID,
qselChemDetail_W_Const.ysnNotDetected
FROM qselChemDetail_W_Const;


All basic stuff, except for the one Iif statement that creates a column
called "Value"

When I try to build the crosstab query, it will not let me use "Value" in
the SQL

TRANSFORM First(qselDEQExport2_Test.Value) AS FirstOfConst
SELECT qselChemDetail_W_Const.autWellChemID
FROM qselChemDetail_W_Const
GROUP BY qselChemDetail_W_Const.autWellChemID
PIVOT qselChemDetail_W_Const.autConstID;

returns the following error
"The Microsoft Jet database engine does not recognize
'qselDEQExport2_Test.Value' as a valid field name or expression.

How can I get around this?

I have set the parameter "Value" to data type "text" in the query parameter
box in the original query "qselDEQExport2_Test"

Thanks much for the help!

Beth

 

[John Spencer]

Your base query is apparently based on another query -
qselChemDetail_W_Const.

Please post that query and the sql for qselDEQExport2_Test if you have
changed it.

With a crosstab query you MUST declare your parameters and if any other
queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

You might try copying the SQL into a new query and see if it works.

Have you turned off AutoCorrect in your database? It can lead to these type
of problems where Access gets the name of something confused.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OK, I tried changing [Value] to [ConstValue] and [Const] but still doesn't
work.

Also, just discovered that when I assign the parameter in the Query
Parameter dialog box, it asks for Const (or Value previously).

Try using another name for your [Value] - Price, Cost, Cost_Per_Item,
etc.
--
KARL DEWEY
Build a little - Test a little

I have a base query (qselDEQExport2_Test) with the following SQL
statement

SELECT qselChemDetail_W_Const.autWellChemDetailID,
qselChemDetail_W_Const.autWellChemID,
qselChemDetail_W_Const.autConstID,
qselChemDetail_W_Const.txtConstituent,
IIf([ysnNotDetected]=-1,"ND",[sglValue]) AS [Value],
qselChemDetail_W_Const.sglValue, qselChemDetail_W_Const.autUnitID,
qselChemDetail_W_Const.ysnNotDetected
FROM qselChemDetail_W_Const;


All basic stuff, except for the one Iif statement that creates a column
called "Value"

When I try to build the crosstab query, it will not let me use "Value"
in
the SQL

TRANSFORM First(qselDEQExport2_Test.Value) AS FirstOfConst
SELECT qselChemDetail_W_Const.autWellChemID
FROM qselChemDetail_W_Const
GROUP BY qselChemDetail_W_Const.autWellChemID
PIVOT qselChemDetail_W_Const.autConstID;

returns the following error
"The Microsoft Jet database engine does not recognize
'qselDEQExport2_Test.Value' as a valid field name or expression.

How can I get around this?

I have set the parameter "Value" to data type "text" in the query
parameter
box in the original query "qselDEQExport2_Test"

Thanks much for the help!

Beth

 

[BMusic]

WOW! I'm a complete idiot! Thanks so much for your comments. You made me see
what was wrong. My crosstab query was referencing two different queries:
qselChemDetail_W_Const and qselDEQExport2_Test. It was only supposed to be
pulling data from qselDEQExport2_Test. Once I resent the SQL in the crosstab
query everything works.

TRANSFORM First(qselDEQExport2_Test.Const) AS FirstOfConst
SELECT qselDEQExport2_Test.autWellChemID
FROM qselDEQExport2_Test
GROUP BY qselDEQExport2_Test.autWellChemID
PIVOT qselDEQExport2_Test.autConstID;

As for setting the parameters, I still can't make that work without causing
it to ask for input from the user, but as long as I pull the data from the
right query, it doesn't seem to object.

Thanks so much for the help! Hopefully this will solve the problem and not
make new ones!

Your base query is apparently based on another query -
qselChemDetail_W_Const.

Please post that query and the sql for qselDEQExport2_Test if you have
changed it.

With a crosstab query you MUST declare your parameters and if any other
queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

You might try copying the SQL into a new query and see if it works.

Have you turned off AutoCorrect in your database? It can lead to these type
of problems where Access gets the name of something confused.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OK, I tried changing [Value] to [ConstValue] and [Const] but still doesn't
work.

Also, just discovered that when I assign the parameter in the Query
Parameter dialog box, it asks for Const (or Value previously).

Try using another name for your [Value] - Price, Cost, Cost_Per_Item,
etc.
--
KARL DEWEY
Build a little - Test a little


:

I have a base query (qselDEQExport2_Test) with the following SQL
statement

SELECT qselChemDetail_W_Const.autWellChemDetailID,
qselChemDetail_W_Const.autWellChemID,
qselChemDetail_W_Const.autConstID,
qselChemDetail_W_Const.txtConstituent,
IIf([ysnNotDetected]=-1,"ND",[sglValue]) AS [Value],
qselChemDetail_W_Const.sglValue, qselChemDetail_W_Const.autUnitID,
qselChemDetail_W_Const.ysnNotDetected
FROM qselChemDetail_W_Const;


All basic stuff, except for the one Iif statement that creates a column
called "Value"

When I try to build the crosstab query, it will not let me use "Value"
in
the SQL

TRANSFORM First(qselDEQExport2_Test.Value) AS FirstOfConst
SELECT qselChemDetail_W_Const.autWellChemID
FROM qselChemDetail_W_Const
GROUP BY qselChemDetail_W_Const.autWellChemID
PIVOT qselChemDetail_W_Const.autConstID;

returns the following error
"The Microsoft Jet database engine does not recognize
'qselDEQExport2_Test.Value' as a valid field name or expression.

How can I get around this?

I have set the parameter "Value" to data type "text" in the query
parameter
box in the original query "qselDEQExport2_Test"

Thanks much for the help!

Beth