T
Tamar
Guest
Background:
I work at a Help Desk; we have someone that daily calls back folks who called us to rate our service. The survey results are stored in a table containing the date of the call, the ticket #, the Employee's name, the skill for the call (application a, application b or technical) and the scores for the 5 questions. There are 2 other fields: Unavailable & Refused - Yes/No fields - if the caller can't get the person or the person doesn't want to complete a survey, then instead of scores the appropriate field is checked.
I've been working for the past few weeks (with someone else's help!!) on creating a query to load into a report to display the totals by day & for the month selected. There is a form that has the parameter dates on it to choose the month for the report - it's referenced in the PARAMETERS line of the query's SQL. They also helped me build column headings for the dates, because I need the report to be formatted with the dates across the top.
My problem: it seems that the WHERE part of the SQL is overriding the PARAMETERS, so I'm getting more than just the month selected.
Here's the SQL:
PARAMETERS [Forms]![frmQASurveyReports]![StartDateChoice] DateTime, [Forms]![frmQASurveyReports]![EndDateChoice] DateTime;
TRANSFORM Count([QA Survey Table].[Ticket#]) AS [CountOfTicket#]
SELECT [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].[Ticket#], [QA Survey Table].Date
FROM [QA Survey Table]
WHERE ((([QA Survey Table].Date) Between DateAdd("d",-30,[Forms]![frmQASurveyReports]![EndDateChoice]) And [Forms]![frmQASurveyReports]![EndDateChoice]))
GROUP BY [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].[Ticket#], [QA Survey Table].Date
PIVOT "D" & DateDiff("d",[Date],[Forms]![frmQASurveyReports]![EndDateChoice]) In ("D0","D1","D2","D3","D4","D5","D6","D7","D8","D9","D10","D11","D12","D13","D14","D15","D16","D17","D18","D19","D20","D21","D22","D23","D24","D25","D26","D27","D28","D29","D30");
Any ideas on how to fix the query? Once that works, I'll move on to fixing the report!!
Thanks.
I work at a Help Desk; we have someone that daily calls back folks who called us to rate our service. The survey results are stored in a table containing the date of the call, the ticket #, the Employee's name, the skill for the call (application a, application b or technical) and the scores for the 5 questions. There are 2 other fields: Unavailable & Refused - Yes/No fields - if the caller can't get the person or the person doesn't want to complete a survey, then instead of scores the appropriate field is checked.
I've been working for the past few weeks (with someone else's help!!) on creating a query to load into a report to display the totals by day & for the month selected. There is a form that has the parameter dates on it to choose the month for the report - it's referenced in the PARAMETERS line of the query's SQL. They also helped me build column headings for the dates, because I need the report to be formatted with the dates across the top.
My problem: it seems that the WHERE part of the SQL is overriding the PARAMETERS, so I'm getting more than just the month selected.
Here's the SQL:
PARAMETERS [Forms]![frmQASurveyReports]![StartDateChoice] DateTime, [Forms]![frmQASurveyReports]![EndDateChoice] DateTime;
TRANSFORM Count([QA Survey Table].[Ticket#]) AS [CountOfTicket#]
SELECT [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].[Ticket#], [QA Survey Table].Date
FROM [QA Survey Table]
WHERE ((([QA Survey Table].Date) Between DateAdd("d",-30,[Forms]![frmQASurveyReports]![EndDateChoice]) And [Forms]![frmQASurveyReports]![EndDateChoice]))
GROUP BY [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].[Ticket#], [QA Survey Table].Date
PIVOT "D" & DateDiff("d",[Date],[Forms]![frmQASurveyReports]![EndDateChoice]) In ("D0","D1","D2","D3","D4","D5","D6","D7","D8","D9","D10","D11","D12","D13","D14","D15","D16","D17","D18","D19","D20","D21","D22","D23","D24","D25","D26","D27","D28","D29","D30");
Any ideas on how to fix the query? Once that works, I'll move on to fixing the report!!
Thanks.