Problem with parameter query

[Kurt]

I've based a report on a crosstab query. The report shows
frequency statistics for survey data. One question on the
survey, which is entered for each respondent, is Survey
Date. I'd like to user to be able to limit the
report/query to data between a start and end survey date.
(e.g., show all surveys entered between 2/3/2003 and
4/3/2003).

The problem is that there is no "SurveyDate" field (the
data is fully normalized so "QstnText" is the field and
the value depends on the question), so I can't simply add
a parameter in the query for "SurveyDate".

I need something like:

If tblQuestions.QstnText = "Survey Date" Then . . .

Below is the current SQL statement for the query. How can
I edit it so that the user can limit the data based on
the survey date?

Thanks. Kurt

***************************************************
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
FROM tblQuestions
INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
GROUP BY tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");
***************************************************

 

[John Spencer (MVP)]

I assume that you have something unique value that indentifies which responder
has answered which survey.

I would create a query that extracts that responderId and the response to the
querstion that is the survey date. Then you could use that in conjunction with
your current query. Something like:

***************************************************
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
FROM (tblQuestions
INNER JOIN tblResponses
ON tblQuestions.QstnID = tblResponses.QstnID)
INNER JOIN qryResponseDate
ON tblResponses.ResponderID = QryResponse.ResponderID
WHERE QryResponse.TheDate Between #2/3/03# And #4/3/2003#
GROUP BY tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");
***************************************************

QryResponseDate would look something like:

SELECT ResponderId, tblResponses.Rspns
FROM tblResponses
WHERE TblResponses.QstnId = 22 <<-- or whichever is the correct question.

 

[Mike M.T.]

1. Build another query:

********************************************
SELECT CrosstabQuery.QstnID, CrosstabQuery.QstnText, CrosstabQuery.Rspns,
CrosstabQuery.[Number of Responses], IIf([CrosstabQuery]![QstnText]="Survey
Date","Limited","NotLimited") AS Mode
FROM CrosstabQuery;
**********************************************

Note: If you copy/paste, replace CrosstabQuery with the name of your query

2. Base your report on this one.

 

[Duane Hookom]

Looks like my "At Your Survey" application. I would add another column in
the crosstab
Field: SurveyDate: CDate(IIf([tblResponses]![QstnID]=14,[Rspns],#1/1/1900#))
Total: Max
Crosstab: Row Heading
Substitute your QstnID in place of 14.

 

[Kurt]

Looks like my "At Your Survey" application

Good call.

I edited the crosstab query (qxtbQstnTextRspnsxCount) like you suggested so it now has a SurveyDate field.
After that, I added the parameter criteria to qselQstnTextRspnsxCount, which is what the report is based on (ala the Statistics report in "At Your Survey").

However, due to the way the report (or query) is set up, the "parametized" report only shows one "Response." For example, if the start and end date parameters are 2/1/2003 and 2/5/2003, the Statistics report (like the one in the "At Your Survey") shows this:

Quest# Question Text Response #0f Resp. %of Resp.
----------------------------------------------------
2 Survey Date 2/4/2003 1 50.0%
2/3/2003 1 50.0%

.. . . as opposed to *all* the responses/records which were entered within the date range provided.

1. Do I need to edit the report or query(s) to fix this? Perhaps I need to treat the Survey Date not as a survey question (i.e., Rspns), but as a category on the same level as RspnsID.

2. I would prefer to use a form instead of a parameter query to pass the start and end date values, but I understand that this can't be done with aggregate or crosstab queries (see http://users.bigpond.net.au/abrowne1/casu-08.html). Is this true?

Thank you for your continued help. Kurt (Full SQLs below)


Full SQL of qselQstnTextRspnsxCount
-----------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID, qxtbQstnTextRspnsxCount.QstnNum, qxtbQstnTextRspnsxCount.QstnLvl1, qxtbQstnTextRspnsxCount.QstnLvl2, qxtbQstnTextRspnsxCount.QstnText, qxtbQstnTextRspnsxCount.Rspns, qxtbQstnTextRspnsxCount.SurveyDate, qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount
WHERE (((qxtbQstnTextRspnsxCount.SurveyDate) Between
[StartDate] And [EndDate]));

Full SQL of qxtbQstnTextRspnsxCount
-----------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
Max(CDate(IIf(tblResponses!QstnID=2,
[Rspns],#1/1/1900#))) AS SurveyDate
FROM tblQuestions INNER JOIN tblResponses ON
tblQuestions.QstnID=tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo" Or
(tblQuestions.QstnType)="ID"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");

-----Original Message-----
Looks like my "At Your Survey" application. I would add another column in
the crosstab
Field: SurveyDate: CDate(IIf([tblResponses]![QstnID]=14,[Rspns],#1/1/1900#))
Total: Max
Crosstab: Row Heading
Substitute your QstnID in place of 14.

--
Duane Hookom
MS Access MVP

I've based a report on a crosstab query. The report shows
frequency statistics for survey data. One question on the
survey, which is entered for each respondent, is Survey
Date. I'd like to user to be able to limit the
report/query to data between a start and end survey date.
(e.g., show all surveys entered between 2/3/2003 and
4/3/2003).

The problem is that there is no "SurveyDate" field (the
data is fully normalized so "QstnText" is the field and
the value depends on the question), so I can't simply add
a parameter in the query for "SurveyDate".

I need something like:

If tblQuestions.QstnText = "Survey Date" Then . . .

Below is the current SQL statement for the query. How can
I edit it so that the user can limit the data based on
the survey date?

Thanks. Kurt

***************************************************
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
FROM tblQuestions
INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
GROUP BY tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");
***************************************************

.

 

[Duane Hookom]

I would create a simple query that results in the RspnsID from tblResponses
where the QstnID is your specific question and the CDate([Rspns]) meets your
criteria. Then join this query into your crosstab and join the RspnsId
fields.

--
Duane Hookom
MS Access MVP

Looks like my "At Your Survey" application

Good call.

I edited the crosstab query (qxtbQstnTextRspnsxCount) like you suggested so
it now has a SurveyDate field.
After that, I added the parameter criteria to qselQstnTextRspnsxCount, which
is what the report is based on (ala the Statistics report in "At Your
Survey").

However, due to the way the report (or query) is set up, the "parametized"
report only shows one "Response." For example, if the start and end date
parameters are 2/1/2003 and 2/5/2003, the Statistics report (like the one in
the "At Your Survey") shows this:

Quest# Question Text Response #0f Resp. %of Resp.
----------------------------------------------------
2 Survey Date 2/4/2003 1 50.0%
2/3/2003 1 50.0%

.. . . as opposed to *all* the responses/records which were entered within
the date range provided.

1. Do I need to edit the report or query(s) to fix this? Perhaps I need to
treat the Survey Date not as a survey question (i.e., Rspns), but as a
category on the same level as RspnsID.

2. I would prefer to use a form instead of a parameter query to pass the
start and end date values, but I understand that this can't be done with
aggregate or crosstab queries (see
http://users.bigpond.net.au/abrowne1/casu-08.html). Is this true?

Thank you for your continued help. Kurt (Full SQLs below)


Full SQL of qselQstnTextRspnsxCount
-----------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum, qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2, qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns, qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount
WHERE (((qxtbQstnTextRspnsxCount.SurveyDate) Between
[StartDate] And [EndDate]));

Full SQL of qxtbQstnTextRspnsxCount
-----------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
Max(CDate(IIf(tblResponses!QstnID=2,
[Rspns],#1/1/1900#))) AS SurveyDate
FROM tblQuestions INNER JOIN tblResponses ON
tblQuestions.QstnID=tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo" Or
(tblQuestions.QstnType)="ID"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");

-----Original Message-----
Looks like my "At Your Survey" application. I would add another column in
the crosstab
Field: SurveyDate: CDate(IIf([tblResponses]![QstnID]=14,[Rspns],#1/1/1900#))
Total: Max
Crosstab: Row Heading
Substitute your QstnID in place of 14.

--
Duane Hookom
MS Access MVP

I've based a report on a crosstab query. The report shows
frequency statistics for survey data. One question on the
survey, which is entered for each respondent, is Survey
Date. I'd like to user to be able to limit the
report/query to data between a start and end survey date.
(e.g., show all surveys entered between 2/3/2003 and
4/3/2003).

The problem is that there is no "SurveyDate" field (the
data is fully normalized so "QstnText" is the field and
the value depends on the question), so I can't simply add
a parameter in the query for "SurveyDate".

I need something like:

If tblQuestions.QstnText = "Survey Date" Then . . .

Below is the current SQL statement for the query. How can
I edit it so that the user can limit the data based on
the survey date?

Thanks. Kurt

***************************************************
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
FROM tblQuestions
INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
GROUP BY tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");
***************************************************

.

 

[Kurt]

I created the simple query (qrySurveyDate), included it
the crosstab query, and the report is essentially
working. However, the date parameter isn't working
consistently.

For example, there are 5 surveys entered, with the
following Survey Dates 2/3/2003, 2/4/2003, 2/10/2003,
3/20/2003, and 5/3/2003.

The query output is not always within the date range
specified in the Start and End Date prompts. Here are
some examples of start and end dates entered and the
resulting output:

Start Date End Date # of records
Entered Entered retrieved
---------------------------------------------
1/2/2003 8/2/2003 5 (correct)
1/3/2003 12/1/2003 0 (incorrect; should be 5)
2/3/2003 5/2/2003 3 (incorrect; should be 4)
- shows records for 2/3/2003, 3/4/2003, & 3/20/2003
2/3/2003 3/3/2003 3 (correct number, but wrong
records)
- shows record for 2/3/2003, 2/4/2003, 3/20/2003
1/2/2003 2/2/2003 1 (incorrect; should be 0)
- shows record for 2/10/2003

I wonder if the problem has something to do with how the
date was initially entered in the form. Although an input
mask (!99/99/0000;0;" ") helps control how the date is
entered, the control is text type (not date type), so the
user could actually enter 56/92/2897 without a problem.
I have since added code to ensure that the date entered
conforms to a date field:

Private Sub Rspns_AfterUpdate()
' Since Survey Date is the only Qstn with a
' QstnMask, this will work
If Me![QstnMaskYN] = True Then
Dim LstrDate As String
Dim LDate As Date

LstrDate = Me.Rspns
LDate = CDate(LstrDate)
End If
End Sub

.. . . but the parameter query continues to include
records which don't conform to the dates entered. Perhaps
it's the input mask?

Any ideas? - Kurt

Here's the SQL for qrySurveyDate
--------------------------------
SELECT tblResponses.RspnsID, tblResponses.QstnID, CDate
([Rspns]) AS SurveyDate
FROM tblResponses
WHERE (((tblResponses.QstnID)=2) AND ((CDate([Rspns]))
Between [StartDate] And [EndDate]));

-----Original Message-----
I would create a simple query that results in the RspnsID from tblResponses
where the QstnID is your specific question and the CDate ([Rspns]) meets your
criteria. Then join this query into your crosstab and join the RspnsId
fields.

--
Duane Hookom
MS Access MVP

Looks like my "At Your Survey" application

Good call.

I edited the crosstab query (qxtbQstnTextRspnsxCount) like you suggested so
it now has a SurveyDate field.
After that, I added the parameter criteria to qselQstnTextRspnsxCount, which
is what the report is based on (ala the Statistics report in "At Your
Survey").

However, due to the way the report (or query) is set up, the "parametized"
report only shows one "Response." For example, if the start and end date
parameters are 2/1/2003 and 2/5/2003, the Statistics report (like the one in
the "At Your Survey") shows this:

Quest# Question Text Response #0f Resp. %of Resp.
----------------------------------------------------
2 Survey Date 2/4/2003 1 50.0%
2/3/2003 1 50.0%

.. . . as opposed to *all* the responses/records which were entered within
the date range provided.

1. Do I need to edit the report or query(s) to fix this? Perhaps I need to
treat the Survey Date not as a survey question (i.e., Rspns), but as a
category on the same level as RspnsID.

2. I would prefer to use a form instead of a parameter query to pass the
start and end date values, but I understand that this can't be done with
aggregate or crosstab queries (see
http://users.bigpond.net.au/abrowne1/casu-08.html). Is this true?

Thank you for your continued help. Kurt (Full SQLs below)


Full SQL of qselQstnTextRspnsxCount
-----------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum, qxtbQstnTextRspnsxCount.QstnLvl1,

qxtbQstnTextRspnsxCount.QstnLvl2,

qxtbQstnTextRspnsxCount.QstnText,

qxtbQstnTextRspnsxCount.Rspns,

qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount
WHERE (((qxtbQstnTextRspnsxCount.SurveyDate) Between
[StartDate] And [EndDate]));

Full SQL of qxtbQstnTextRspnsxCount
-----------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
Max(CDate(IIf(tblResponses!QstnID=2,
[Rspns],#1/1/1900#))) AS SurveyDate
FROM tblQuestions INNER JOIN tblResponses ON
tblQuestions.QstnID=tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo" Or
(tblQuestions.QstnType)="ID"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");

-----Original Message-----
Looks like my "At Your Survey" application. I would add another column in
the crosstab
Field: SurveyDate: CDate(IIf([tblResponses]![QstnID]=14,[Rspns],#1/1/1900#))
Total: Max
Crosstab: Row Heading
Substitute your QstnID in place of 14.

--
Duane Hookom
MS Access MVP

I've based a report on a crosstab query. The report shows
frequency statistics for survey data. One question on the
survey, which is entered for each respondent, is Survey
Date. I'd like to user to be able to limit the
report/query to data between a start and end survey date.
(e.g., show all surveys entered between 2/3/2003 and
4/3/2003).

The problem is that there is no "SurveyDate" field (the
data is fully normalized so "QstnText" is the field and
the value depends on the question), so I can't simply add
a parameter in the query for "SurveyDate".

I need something like:

If tblQuestions.QstnText = "Survey Date" Then . . .

Below is the current SQL statement for the query. How can
I edit it so that the user can limit the data based on
the survey date?

Thanks. Kurt

***************************************************
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
FROM tblQuestions
INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
GROUP BY tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");
***************************************************

.

.

 

[Kurt]

Fixed it. In the query, I declared two parameters of type
Date/Time: one for StartDate and one for EndDate.

The query now consistently limits the records as
indicated.

Thank you for all your help getting me to this point.

Kurt

-----Original Message-----
I created the simple query (qrySurveyDate), included it
the crosstab query, and the report is essentially
working. However, the date parameter isn't working
consistently.

For example, there are 5 surveys entered, with the
following Survey Dates 2/3/2003, 2/4/2003, 2/10/2003,
3/20/2003, and 5/3/2003.

The query output is not always within the date range
specified in the Start and End Date prompts. Here are
some examples of start and end dates entered and the
resulting output:

Start Date End Date # of records
Entered Entered retrieved
---------------------------------------------
1/2/2003 8/2/2003 5 (correct)
1/3/2003 12/1/2003 0 (incorrect; should be 5)
2/3/2003 5/2/2003 3 (incorrect; should be 4)
- shows records for 2/3/2003, 3/4/2003, & 3/20/2003
2/3/2003 3/3/2003 3 (correct number, but wrong
records)
- shows record for 2/3/2003, 2/4/2003, 3/20/2003
1/2/2003 2/2/2003 1 (incorrect; should be 0)
- shows record for 2/10/2003

I wonder if the problem has something to do with how the
date was initially entered in the form. Although an input
mask (!99/99/0000;0;" ") helps control how the date is
entered, the control is text type (not date type), so the
user could actually enter 56/92/2897 without a problem.
I have since added code to ensure that the date entered
conforms to a date field:

Private Sub Rspns_AfterUpdate()
' Since Survey Date is the only Qstn with a
' QstnMask, this will work
If Me![QstnMaskYN] = True Then
Dim LstrDate As String
Dim LDate As Date

LstrDate = Me.Rspns
LDate = CDate(LstrDate)
End If
End Sub

.. . . but the parameter query continues to include
records which don't conform to the dates entered. Perhaps
it's the input mask?

Any ideas? - Kurt

Here's the SQL for qrySurveyDate
--------------------------------
SELECT tblResponses.RspnsID, tblResponses.QstnID, CDate
([Rspns]) AS SurveyDate
FROM tblResponses
WHERE (((tblResponses.QstnID)=2) AND ((CDate([Rspns]))
Between [StartDate] And [EndDate]));

-----Original Message-----
I would create a simple query that results in the RspnsID from tblResponses
where the QstnID is your specific question and the CDate ([Rspns]) meets your
criteria. Then join this query into your crosstab and join the RspnsId
fields.

--
Duane Hookom
MS Access MVP

Looks like my "At Your Survey" application

Good call.

I edited the crosstab query (qxtbQstnTextRspnsxCount) like you suggested so
it now has a SurveyDate field.
After that, I added the parameter criteria to qselQstnTextRspnsxCount, which
is what the report is based on (ala the Statistics report in "At Your
Survey").

However, due to the way the report (or query) is set

up,
the "parametized"

report only shows one "Response." For example, if the start and end date
parameters are 2/1/2003 and 2/5/2003, the Statistics report (like the one in
the "At Your Survey") shows this:

Quest# Question Text Response #0f Resp. %of Resp.
----------------------------------------------------
2 Survey Date 2/4/2003 1 50.0%
2/3/2003 1 50.0%

.. . . as opposed to *all* the responses/records which were entered within
the date range provided.

1. Do I need to edit the report or query(s) to fix

this?
Perhaps I need to

treat the Survey Date not as a survey question (i.e., Rspns), but as a
category on the same level as RspnsID.

2. I would prefer to use a form instead of a parameter query to pass the
start and end date values, but I understand that this can't be done with
aggregate or crosstab queries (see
http://users.bigpond.net.au/abrowne1/casu-08.html). Is this true?

Thank you for your continued help. Kurt (Full SQLs below)


Full SQL of qselQstnTextRspnsxCount
-----------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum, qxtbQstnTextRspnsxCount.QstnLvl1,

qxtbQstnTextRspnsxCount.QstnLvl2,

qxtbQstnTextRspnsxCount.QstnText,

qxtbQstnTextRspnsxCount.Rspns,

qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount
WHERE (((qxtbQstnTextRspnsxCount.SurveyDate) Between
[StartDate] And [EndDate]));

Full SQL of qxtbQstnTextRspnsxCount
-----------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
Max(CDate(IIf(tblResponses!QstnID=2,
[Rspns],#1/1/1900#))) AS SurveyDate
FROM tblQuestions INNER JOIN tblResponses ON
tblQuestions.QstnID=tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo" Or
(tblQuestions.QstnType)="ID"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");

-----Original Message-----
Looks like my "At Your Survey" application. I would

add
another column in

the crosstab
Field: SurveyDate:

CDate(IIf([tblResponses]![QstnID]=14, [Rspns],#1/1/1900#))

Total: Max
Crosstab: Row Heading
Substitute your QstnID in place of 14.

--
Duane Hookom
MS Access MVP


I've based a report on a crosstab query. The report shows
frequency statistics for survey data. One question

on

the

simply

add

How

can


.

.

 

[Kurt]

And I thought I was done. For those who haven't lost
patient with my interminable problem . . .

The (hopefully last) problem I have now is that, since
adding the qrySurveyDate to the crosstabs, the report is
showing responses for *each* respondent for *each* survey
date that falls with the range specified. For example:

Question Text Rspns #ofResp. %ofResp.
-----------------------------------------------------
The staff was helpful Agree 1 33.3%
Strongly Agree 1 33.3%
Agree 1 33.3%
------------------------------
Total Responses 3 100.0%

....

I'm not sure if I need to edit the report or one of the
underlying queries, or somehow Group By the SurveyDate
range that's given?

Here's what I did:

1. I made the simple query (qrySurveyDate) with a
SurveyDate2 field. (SQL below)

2. I added qrySurveyDate to qxtbQstnTextRspnsxCount, and
joined the RspnsID (of tblResponses and qrySurveyDate). I
dragged SurveyDate2 into the grid, and gave it Group By
and Row Heading properties. (SQL below)

3. I then edited qselQxtTextRspnsxCount (the query the
rptStatistics is based on) by dragging the SurveyDate2
field into the grid. (SQL below)

Thanks again for any guidance. - Kurt

SQL for qrySurveyDate
---------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblResponses.RspnsID, CDate([Rspns]) AS SurveyDate2
FROM tblResponses
WHERE (((CDate([Rspns])) Between [StartDate] And
[EndDate]) AND ((tblResponses.QstnID)=2));

SQL for qxtbQstnTextRspnsxCount
-------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
qrySurveyDate.SurveyDate2, Max((CDate(IIf(tblResponses!
QstnID=2,[Rspns],#1/1/1900#)))) AS SurveyDate
FROM tblQuestions INNER JOIN (tblResponses INNER JOIN
qrySurveyDate ON tblResponses.RspnsID =
qrySurveyDate.RspnsID) ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
qrySurveyDate.SurveyDate2
PIVOT "Number of Responses" In ("Number of Responses");

SQL for qselQxtTextRspnsxCount (basis for report)
-------------------------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum,
qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2,
qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns,
qxtbQstnTextRspnsxCount.SurveyDate2,
qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount;

-----Original Message-----
Fixed it. In the query, I declared two parameters of type
Date/Time: one for StartDate and one for EndDate.

The query now consistently limits the records as
indicated.

Thank you for all your help getting me to this point.

Kurt

-----Original Message-----
I created the simple query (qrySurveyDate), included it
the crosstab query, and the report is essentially
working. However, the date parameter isn't working
consistently.

For example, there are 5 surveys entered, with the
following Survey Dates 2/3/2003, 2/4/2003, 2/10/2003,
3/20/2003, and 5/3/2003.

The query output is not always within the date range
specified in the Start and End Date prompts. Here are
some examples of start and end dates entered and the
resulting output:

Start Date End Date # of records
Entered Entered retrieved
---------------------------------------------
1/2/2003 8/2/2003 5 (correct)
1/3/2003 12/1/2003 0 (incorrect; should be 5)
2/3/2003 5/2/2003 3 (incorrect; should be 4)
- shows records for 2/3/2003, 3/4/2003, & 3/20/2003
2/3/2003 3/3/2003 3 (correct number, but wrong
records)
- shows record for 2/3/2003, 2/4/2003, 3/20/2003
1/2/2003 2/2/2003 1 (incorrect; should be 0)
- shows record for 2/10/2003

I wonder if the problem has something to do with how the
date was initially entered in the form. Although an input
mask (!99/99/0000;0;" ") helps control how the date is
entered, the control is text type (not date type), so the
user could actually enter 56/92/2897 without a problem.
I have since added code to ensure that the date entered
conforms to a date field:

Private Sub Rspns_AfterUpdate()
' Since Survey Date is the only Qstn with a
' QstnMask, this will work
If Me![QstnMaskYN] = True Then
Dim LstrDate As String
Dim LDate As Date

LstrDate = Me.Rspns
LDate = CDate(LstrDate)
End If
End Sub

.. . . but the parameter query continues to include
records which don't conform to the dates entered. Perhaps
it's the input mask?

Any ideas? - Kurt

Here's the SQL for qrySurveyDate
--------------------------------
SELECT tblResponses.RspnsID, tblResponses.QstnID, CDate
([Rspns]) AS SurveyDate
FROM tblResponses
WHERE (((tblResponses.QstnID)=2) AND ((CDate([Rspns]))
Between [StartDate] And [EndDate]));

-----Original Message-----
I would create a simple query that results in the RspnsID from tblResponses
where the QstnID is your specific question and the

CDate
([Rspns]) meets your

criteria. Then join this query into your crosstab and join the RspnsId
fields.

--
Duane Hookom
MS Access MVP


Looks like my "At Your Survey" application

Good call.

I edited the crosstab query (qxtbQstnTextRspnsxCount) like you suggested so
it now has a SurveyDate field.
After that, I added the parameter criteria to qselQstnTextRspnsxCount, which
is what the report is based on (ala the Statistics report in "At Your
Survey").

However, due to the way the report (or query) is set

up,
the "parametized"

report only shows one "Response." For example, if the start and end date
parameters are 2/1/2003 and 2/5/2003, the Statistics report (like the one in
the "At Your Survey") shows this:

Quest# Question Text Response #0f Resp. %of Resp.
----------------------------------------------------
2 Survey Date 2/4/2003 1 50.0%
2/3/2003 1 50.0%

.. . . as opposed to *all* the responses/records which were entered within
the date range provided.

1. Do I need to edit the report or query(s) to fix

this?
Perhaps I need to

treat the Survey Date not as a survey question (i.e., Rspns), but as a
category on the same level as RspnsID.

2. I would prefer to use a form instead of a parameter query to pass the
start and end date values, but I understand that this can't be done with
aggregate or crosstab queries (see
http://users.bigpond.net.au/abrowne1/casu-08.html). Is this true?

Thank you for your continued help. Kurt (Full SQLs below)


Full SQL of qselQstnTextRspnsxCount
-----------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum, qxtbQstnTextRspnsxCount.QstnLvl1,

qxtbQstnTextRspnsxCount.QstnLvl2,

qxtbQstnTextRspnsxCount.QstnText,

qxtbQstnTextRspnsxCount.Rspns,

qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount
WHERE (((qxtbQstnTextRspnsxCount.SurveyDate) Between
[StartDate] And [EndDate]));

Full SQL of qxtbQstnTextRspnsxCount
-----------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
Max(CDate(IIf(tblResponses!QstnID=2,
[Rspns],#1/1/1900#))) AS SurveyDate
FROM tblQuestions INNER JOIN tblResponses ON
tblQuestions.QstnID=tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo" Or
(tblQuestions.QstnType)="ID"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");


-----Original Message-----
Looks like my "At Your Survey" application. I would

add
another column in

the crosstab
Field: SurveyDate:
CDate(IIf([tblResponses]![QstnID]=14, [Rspns],#1/1/1900#))
Total: Max
Crosstab: Row Heading
Substitute your QstnID in place of 14.

--
Duane Hookom
MS Access MVP


I've based a report on a crosstab query. The report shows
frequency statistics for survey data. One question

on

the
survey, which is entered for each respondent, is Survey
Date. I'd like to user to be able to limit the
report/query to data between a start and end survey date.
(e.g., show all surveys entered between 2/3/2003 and
4/3/2003).

The problem is that there is no "SurveyDate" field (the
data is fully normalized so "QstnText" is the field and
the value depends on the question), so I can't

simply

add
a parameter in the query for "SurveyDate".

I need something like:

If tblQuestions.QstnText = "Survey Date" Then . . .

Below is the current SQL statement for the query.

How

can
I edit it so that the user can limit the data based on
the survey date?

Thanks. Kurt

***************************************************
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
FROM tblQuestions
INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
GROUP BY tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");
***************************************************


.



.

.

.

 

[Duane Hookom]

I am curious why you added the SurveyDate2 to the query. I would only return
the unique RspnsIDs
SQL for qrySurveyDate
---------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblResponses.RspnsID
FROM tblResponses
WHERE (((CDate([Rspns])) Between [StartDate] And
[EndDate]) AND ((tblResponses.QstnID)=2));
--
Duane Hookom
MS Access MVP

And I thought I was done. For those who haven't lost
patient with my interminable problem . . .

The (hopefully last) problem I have now is that, since
adding the qrySurveyDate to the crosstabs, the report is
showing responses for *each* respondent for *each* survey
date that falls with the range specified. For example:

Question Text Rspns #ofResp. %ofResp.
-----------------------------------------------------
The staff was helpful Agree 1 33.3%
Strongly Agree 1 33.3%
Agree 1 33.3%
------------------------------
Total Responses 3 100.0%

...

I'm not sure if I need to edit the report or one of the
underlying queries, or somehow Group By the SurveyDate
range that's given?

Here's what I did:

1. I made the simple query (qrySurveyDate) with a
SurveyDate2 field. (SQL below)

2. I added qrySurveyDate to qxtbQstnTextRspnsxCount, and
joined the RspnsID (of tblResponses and qrySurveyDate). I
dragged SurveyDate2 into the grid, and gave it Group By
and Row Heading properties. (SQL below)

3. I then edited qselQxtTextRspnsxCount (the query the
rptStatistics is based on) by dragging the SurveyDate2
field into the grid. (SQL below)

Thanks again for any guidance. - Kurt

SQL for qrySurveyDate
---------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblResponses.RspnsID, CDate([Rspns]) AS SurveyDate2
FROM tblResponses
WHERE (((CDate([Rspns])) Between [StartDate] And
[EndDate]) AND ((tblResponses.QstnID)=2));

SQL for qxtbQstnTextRspnsxCount
-------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
qrySurveyDate.SurveyDate2, Max((CDate(IIf(tblResponses!
QstnID=2,[Rspns],#1/1/1900#)))) AS SurveyDate
FROM tblQuestions INNER JOIN (tblResponses INNER JOIN
qrySurveyDate ON tblResponses.RspnsID =
qrySurveyDate.RspnsID) ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
qrySurveyDate.SurveyDate2
PIVOT "Number of Responses" In ("Number of Responses");

SQL for qselQxtTextRspnsxCount (basis for report)
-------------------------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum,
qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2,
qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns,
qxtbQstnTextRspnsxCount.SurveyDate2,
qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount;

-----Original Message-----
Fixed it. In the query, I declared two parameters of type
Date/Time: one for StartDate and one for EndDate.

The query now consistently limits the records as
indicated.

Thank you for all your help getting me to this point.

Kurt

-----Original Message-----
I created the simple query (qrySurveyDate), included it
the crosstab query, and the report is essentially
working. However, the date parameter isn't working
consistently.

For example, there are 5 surveys entered, with the
following Survey Dates 2/3/2003, 2/4/2003, 2/10/2003,
3/20/2003, and 5/3/2003.

The query output is not always within the date range
specified in the Start and End Date prompts. Here are
some examples of start and end dates entered and the
resulting output:

Start Date End Date # of records
Entered Entered retrieved
---------------------------------------------
1/2/2003 8/2/2003 5 (correct)
1/3/2003 12/1/2003 0 (incorrect; should be 5)
2/3/2003 5/2/2003 3 (incorrect; should be 4)
- shows records for 2/3/2003, 3/4/2003, & 3/20/2003
2/3/2003 3/3/2003 3 (correct number, but wrong
records)
- shows record for 2/3/2003, 2/4/2003, 3/20/2003
1/2/2003 2/2/2003 1 (incorrect; should be 0)
- shows record for 2/10/2003

I wonder if the problem has something to do with how the
date was initially entered in the form. Although an input
mask (!99/99/0000;0;" ") helps control how the date is
entered, the control is text type (not date type), so the
user could actually enter 56/92/2897 without a problem.
I have since added code to ensure that the date entered
conforms to a date field:

Private Sub Rspns_AfterUpdate()
' Since Survey Date is the only Qstn with a
' QstnMask, this will work
If Me![QstnMaskYN] = True Then
Dim LstrDate As String
Dim LDate As Date

LstrDate = Me.Rspns
LDate = CDate(LstrDate)
End If
End Sub

.. . . but the parameter query continues to include
records which don't conform to the dates entered. Perhaps
it's the input mask?

Any ideas? - Kurt

Here's the SQL for qrySurveyDate
--------------------------------
SELECT tblResponses.RspnsID, tblResponses.QstnID, CDate
([Rspns]) AS SurveyDate
FROM tblResponses
WHERE (((tblResponses.QstnID)=2) AND ((CDate([Rspns]))
Between [StartDate] And [EndDate]));



-----Original Message-----
I would create a simple query that results in the
RspnsID from tblResponses
where the QstnID is your specific question and the CDate
([Rspns]) meets your
criteria. Then join this query into your crosstab and
join the RspnsId
fields.

--
Duane Hookom
MS Access MVP


message
Looks like my "At Your Survey" application

Good call.

I edited the crosstab query (qxtbQstnTextRspnsxCount)
like you suggested so
it now has a SurveyDate field.
After that, I added the parameter criteria to
qselQstnTextRspnsxCount, which
is what the report is based on (ala the Statistics
report in "At Your
Survey").

However, due to the way the report (or query) is set up,
the "parametized"
report only shows one "Response." For example, if the
start and end date
parameters are 2/1/2003 and 2/5/2003, the Statistics
report (like the one in
the "At Your Survey") shows this:

Quest# Question Text Response #0f Resp. %of Resp.
----------------------------------------------------
2 Survey Date 2/4/2003 1 50.0%
2/3/2003 1 50.0%

.. . . as opposed to *all* the responses/records which
were entered within
the date range provided.

1. Do I need to edit the report or query(s) to fix this?
Perhaps I need to
treat the Survey Date not as a survey question (i.e.,
Rspns), but as a
category on the same level as RspnsID.

2. I would prefer to use a form instead of a parameter
query to pass the
start and end date values, but I understand that this
can't be done with
aggregate or crosstab queries (see
http://users.bigpond.net.au/abrowne1/casu-08.html). Is
this true?

Thank you for your continued help. Kurt (Full SQLs below)


Full SQL of qselQstnTextRspnsxCount
-----------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum,
qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2,
qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns,
qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount
WHERE (((qxtbQstnTextRspnsxCount.SurveyDate) Between
[StartDate] And [EndDate]));

Full SQL of qxtbQstnTextRspnsxCount
-----------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
Max(CDate(IIf(tblResponses!QstnID=2,
[Rspns],#1/1/1900#))) AS SurveyDate
FROM tblQuestions INNER JOIN tblResponses ON
tblQuestions.QstnID=tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo" Or
(tblQuestions.QstnType)="ID"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");


-----Original Message-----
Looks like my "At Your Survey" application. I would add
another column in
the crosstab
Field: SurveyDate:
CDate(IIf([tblResponses]![QstnID]=14, [Rspns],#1/1/1900#))
Total: Max
Crosstab: Row Heading
Substitute your QstnID in place of 14.

--
Duane Hookom
MS Access MVP


message
I've based a report on a crosstab query. The report
shows
frequency statistics for survey data. One question on
the
survey, which is entered for each respondent, is
Survey
Date. I'd like to user to be able to limit the
report/query to data between a start and end survey
date.
(e.g., show all surveys entered between 2/3/2003 and
4/3/2003).

The problem is that there is no "SurveyDate" field
(the
data is fully normalized so "QstnText" is the field
and
the value depends on the question), so I can't simply
add
a parameter in the query for "SurveyDate".

I need something like:

If tblQuestions.QstnText = "Survey Date"
Then . . .

Below is the current SQL statement for the query. How
can
I edit it so that the user can limit the data based on
the survey date?

Thanks. Kurt

***************************************************
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
FROM tblQuestions
INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
GROUP BY tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of
Responses");
***************************************************


.



.

.

.

 

[Kurt]

I am curious why you added the SurveyDate2 to the query.

I misunderstood its use. I've since removed it, and edited qrySurveyDate (SQL below) to get its parameters from a form while allowing for null.

Dare I say it but . . . I think this database is done. Everything is finally working as it should.

Thanks again Duane for all your help, patience, and time.

Kurt

Full SQL for qrySurveyDate
----------------------------
SELECT tblResponses.RspnsID
FROM tblResponses
WHERE (((CDate([Rspns])) Between [Forms]!
[frmnuReportSelect]![txtStartDate] And [Forms]!
[frmnuReportSelect]![txtEndDate]) AND
((tblResponses.QstnID)=2)) OR (((tblResponses.QstnID)
=2) AND (((CDate([Rspns])) Like ((CDate([Rspns]))
Between [Forms]![frmnuReportSelect]![txtStartDate]
And [Forms]![frmnuReportSelect]![txtEndDate])) Is
Null));

-----Original Message-----
I am curious why you added the SurveyDate2 to the query. I would only return
the unique RspnsIDs
SQL for qrySurveyDate
---------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblResponses.RspnsID
FROM tblResponses
WHERE (((CDate([Rspns])) Between [StartDate] And
[EndDate]) AND ((tblResponses.QstnID)=2));
--
Duane Hookom
MS Access MVP

And I thought I was done. For those who haven't lost
patient with my interminable problem . . .

The (hopefully last) problem I have now is that, since
adding the qrySurveyDate to the crosstabs, the report is
showing responses for *each* respondent for *each* survey
date that falls with the range specified. For example:

Question Text Rspns #ofResp. %ofResp.
-----------------------------------------------------
The staff was helpful Agree 1 33.3%
Strongly Agree 1 33.3%
Agree 1 33.3%
------------------------------
Total Responses 3 100.0%

...

I'm not sure if I need to edit the report or one of the
underlying queries, or somehow Group By the SurveyDate
range that's given?

Here's what I did:

1. I made the simple query (qrySurveyDate) with a
SurveyDate2 field. (SQL below)

2. I added qrySurveyDate to qxtbQstnTextRspnsxCount, and
joined the RspnsID (of tblResponses and qrySurveyDate). I
dragged SurveyDate2 into the grid, and gave it Group By
and Row Heading properties. (SQL below)

3. I then edited qselQxtTextRspnsxCount (the query the
rptStatistics is based on) by dragging the SurveyDate2
field into the grid. (SQL below)

Thanks again for any guidance. - Kurt

SQL for qrySurveyDate
---------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblResponses.RspnsID, CDate([Rspns]) AS SurveyDate2
FROM tblResponses
WHERE (((CDate([Rspns])) Between [StartDate] And
[EndDate]) AND ((tblResponses.QstnID)=2));

SQL for qxtbQstnTextRspnsxCount
-------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
qrySurveyDate.SurveyDate2, Max((CDate(IIf(tblResponses!
QstnID=2,[Rspns],#1/1/1900#)))) AS SurveyDate
FROM tblQuestions INNER JOIN (tblResponses INNER JOIN
qrySurveyDate ON tblResponses.RspnsID =
qrySurveyDate.RspnsID) ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
qrySurveyDate.SurveyDate2
PIVOT "Number of Responses" In ("Number of Responses");

SQL for qselQxtTextRspnsxCount (basis for report)
-------------------------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum,
qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2,
qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns,
qxtbQstnTextRspnsxCount.SurveyDate2,
qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount;

-----Original Message-----
Fixed it. In the query, I declared two parameters of type
Date/Time: one for StartDate and one for EndDate.

The query now consistently limits the records as
indicated.

Thank you for all your help getting me to this point.

Kurt


-----Original Message-----
I created the simple query (qrySurveyDate), included it
the crosstab query, and the report is essentially
working. However, the date parameter isn't working
consistently.

For example, there are 5 surveys entered, with the
following Survey Dates 2/3/2003, 2/4/2003, 2/10/2003,
3/20/2003, and 5/3/2003.

The query output is not always within the date range
specified in the Start and End Date prompts. Here are
some examples of start and end dates entered and the
resulting output:

Start Date End Date # of records
Entered Entered retrieved
---------------------------------------------
1/2/2003 8/2/2003 5 (correct)
1/3/2003 12/1/2003 0 (incorrect; should be 5)
2/3/2003 5/2/2003 3 (incorrect; should be 4)
- shows records for 2/3/2003, 3/4/2003, & 3/20/2003
2/3/2003 3/3/2003 3 (correct number, but wrong
records)
- shows record for 2/3/2003, 2/4/2003, 3/20/2003
1/2/2003 2/2/2003 1 (incorrect; should be 0)
- shows record for 2/10/2003

I wonder if the problem has something to do with how the
date was initially entered in the form. Although an
input
mask (!99/99/0000;0;" ") helps control how the date is
entered, the control is text type (not date type), so
the
user could actually enter 56/92/2897 without a problem.
I have since added code to ensure that the date entered
conforms to a date field:

Private Sub Rspns_AfterUpdate()
' Since Survey Date is the only Qstn with a
' QstnMask, this will work
If Me![QstnMaskYN] = True Then
Dim LstrDate As String
Dim LDate As Date

LstrDate = Me.Rspns
LDate = CDate(LstrDate)
End If
End Sub

.. . . but the parameter query continues to include
records which don't conform to the dates entered.
Perhaps
it's the input mask?

Any ideas? - Kurt

Here's the SQL for qrySurveyDate
--------------------------------
SELECT tblResponses.RspnsID, tblResponses.QstnID, CDate
([Rspns]) AS SurveyDate
FROM tblResponses
WHERE (((tblResponses.QstnID)=2) AND ((CDate([Rspns]))
Between [StartDate] And [EndDate]));



-----Original Message-----
I would create a simple query that results in the
RspnsID from tblResponses
where the QstnID is your specific question and the CDate
([Rspns]) meets your
criteria. Then join this query into your crosstab and
join the RspnsId
fields.

--
Duane Hookom
MS Access MVP


message
Looks like my "At Your Survey" application

Good call.

I edited the crosstab query (qxtbQstnTextRspnsxCount)
like you suggested so
it now has a SurveyDate field.
After that, I added the parameter criteria to
qselQstnTextRspnsxCount, which
is what the report is based on (ala the Statistics
report in "At Your
Survey").

However, due to the way the report (or query) is set
up,
the "parametized"
report only shows one "Response." For example, if the
start and end date
parameters are 2/1/2003 and 2/5/2003, the Statistics
report (like the one in
the "At Your Survey") shows this:

Quest# Question Text Response #0f Resp. %of Resp.
----------------------------------------------------
2 Survey Date 2/4/2003 1 50.0%
2/3/2003 1 50.0%

.. . . as opposed to *all* the responses/records which
were entered within
the date range provided.

1. Do I need to edit the report or query(s) to fix
this?
Perhaps I need to
treat the Survey Date not as a survey question (i.e.,
Rspns), but as a
category on the same level as RspnsID.

2. I would prefer to use a form instead of a parameter
query to pass the
start and end date values, but I understand that this
can't be done with
aggregate or crosstab queries (see
http://users.bigpond.net.au/abrowne1/casu-08.html). Is
this true?

Thank you for your continued help. Kurt (Full SQLs
below)


Full SQL of qselQstnTextRspnsxCount
-----------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum,
qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2,
qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns,
qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount
WHERE (((qxtbQstnTextRspnsxCount.SurveyDate) Between
[StartDate] And [EndDate]));

Full SQL of qxtbQstnTextRspnsxCount
-----------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
Max(CDate(IIf(tblResponses!QstnID=2,
[Rspns],#1/1/1900#))) AS SurveyDate
FROM tblQuestions INNER JOIN tblResponses ON
tblQuestions.QstnID=tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo" Or
(tblQuestions.QstnType)="ID"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");


-----Original Message-----
Looks like my "At Your Survey" application. I would
add
another column in
the crosstab
Field: SurveyDate:
CDate(IIf([tblResponses]![QstnID]=14,
[Rspns],#1/1/1900#))
Total: Max
Crosstab: Row Heading
Substitute your QstnID in place of 14.

--
Duane Hookom
MS Access MVP


message
I've based a report on a crosstab query. The report
shows
frequency statistics for survey data. One question
on
the
survey, which is entered for each respondent, is
Survey
Date. I'd like to user to be able to limit the
report/query to data between a start and end survey
date.
(e.g., show all surveys entered between 2/3/2003 and
4/3/2003).

The problem is that there is no "SurveyDate" field
(the
data is fully normalized so "QstnText" is the field
and
the value depends on the question), so I can't
simply
add
a parameter in the query for "SurveyDate".

I need something like:

If tblQuestions.QstnText = "Survey Date"
Then . . .

Below is the current SQL statement for the query.
How
can
I edit it so that the user can limit the data based
on
the survey date?

Thanks. Kurt

***************************************************
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
FROM tblQuestions
INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
GROUP BY tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of
Responses");
***************************************************


.



.

.

.

.

 

[Duane Hookom]

Glad this is now working for you. One word of caution, a database is NEVER
done ;-)

--
Duane Hookom
MS Access MVP

I am curious why you added the SurveyDate2 to the query.

I misunderstood its use. I've since removed it, and edited qrySurveyDate
(SQL below) to get its parameters from a form while allowing for null.

Dare I say it but . . . I think this database is done. Everything is finally
working as it should.

Thanks again Duane for all your help, patience, and time.

Kurt

Full SQL for qrySurveyDate
----------------------------
SELECT tblResponses.RspnsID
FROM tblResponses
WHERE (((CDate([Rspns])) Between [Forms]!
[frmnuReportSelect]![txtStartDate] And [Forms]!
[frmnuReportSelect]![txtEndDate]) AND
((tblResponses.QstnID)=2)) OR (((tblResponses.QstnID)
=2) AND (((CDate([Rspns])) Like ((CDate([Rspns]))
Between [Forms]![frmnuReportSelect]![txtStartDate]
And [Forms]![frmnuReportSelect]![txtEndDate])) Is
Null));

-----Original Message-----
I am curious why you added the SurveyDate2 to the query. I would only return
the unique RspnsIDs
SQL for qrySurveyDate
---------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblResponses.RspnsID
FROM tblResponses
WHERE (((CDate([Rspns])) Between [StartDate] And
[EndDate]) AND ((tblResponses.QstnID)=2));
--
Duane Hookom
MS Access MVP

And I thought I was done. For those who haven't lost
patient with my interminable problem . . .

The (hopefully last) problem I have now is that, since
adding the qrySurveyDate to the crosstabs, the report is
showing responses for *each* respondent for *each* survey
date that falls with the range specified. For example:

Question Text Rspns #ofResp. %ofResp.
-----------------------------------------------------
The staff was helpful Agree 1 33.3%
Strongly Agree 1 33.3%
Agree 1 33.3%
------------------------------
Total Responses 3 100.0%

...

I'm not sure if I need to edit the report or one of the
underlying queries, or somehow Group By the SurveyDate
range that's given?

Here's what I did:

1. I made the simple query (qrySurveyDate) with a
SurveyDate2 field. (SQL below)

2. I added qrySurveyDate to qxtbQstnTextRspnsxCount, and
joined the RspnsID (of tblResponses and qrySurveyDate). I
dragged SurveyDate2 into the grid, and gave it Group By
and Row Heading properties. (SQL below)

3. I then edited qselQxtTextRspnsxCount (the query the
rptStatistics is based on) by dragging the SurveyDate2
field into the grid. (SQL below)

Thanks again for any guidance. - Kurt

SQL for qrySurveyDate
---------------------
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblResponses.RspnsID, CDate([Rspns]) AS SurveyDate2
FROM tblResponses
WHERE (((CDate([Rspns])) Between [StartDate] And
[EndDate]) AND ((tblResponses.QstnID)=2));

SQL for qxtbQstnTextRspnsxCount
-------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
qrySurveyDate.SurveyDate2, Max((CDate(IIf(tblResponses!
QstnID=2,[Rspns],#1/1/1900#)))) AS SurveyDate
FROM tblQuestions INNER JOIN (tblResponses INNER JOIN
qrySurveyDate ON tblResponses.RspnsID =
qrySurveyDate.RspnsID) ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
qrySurveyDate.SurveyDate2
PIVOT "Number of Responses" In ("Number of Responses");

SQL for qselQxtTextRspnsxCount (basis for report)
-------------------------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum,
qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2,
qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns,
qxtbQstnTextRspnsxCount.SurveyDate2,
qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount;

-----Original Message-----
Fixed it. In the query, I declared two parameters of type
Date/Time: one for StartDate and one for EndDate.

The query now consistently limits the records as
indicated.

Thank you for all your help getting me to this point.

Kurt


-----Original Message-----
I created the simple query (qrySurveyDate), included it
the crosstab query, and the report is essentially
working. However, the date parameter isn't working
consistently.

For example, there are 5 surveys entered, with the
following Survey Dates 2/3/2003, 2/4/2003, 2/10/2003,
3/20/2003, and 5/3/2003.

The query output is not always within the date range
specified in the Start and End Date prompts. Here are
some examples of start and end dates entered and the
resulting output:

Start Date End Date # of records
Entered Entered retrieved
---------------------------------------------
1/2/2003 8/2/2003 5 (correct)
1/3/2003 12/1/2003 0 (incorrect; should be 5)
2/3/2003 5/2/2003 3 (incorrect; should be 4)
- shows records for 2/3/2003, 3/4/2003, & 3/20/2003
2/3/2003 3/3/2003 3 (correct number, but wrong
records)
- shows record for 2/3/2003, 2/4/2003, 3/20/2003
1/2/2003 2/2/2003 1 (incorrect; should be 0)
- shows record for 2/10/2003

I wonder if the problem has something to do with how the
date was initially entered in the form. Although an
input
mask (!99/99/0000;0;" ") helps control how the date is
entered, the control is text type (not date type), so
the
user could actually enter 56/92/2897 without a problem.
I have since added code to ensure that the date entered
conforms to a date field:

Private Sub Rspns_AfterUpdate()
' Since Survey Date is the only Qstn with a
' QstnMask, this will work
If Me![QstnMaskYN] = True Then
Dim LstrDate As String
Dim LDate As Date

LstrDate = Me.Rspns
LDate = CDate(LstrDate)
End If
End Sub

.. . . but the parameter query continues to include
records which don't conform to the dates entered.
Perhaps
it's the input mask?

Any ideas? - Kurt

Here's the SQL for qrySurveyDate
--------------------------------
SELECT tblResponses.RspnsID, tblResponses.QstnID, CDate
([Rspns]) AS SurveyDate
FROM tblResponses
WHERE (((tblResponses.QstnID)=2) AND ((CDate([Rspns]))
Between [StartDate] And [EndDate]));



-----Original Message-----
I would create a simple query that results in the
RspnsID from tblResponses
where the QstnID is your specific question and the CDate
([Rspns]) meets your
criteria. Then join this query into your crosstab and
join the RspnsId
fields.

--
Duane Hookom
MS Access MVP


message
Looks like my "At Your Survey" application

Good call.

I edited the crosstab query (qxtbQstnTextRspnsxCount)
like you suggested so
it now has a SurveyDate field.
After that, I added the parameter criteria to
qselQstnTextRspnsxCount, which
is what the report is based on (ala the Statistics
report in "At Your
Survey").

However, due to the way the report (or query) is set
up,
the "parametized"
report only shows one "Response." For example, if the
start and end date
parameters are 2/1/2003 and 2/5/2003, the Statistics
report (like the one in
the "At Your Survey") shows this:

Quest# Question Text Response #0f Resp. %of Resp.
----------------------------------------------------
2 Survey Date 2/4/2003 1 50.0%
2/3/2003 1 50.0%

.. . . as opposed to *all* the responses/records which
were entered within
the date range provided.

1. Do I need to edit the report or query(s) to fix
this?
Perhaps I need to
treat the Survey Date not as a survey question (i.e.,
Rspns), but as a
category on the same level as RspnsID.

2. I would prefer to use a form instead of a parameter
query to pass the
start and end date values, but I understand that this
can't be done with
aggregate or crosstab queries (see
http://users.bigpond.net.au/abrowne1/casu-08.html). Is
this true?

Thank you for your continued help. Kurt (Full SQLs
below)


Full SQL of qselQstnTextRspnsxCount
-----------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum,
qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2,
qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns,
qxtbQstnTextRspnsxCount.SurveyDate,
qxtbQstnTextRspnsxCount.[Number of Responses],
FROM qxtbQstnTextRspnsxCount
WHERE (((qxtbQstnTextRspnsxCount.SurveyDate) Between
[StartDate] And [EndDate]));

Full SQL of qxtbQstnTextRspnsxCount
-----------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns,
Max(CDate(IIf(tblResponses!QstnID=2,
[Rspns],#1/1/1900#))) AS SurveyDate
FROM tblQuestions INNER JOIN tblResponses ON
tblQuestions.QstnID=tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo" Or
(tblQuestions.QstnType)="ID"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");


-----Original Message-----
Looks like my "At Your Survey" application. I would
add
another column in
the crosstab
Field: SurveyDate:
CDate(IIf([tblResponses]![QstnID]=14,
[Rspns],#1/1/1900#))
Total: Max
Crosstab: Row Heading
Substitute your QstnID in place of 14.

--
Duane Hookom
MS Access MVP


message
I've based a report on a crosstab query. The report
shows
frequency statistics for survey data. One question
on
the
survey, which is entered for each respondent, is
Survey
Date. I'd like to user to be able to limit the
report/query to data between a start and end survey
date.
(e.g., show all surveys entered between 2/3/2003 and
4/3/2003).

The problem is that there is no "SurveyDate" field
(the
data is fully normalized so "QstnText" is the field
and
the value depends on the question), so I can't
simply
add
a parameter in the query for "SurveyDate".

I need something like:

If tblQuestions.QstnText = "Survey Date"
Then . . .

Below is the current SQL statement for the query.
How
can
I edit it so that the user can limit the data based
on
the survey date?

Thanks. Kurt

***************************************************
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
FROM tblQuestions
INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
GROUP BY tblQuestions.QstnID, tblQuestions.QstnText,
tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of
Responses");
***************************************************


.



.

.

.

.