How do I set up a parameter query in a cross tabs query?

[Kirk]

I am trying to set up a crosstabs query with a parameter query to return
information based on recored between certain dates. I have tried the
following syntax in the Criteria section of the date field:

Between [enter start date] And [enter end date]

but I can't make it work.

Help!!

 

[Jeff Boyce]

Kirk

.... can't make it work... doesn't give us much to go on.

What happens when you try this? What happens if you leave out the prompts
and use actual date values for testing?

More info, please...

Jeff Boyce
<Office/Access MVP>

 

[John Spencer]

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

Or open the query in SQL view and type at the beginning
Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
....

 

[Kirk]

Thanks John. Very helpful. Problem solved.

Kirk

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

Or open the query in SQL view and type at the beginning
Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
....

I am trying to set up a crosstabs query with a parameter query to return
information based on recored between certain dates. I have tried the
following syntax in the Criteria section of the date field:

Between [enter start date] And [enter end date]

but I can't make it work.

Help!!

 

[Dmackcwby]

I have declared the parameters as suggested in this thread. However, the
query is not limiting itself to parameters that are imputed. Here is what
the SQL looks like:

PARAMETERS [Beginning Date:] DateTime, [Ending Date:] DateTime;
TRANSFORM Count(tblFsLog.LogID) AS CountOfLogID
SELECT tblEmployees.ShortName, tblFsLog.Date, Count(tblFsLog.LogID) AS
CountOfLogID1
FROM tblFsReason INNER JOIN (tblEmployees INNER JOIN tblFsLog ON
tblEmployees.EmployeeID = tblFsLog.CsrID) ON tblFsReason.FsReasonID =
tblFsLog.ReasonID
GROUP BY tblEmployees.ShortName, tblFsLog.Date
ORDER BY tblEmployees.ShortName, tblFsReason.Reason
PIVOT tblFsReason.Reason;

As you can see I have the parameters at the beginning. However, the query
is pulling all data. What have I done wrong?

David

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

Or open the query in SQL view and type at the beginning
Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
....

I am trying to set up a crosstabs query with a parameter query to return
information based on recored between certain dates. I have tried the
following syntax in the Criteria section of the date field:

Between [enter start date] And [enter end date]

but I can't make it work.

Help!!

 

[fredg]

I have declared the parameters as suggested in this thread. However, the
query is not limiting itself to parameters that are imputed. Here is what
the SQL looks like:

PARAMETERS [Beginning Date:] DateTime, [Ending Date:] DateTime;
TRANSFORM Count(tblFsLog.LogID) AS CountOfLogID
SELECT tblEmployees.ShortName, tblFsLog.Date, Count(tblFsLog.LogID) AS
CountOfLogID1
FROM tblFsReason INNER JOIN (tblEmployees INNER JOIN tblFsLog ON
tblEmployees.EmployeeID = tblFsLog.CsrID) ON tblFsReason.FsReasonID =
tblFsLog.ReasonID
GROUP BY tblEmployees.ShortName, tblFsLog.Date
ORDER BY tblEmployees.ShortName, tblFsReason.Reason
PIVOT tblFsReason.Reason;

As you can see I have the parameters at the beginning. However, the query
is pulling all data. What have I done wrong?

David

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

Or open the query in SQL view and type at the beginning
Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
....

I am trying to set up a crosstabs query with a parameter query to return
information based on recored between certain dates. I have tried the
following syntax in the Criteria section of the date field:

Between [enter start date] And [enter end date]

but I can't make it work.

Help!!

You have declared the parameters as DateTime, but nowhere in your
query SQL do you have the where clause to actually filter the records.

Parameters ...
Transform ....
Select ...
From ....
Where YourTable.DateField between [Beginning Date:] and [Ending Date:]
Group By ....
Order By ...
Pivot ...

Change YourTable.DateField to the aqctual table and field name.

 

[John Spencer]

You haven't USED the parameters in a where clause. All you've done is
declare the parameters as being a specific type.

Assuming that you are trying to limit the report to records that have
tblFsLog.Date between the two parameters, you would need something like
the following.

PARAMETERS [Beginning Date:] DateTime, [Ending Date:] DateTime;
TRANSFORM Count(tblFsLog.LogID) AS CountOfLogID
SELECT tblEmployees.ShortName
, tblFsLog.Date
, Count(tblFsLog.LogID) AS CountOfLogID1
FROM tblFsReason INNER JOIN
(tblEmployees INNER JOIN tblFsLog
ON tblEmployees.EmployeeID = tblFsLog.CsrID)
ON tblFsReason.FsReasonID = tblFsLog.ReasonID


WHERE tblFsLog.Date Between [Beginning Date:] and [Ending date:]


GROUP BY tblEmployees.ShortName, tblFsLog.Date
ORDER BY tblEmployees.ShortName, tblFsReason.Reason
PIVOT tblFsReason.Reason;
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have declared the parameters as suggested in this thread. However, the
query is not limiting itself to parameters that are imputed. Here is what
the SQL looks like:

PARAMETERS [Beginning Date:] DateTime, [Ending Date:] DateTime;
TRANSFORM Count(tblFsLog.LogID) AS CountOfLogID
SELECT tblEmployees.ShortName, tblFsLog.Date, Count(tblFsLog.LogID) AS
CountOfLogID1
FROM tblFsReason INNER JOIN (tblEmployees INNER JOIN tblFsLog ON
tblEmployees.EmployeeID = tblFsLog.CsrID) ON tblFsReason.FsReasonID =
tblFsLog.ReasonID
GROUP BY tblEmployees.ShortName, tblFsLog.Date
ORDER BY tblEmployees.ShortName, tblFsReason.Reason
PIVOT tblFsReason.Reason;

As you can see I have the parameters at the beginning. However, the query
is pulling all data. What have I done wrong?

David

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

Or open the query in SQL view and type at the beginning
Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
....

I am trying to set up a crosstabs query with a parameter query to return
information based on recored between certain dates. I have tried the
following syntax in the Criteria section of the date field:

Between [enter start date] And [enter end date]

but I can't make it work.

Help!!

 

[Dmackcwby]

Thanks so much. I knew it had to be something simple that I just over
looked. It is working perfectly now. Thanks again

You haven't USED the parameters in a where clause. All you've done is
declare the parameters as being a specific type.

Assuming that you are trying to limit the report to records that have
tblFsLog.Date between the two parameters, you would need something like
the following.

PARAMETERS [Beginning Date:] DateTime, [Ending Date:] DateTime;
TRANSFORM Count(tblFsLog.LogID) AS CountOfLogID
SELECT tblEmployees.ShortName
, tblFsLog.Date
, Count(tblFsLog.LogID) AS CountOfLogID1
FROM tblFsReason INNER JOIN
(tblEmployees INNER JOIN tblFsLog
ON tblEmployees.EmployeeID = tblFsLog.CsrID)
ON tblFsReason.FsReasonID = tblFsLog.ReasonID


WHERE tblFsLog.Date Between [Beginning Date:] and [Ending date:]


GROUP BY tblEmployees.ShortName, tblFsLog.Date
ORDER BY tblEmployees.ShortName, tblFsReason.Reason
PIVOT tblFsReason.Reason;
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have declared the parameters as suggested in this thread. However, the
query is not limiting itself to parameters that are imputed. Here is what
the SQL looks like:

PARAMETERS [Beginning Date:] DateTime, [Ending Date:] DateTime;
TRANSFORM Count(tblFsLog.LogID) AS CountOfLogID
SELECT tblEmployees.ShortName, tblFsLog.Date, Count(tblFsLog.LogID) AS
CountOfLogID1
FROM tblFsReason INNER JOIN (tblEmployees INNER JOIN tblFsLog ON
tblEmployees.EmployeeID = tblFsLog.CsrID) ON tblFsReason.FsReasonID =
tblFsLog.ReasonID
GROUP BY tblEmployees.ShortName, tblFsLog.Date
ORDER BY tblEmployees.ShortName, tblFsReason.Reason
PIVOT tblFsReason.Reason;

As you can see I have the parameters at the beginning. However, the query
is pulling all data. What have I done wrong?

David

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

Or open the query in SQL view and type at the beginning
Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
....

I am trying to set up a crosstabs query with a parameter query to return
information based on recored between certain dates. I have tried the
following syntax in the Criteria section of the date field:

Between [enter start date] And [enter end date]

but I can't make it work.

Help!!

 

[Mobiius]

Hello, I'm also having troubles with a crosstab parameter query. Following
the very helpful advice above, I can get my query to appear in datasheet
view, however when I run a report based on it, (A report which works without
the between parameters) I get prompted for the start and end date twice, then
I get the following error message:

"The Microsoft Jet database engine does not recognize '' as a valid field
name or expression."

The SQL is as follows:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Count([SPoC Metrics Query].Ref) AS CountOfRef
SELECT [SPoC Metrics Query].[Team Name], Count([SPoC Metrics Query].Ref) AS
Total, Count([SPoC Metrics Query].[2 2 Pass]) AS FTF
FROM [SPoC Metrics Query]
WHERE ((([SPoC Metrics Query].[Open Date]) Between [Enter Start Date] And
[Enter End Date]))
GROUP BY [SPoC Metrics Query].[Team Name]
PIVOT [SPoC Metrics Query].ShortCategory;

Any help would be gratefully appreciated.

 

[Mobiius]

I believe that it may have something to do with the report featuring all 17
row headers, and the filtered query only giving 14 rows worth of data.

I can't figure out how to ensure that all 17 columns display in the query so
it'll appear in the report.

 

[John Spencer MVP]

If you know the 17 COLUMN headers generated by ShortCategory you can specify
them in the query. If you do the ones you designate will always be present
and only the ones you designate will be present.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Count([SPoC Metrics Query].Ref) AS CountOfRef
SELECT [SPoC Metrics Query].[Team Name], Count([SPoC Metrics Query].Ref) AS
Total, Count([SPoC Metrics Query].[2 2 Pass]) AS FTF
FROM [SPoC Metrics Query]
WHERE ((([SPoC Metrics Query].[Open Date]) Between [Enter Start Date] And
[Enter End Date]))
GROUP BY [SPoC Metrics Query].[Team Name]
PIVOT [SPoC Metrics Query].ShortCategory IN ("Heading1","WhateverHeading2is",
.... , "Heading16", "AndTheLastHeading")

Hopefully it is clear to you that you would not designate "Team Name",
"Total", and "FTF" in the Pivot clause since they are not (I assume) one of
the possible ShortCategory values.

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

 

[Mobiius]

Thank you for this, I thought about this but I didn't want to have to
explicitly add these headers as the list could increase in the future.

I'll get right on it now.

Thank you.

 

[Mobiius]

It worked beautifully! Thanks.

 

[Owen]

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

Or open the query in SQL view and type at the beginning
Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
....

I am trying to set up a crosstabs query with a parameter query to return
information based on recored between certain dates. I have tried the
following syntax in the Criteria section of the date field:

Between [enter start date] And [enter end date]

but I can't make it work.

Help!!

John,
This was helpful, but how can i bring in information from another table. I
can't create a join because there is no common field. I am trying to count
the number of trips in each database, but there is no common field to join.