Report and crosstab

[Guest]

Hi

I have a report that is build on a crosstab query that looks like this:

Employee number Name Course name Expression
Row heading Row heading Culomn heading Value

I want the user of the report to be prompted for an employee number from a
combo box (named Employee number). I have then made a form called paramForm
where the combobox looks up the values in the query. I have modified the
report event procedure so it calls the form on opening. In the query I have
tried to use the criteria Forms![Paramform]![Employee number] to use the
combo box command but the problem is that it comes with and error saying it
does'nt recognize the expression? Can anyone help solve this problem?

 

[John Spencer]

First thing, 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
[Forms]![Paramform]![Employee number]
Select the data type of the parameter in column 2

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

 

[Guest]

Hi John

First thank you for helping me. I tried to do what you said and in the
crosstab query it works okay. But when I then try to run the report it keeps
on genereting a parameter prompt again and again and again.

I have attached my sql for the crosstab query as I think the problem is here.

TRANSFORM Sum(IIf([Employee qualification 2].[Masterdata
courses].Name=[Masterdata courses].Name,1,0)) AS Expr1
SELECT [Employee qualification 2].[Masterdata employees].Name, [Employee
qualification 2].[Trade divisions], [Employee qualification 2].[Employee
number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses] ON [Employee
qualification 2].[Course number] = [Masterdata courses].[Course number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name, [Employee
qualification 2].[Trade divisions], [Employee qualification 2].[Employee
number]
PIVOT [Masterdata courses].Name;

hope you can help.

First thing, 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
[Forms]![Paramform]![Employee number]
Select the data type of the parameter in column 2

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

Hi

I have a report that is build on a crosstab query that looks like this:

Employee number Name Course name Expression
Row heading Row heading Culomn heading Value

I want the user of the report to be prompted for an employee number from a
combo box (named Employee number). I have then made a form called
paramForm
where the combobox looks up the values in the query. I have modified the
report event procedure so it calls the form on opening. In the query I
have
tried to use the criteria Forms![Paramform]![Employee number] to use the
combo box command but the problem is that it comes with and error saying
it
does'nt recognize the expression? Can anyone help solve this problem?

 

[John Spencer]

First, can you run the query by itself without getting the parameter prompt?
I would think not.

The use of
[Employee qualification 2].[Masterdata employees].Name
as a field name looks highly suspect to me.

I think the SQL should look like:

TRANSFORM
Sum(IIf([Employee qualification 2].Name=[Masterdata courses].Name,1,0)) AS
Expr1
SELECT [Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses]
ON [Employee qualification 2].[Course number] = [Masterdata courses].[Course
number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
PIVOT [Masterdata courses].Name;


Although I think you could more easily write that as
TRANSFORM Nz(Count([Employee qualification 2].Name),0) AS Expr1
SELECT [Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses]
ON [Employee qualification 2].[Course number] = [Masterdata courses].[Course
number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
PIVOT [Masterdata courses].Name;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John

First thank you for helping me. I tried to do what you said and in the
crosstab query it works okay. But when I then try to run the report it
keeps
on genereting a parameter prompt again and again and again.

I have attached my sql for the crosstab query as I think the problem is
here.

TRANSFORM Sum(IIf([Employee qualification 2].[Masterdata
courses].Name=[Masterdata courses].Name,1,0)) AS Expr1
SELECT [Employee qualification 2].[Masterdata employees].Name, [Employee
qualification 2].[Trade divisions], [Employee qualification 2].[Employee
number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses] ON
[Employee
qualification 2].[Course number] = [Masterdata courses].[Course number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name, [Employee
qualification 2].[Trade divisions], [Employee qualification 2].[Employee
number]
PIVOT [Masterdata courses].Name;

hope you can help.

First thing, 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
[Forms]![Paramform]![Employee number]
Select the data type of the parameter in column 2

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

Hi

I have a report that is build on a crosstab query that looks like this:

Employee number Name Course name Expression
Row heading Row heading Culomn heading Value

I want the user of the report to be prompted for an employee number
from a
combo box (named Employee number). I have then made a form called
paramForm
where the combobox looks up the values in the query. I have modified
the
report event procedure so it calls the form on opening. In the query I
have
tried to use the criteria Forms![Paramform]![Employee number] to use
the
combo box command but the problem is that it comes with and error
saying
it
does'nt recognize the expression? Can anyone help solve this problem?

 

[Guest]

Hi John

I have modified my Sql statement as you suggested (se below) and this works
fine in my crosstab query. When I try to run my report based on this cross
tab query it doesn't work. I coded the open event procedure of my report to
open my ParamForm where the user should select the EmployeeNo from a
combobox. This does'nt work. The report will not be genereted. It says that
the fields from my crosstab query is not valid. Any suggestions?

PARAMETERS [Forms]![ParamForm]![cmbEmployeeNo] Long;
TRANSFORM Sum(IIf([Employee qualification 2].Course_Name=[Masterdata
courses].Course_Name,1,0)) AS [Value]
SELECT [Employee qualification 2].Employee_number, [Employee qualification
2].Employee_Name, [Employee qualification 2].Trade_divisions
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses] ON [Employee
qualification 2].Course_number = [Masterdata courses].Course_number
WHERE ((([Employee qualification
2].Employee_number)=[Forms]![ParamForm]![cmbEmployeeNo]))
GROUP BY [Employee qualification 2].Employee_number, [Employee qualification
2].Employee_Name, [Employee qualification 2].Trade_divisions
PIVOT [Masterdata courses].Course_Name;

Ticotion

First, can you run the query by itself without getting the parameter prompt?
I would think not.

The use of
[Employee qualification 2].[Masterdata employees].Name
as a field name looks highly suspect to me.

I think the SQL should look like:

TRANSFORM
Sum(IIf([Employee qualification 2].Name=[Masterdata courses].Name,1,0)) AS
Expr1
SELECT [Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses]
ON [Employee qualification 2].[Course number] = [Masterdata courses].[Course
number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
PIVOT [Masterdata courses].Name;


Although I think you could more easily write that as
TRANSFORM Nz(Count([Employee qualification 2].Name),0) AS Expr1
SELECT [Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses]
ON [Employee qualification 2].[Course number] = [Masterdata courses].[Course
number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
PIVOT [Masterdata courses].Name;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John

First thank you for helping me. I tried to do what you said and in the
crosstab query it works okay. But when I then try to run the report it
keeps
on genereting a parameter prompt again and again and again.

I have attached my sql for the crosstab query as I think the problem is
here.

TRANSFORM Sum(IIf([Employee qualification 2].[Masterdata
courses].Name=[Masterdata courses].Name,1,0)) AS Expr1
SELECT [Employee qualification 2].[Masterdata employees].Name, [Employee
qualification 2].[Trade divisions], [Employee qualification 2].[Employee
number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses] ON
[Employee
qualification 2].[Course number] = [Masterdata courses].[Course number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name, [Employee
qualification 2].[Trade divisions], [Employee qualification 2].[Employee
number]
PIVOT [Masterdata courses].Name;

hope you can help.

First thing, 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
[Forms]![Paramform]![Employee number]
Select the data type of the parameter in column 2

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

Hi

I have a report that is build on a crosstab query that looks like this:

Employee number Name Course name Expression
Row heading Row heading Culomn heading Value

I want the user of the report to be prompted for an employee number
from a
combo box (named Employee number). I have then made a form called
paramForm
where the combobox looks up the values in the query. I have modified
the
report event procedure so it calls the form on opening. In the query I
have
tried to use the criteria Forms![Paramform]![Employee number] to use
the
combo box command but the problem is that it comes with and error
saying
it
does'nt recognize the expression? Can anyone help solve this problem?

 

[John Spencer]

Other possibilities are the report references "fields" that no longer exist
since you have rebuilt the query.

For instance, the Transform statement you originally had used "As Expr1" and
now that is "As [Value]"
and your first "field" in the SELECT clause was different then it is now.

Open the report in design view and check the control source of each control
to see if the control source is one of the fields in the query.

Also, since you didn't specify the column names in the Pivot clause, the
report may have problems if the course name does not appear in the results.

PIVOT [Masterdata courses].Course_Name IN ("CourseA",
"YourNextCourseName",...)

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

Hi John

I have modified my Sql statement as you suggested (se below) and this
works
fine in my crosstab query. When I try to run my report based on this cross
tab query it doesn't work. I coded the open event procedure of my report
to
open my ParamForm where the user should select the EmployeeNo from a
combobox. This does'nt work. The report will not be genereted. It says
that
the fields from my crosstab query is not valid. Any suggestions?

PARAMETERS [Forms]![ParamForm]![cmbEmployeeNo] Long;
TRANSFORM Sum(IIf([Employee qualification 2].Course_Name=[Masterdata
courses].Course_Name,1,0)) AS [Value]
SELECT [Employee qualification 2].Employee_number, [Employee qualification
2].Employee_Name, [Employee qualification 2].Trade_divisions
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses] ON
[Employee
qualification 2].Course_number = [Masterdata courses].Course_number
WHERE ((([Employee qualification
2].Employee_number)=[Forms]![ParamForm]![cmbEmployeeNo]))
GROUP BY [Employee qualification 2].Employee_number, [Employee
qualification
2].Employee_Name, [Employee qualification 2].Trade_divisions
PIVOT [Masterdata courses].Course_Name;

Ticotion

First, can you run the query by itself without getting the parameter
prompt?
I would think not.

The use of
[Employee qualification 2].[Masterdata employees].Name
as a field name looks highly suspect to me.

I think the SQL should look like:

TRANSFORM
Sum(IIf([Employee qualification 2].Name=[Masterdata courses].Name,1,0))
AS
Expr1
SELECT [Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses]
ON [Employee qualification 2].[Course number] = [Masterdata
courses].[Course
number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
PIVOT [Masterdata courses].Name;


Although I think you could more easily write that as
TRANSFORM Nz(Count([Employee qualification 2].Name),0) AS Expr1
SELECT [Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses]
ON [Employee qualification 2].[Course number] = [Masterdata
courses].[Course
number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name
, [Employee qualification 2].[Trade divisions]
, [Employee qualification 2].[Employee number]
PIVOT [Masterdata courses].Name;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John

First thank you for helping me. I tried to do what you said and in the
crosstab query it works okay. But when I then try to run the report it
keeps
on genereting a parameter prompt again and again and again.

I have attached my sql for the crosstab query as I think the problem is
here.

TRANSFORM Sum(IIf([Employee qualification 2].[Masterdata
courses].Name=[Masterdata courses].Name,1,0)) AS Expr1
SELECT [Employee qualification 2].[Masterdata employees].Name,
[Employee
qualification 2].[Trade divisions], [Employee qualification
2].[Employee
number]
FROM [Employee qualification 2] RIGHT JOIN [Masterdata courses] ON
[Employee
qualification 2].[Course number] = [Masterdata courses].[Course number]
GROUP BY [Employee qualification 2].[Masterdata employees].Name,
[Employee
qualification 2].[Trade divisions], [Employee qualification
2].[Employee
number]
PIVOT [Masterdata courses].Name;

hope you can help.


:

First thing, 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
[Forms]![Paramform]![Employee number]
Select the data type of the parameter in column 2

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

Hi

I have a report that is build on a crosstab query that looks like
this:

Employee number Name Course name Expression
Row heading Row heading Culomn heading Value

I want the user of the report to be prompted for an employee number
from a
combo box (named Employee number). I have then made a form called
paramForm
where the combobox looks up the values in the query. I have modified
the
report event procedure so it calls the form on opening. In the query
I
have
tried to use the criteria Forms![Paramform]![Employee number] to use
the
combo box command but the problem is that it comes with and error
saying
it
does'nt recognize the expression? Can anyone help solve this
problem?