Cross Tab Queries Problems

B

Bill - ESAI

Hi All

I'm struggling with an issue here that I would love input on.

Here's what I want.

I have a table called Projects with the following attributes:
ProjectID
ActualCompletionDate
Department
plus more

I also have a table called tbl_Savings with these attributes:
save_Amount
save_ProjectID
Benefit_Type
plus more

Here's a query that I called qryGetSavingsByDepartment:
SELECT tbl_Savings.save_Amount, tbl_Savings.Benefit_Type,
Projects.Department, Projects.ProjectID, Projects.ActualCompDate
FROM Projects INNER JOIN tbl_Savings ON Projects.ProjectID =
tbl_Savings.save_ProjectID
WHERE (((tbl_Savings.save_Amount) Is Not Null));

I have a form field where this is the actual path:
[forms]![frmMainMenu]![txtDepartmentParameter]

I've tried adding Projects.Department =
[forms]![frmMainMenu]![txtDepartmentParameter] to the WHERE condition which
works fine if I run this query on it's own.

Now I have a CrossTab query like so:
TRANSFORM Sum(qryGetSavingsbyDepartment.save_Amount) AS SumOfsave_Amount
SELECT qryGetSavingsbyDepartment.Benefit_Type,
Sum(qryGetSavingsbyDepartment.save_Amount) AS [Total Of save_Amount]
FROM qryGetSavingsbyDepartment
GROUP BY qryGetSavingsbyDepartment.Benefit_Type
PIVOT qryGetSavingsbyDepartment.Department;


I need the result to be filtered on the Projects.Department but no matter
what I've tried so far, when trying to use the form field
"[forms]![frmMainMenu]![txtDepartmentParameter]" Access tellings me that it
can't find the form element. If I copy and past that path in a simple query
to select all projects the path works fine. I seems to have something to do
the CrossTab query.

Any help would be greatly appreciate.

TY

Bill
 
J

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
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
B

Bill - ESAI

Brilliant.

Thank you John

Bill



John Spencer said:
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
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi All

I'm struggling with an issue here that I would love input on.

Here's what I want.

I have a table called Projects with the following attributes:
ProjectID
ActualCompletionDate
Department
plus more

I also have a table called tbl_Savings with these attributes:
save_Amount
save_ProjectID
Benefit_Type
plus more

Here's a query that I called qryGetSavingsByDepartment:
SELECT tbl_Savings.save_Amount, tbl_Savings.Benefit_Type,
Projects.Department, Projects.ProjectID, Projects.ActualCompDate
FROM Projects INNER JOIN tbl_Savings ON Projects.ProjectID =
tbl_Savings.save_ProjectID
WHERE (((tbl_Savings.save_Amount) Is Not Null));

I have a form field where this is the actual path:
[forms]![frmMainMenu]![txtDepartmentParameter]

I've tried adding Projects.Department =
[forms]![frmMainMenu]![txtDepartmentParameter] to the WHERE condition
which works fine if I run this query on it's own.

Now I have a CrossTab query like so:
TRANSFORM Sum(qryGetSavingsbyDepartment.save_Amount) AS SumOfsave_Amount
SELECT qryGetSavingsbyDepartment.Benefit_Type,
Sum(qryGetSavingsbyDepartment.save_Amount) AS [Total Of save_Amount]
FROM qryGetSavingsbyDepartment
GROUP BY qryGetSavingsbyDepartment.Benefit_Type
PIVOT qryGetSavingsbyDepartment.Department;


I need the result to be filtered on the Projects.Department but no matter
what I've tried so far, when trying to use the form field
"[forms]![frmMainMenu]![txtDepartmentParameter]" Access tellings me that
it can't find the form element. If I copy and past that path in a simple
query to select all projects the path works fine. I seems to have
something to do the CrossTab query.

Any help would be greatly appreciate.

TY

Bill
Click to expand...
Click to expand...
 
Top