Linking Criteria from Query to Crosstab Query

[JB]

So far I have created a query where the user can enter department of
their choice to view the detail. I then created a crosstab query to
categorise the results. I created a report with a subreport (crosstab)
to show the results.

The question I have is how can I set up the report where the user only
haves to enter the department number only once?

Here is the SQL from my crosstab query, if it helps.

TRANSFORM CLng(Nz(Sum(qry_HADC_Ship_All_Dept.[Open Qty]),0)) AS
[SumOfOpen Qty]
SELECT qry_HADC_Ship_All_Dept.Dept, Sum(qry_HADC_Ship_All_Dept.[Open
Qty]) AS [Total Of Open Qty], Count(qry_HADC_Ship_All_Dept.Distro) AS
CountOfDistro
FROM qry_HADC_Ship_All_Dept
WHERE
(((qry_HADC_Ship_All_Dept.Dept)=[Forms]![qry_HADC_Ship_All_Dept]![Dept]))
GROUP BY qry_HADC_Ship_All_Dept.Dept
PIVOT qry_HADC_Ship_All_Dept.[Age Bucket] In ("0 to 3","4 to 6","Over
7");

Thanks

Jeremiah

 

[Marshall Barton]

So far I have created a query where the user can enter department of
their choice to view the detail. I then created a crosstab query to
categorise the results. I created a report with a subreport (crosstab)
to show the results.

The question I have is how can I set up the report where the user only
haves to enter the department number only once?

Here is the SQL from my crosstab query, if it helps.

TRANSFORM CLng(Nz(Sum(qry_HADC_Ship_All_Dept.[Open Qty]),0)) AS
[SumOfOpen Qty]
SELECT qry_HADC_Ship_All_Dept.Dept, Sum(qry_HADC_Ship_All_Dept.[Open
Qty]) AS [Total Of Open Qty], Count(qry_HADC_Ship_All_Dept.Distro) AS
CountOfDistro
FROM qry_HADC_Ship_All_Dept
WHERE
(((qry_HADC_Ship_All_Dept.Dept)=[Forms]![qry_HADC_Ship_All_Dept]![Dept]))
GROUP BY qry_HADC_Ship_All_Dept.Dept
PIVOT qry_HADC_Ship_All_Dept.[Age Bucket] In ("0 to 3","4 to 6","Over
7");

You need to explicitly specify a crosstab query's parmeters
and its data type. You can do that using the Query -
Parameters menu item in query design view or by inserting a
Parameters statement at the top of the query's SQL view:

PARAMETERS [Forms]![qry_HADC_Ship_All_Dept]![Dept] TEXT (
255 );