|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
On Mon, 7 Apr 2008 16:13:00 -0700, Dmackcwby wrote:
> 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
>
> "John Spencer" wrote:
>
>> 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" <Kirk@discussions.microsoft.com> wrote in message
>> news:F1478F11-53AF-4E9B-A7D6-67EDA1510D1B@microsoft.com...
>>>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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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
'====================================================
Dmackcwby wrote:
> 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
>
> "John Spencer" wrote:
>
>> 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" <Kirk@discussions.microsoft.com> wrote in message
>> news:F1478F11-53AF-4E9B-A7D6-67EDA1510D1B@microsoft.com...
>>> 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!!
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Thanks so much. I knew it had to be something simple that I just over
looked. It is working perfectly now. Thanks again
"John Spencer" wrote:
> 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
> '====================================================
>
>
> Dmackcwby wrote:
> > 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
> >
> > "John Spencer" wrote:
> >
> >> 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" <Kirk@discussions.microsoft.com> wrote in message
> >> news:F1478F11-53AF-4E9B-A7D6-67EDA1510D1B@microsoft.com...
> >>> 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!!
> >>
> >>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
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 wrote:
> 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.
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Question
Answer
Notify me of replies