On 8 Sep 2006 07:59:16 -0700, "Richie" <gsing***@gmail.com> wrote:

>Hello Experts,
>
>I am trying to create a Stored Procedure in SQL Server 2005 from a
>query that works fine in Access, but it does not work on SQL Sever and
>returns a empty table. Any suggestions.
>
>Thanks.
>Richie
>===========================================================
>
>    SELECT tb_Question.QuestionID,
>    Count(tb_Response.ResponseID) AS CountOfResponseID,
>    tb_Question.QuestionDisplaySequence,
>    tb_Answer.AnswerDisplaySequence,
>    tb_Group.GroupID,
>    tb_Group.GroupText,
>    tb_Question.QuestionStatus,
>    tb_Question.Questiontext,
>    tb_Answer.AnswerText,
>    tb_Group.GroupNumbering,
>    tb_Group.GroupDisplaySequence,
>    tb_GroupResponsesSummary.NumberOfRespondents,
>    tb_QuestionResponsesDetail.TotalAnswersForQuestion
>    FROM (((tb_Question INNER JOIN
>    (tb_Group INNER JOIN
>    tb_GroupQuestion ON tb_Group.GroupID = tb_GroupQuestion.GroupID) ON
>    tb_Question.QuestionID = tb_GroupQuestion.QuestionID)
>    INNER JOIN tb_GroupResponsesSummary ON
>    tb_Group.GroupID = tb_GroupResponsesSummary.GroupID)
>    INNER JOIN tb_QuestionResponsesDetail
>    ON (tb_Question.QuestionDisplaySequence =
>tb_QuestionResponsesDetail.QuestionDisplaySequence) AND
>    (tb_Group.GroupID = tb_QuestionResponsesDetail.GroupID))
>    INNER JOIN (tb_Answer INNER JOIN tb_Response
>    ON tb_Answer.AnswerID = tb_Response.AnswerID)
>    ON (tb_Question.QuestionID = tb_Response.QuestionID)
>    AND (tb_Question.QuestionID = tb_Answer.QuestionID)
>    GROUP BY tb_Question.QuestionID,
>    tb_Answer.AnswerText,
>    tb_Question.QuestionDisplaySequence,
>    tb_Answer.AnswerDisplaySequence,
>    tb_Group.GroupID,
>    tb_Group.GroupText,
>    tb_Question.Questiontext,
>    tb_Question.QuestionStatus,
>    tb_Group.GroupNumbering,
>    tb_Group.GroupDisplaySequence,
>    tb_GroupResponsesSummary.NumberOfRespondents,
>    tb_QuestionResponsesDetail.TotalAnswersForQuestion
>    ORDER BY tb_Question.QuestionDisplaySequence,
>    tb_Answer.AnswerDisplaySequence,
>    tb_Group.GroupDisplaySequence

> The (parentheses) around parts of the FROM clause are not the problem,
> but I think they are confusing things.  Since all the joins are INNER
> I would get rid of them.  You might have to change the order of the
> tables.
>
> Since the only selectivity is the ON clauses doing the JOINs, I would
> start diagnosing this  by changing the SELECT list to a simple
> count(*), and removing the GROUP BY.  From what you say, that should
> return zero.  Then, testing at each stage, I would one by one remove
> tables from the JOIN process.  At some point the number of rows
> returned should jumpt from 0 to a number.  Take a close look at
> whatever JOIN you removed to make that change.  Make really sure the
> rows are really in the table.
>
> Roy Harvey
> Beacon Falls, CT
>
> On 8 Sep 2006 07:59:16 -0700, "Richie" <gsing***@gmail.com> wrote:
>
> >Hello Experts,
> >
> >I am trying to create a Stored Procedure in SQL Server 2005 from a
> >query that works fine in Access, but it does not work on SQL Sever and
> >returns a empty table. Any suggestions.
> >
> >Thanks.
> >Richie
> >===========================================================
> >
> >    SELECT tb_Question.QuestionID,
> >    Count(tb_Response.ResponseID) AS CountOfResponseID,
> >    tb_Question.QuestionDisplaySequence,
> >    tb_Answer.AnswerDisplaySequence,
> >    tb_Group.GroupID,
> >    tb_Group.GroupText,
> >    tb_Question.QuestionStatus,
> >    tb_Question.Questiontext,
> >    tb_Answer.AnswerText,
> >    tb_Group.GroupNumbering,
> >    tb_Group.GroupDisplaySequence,
> >    tb_GroupResponsesSummary.NumberOfRespondents,
> >    tb_QuestionResponsesDetail.TotalAnswersForQuestion
> >    FROM (((tb_Question INNER JOIN
> >    (tb_Group INNER JOIN
> >    tb_GroupQuestion ON tb_Group.GroupID = tb_GroupQuestion.GroupID) ON
> >    tb_Question.QuestionID = tb_GroupQuestion.QuestionID)
> >    INNER JOIN tb_GroupResponsesSummary ON
> >    tb_Group.GroupID = tb_GroupResponsesSummary.GroupID)
> >    INNER JOIN tb_QuestionResponsesDetail
> >    ON (tb_Question.QuestionDisplaySequence =
> >tb_QuestionResponsesDetail.QuestionDisplaySequence) AND
> >    (tb_Group.GroupID = tb_QuestionResponsesDetail.GroupID))
> >    INNER JOIN (tb_Answer INNER JOIN tb_Response
> >    ON tb_Answer.AnswerID = tb_Response.AnswerID)
> >    ON (tb_Question.QuestionID = tb_Response.QuestionID)
> >    AND (tb_Question.QuestionID = tb_Answer.QuestionID)
> >    GROUP BY tb_Question.QuestionID,
> >    tb_Answer.AnswerText,
> >    tb_Question.QuestionDisplaySequence,
> >    tb_Answer.AnswerDisplaySequence,
> >    tb_Group.GroupID,
> >    tb_Group.GroupText,
> >    tb_Question.Questiontext,
> >    tb_Question.QuestionStatus,
> >    tb_Group.GroupNumbering,
> >    tb_Group.GroupDisplaySequence,
> >    tb_GroupResponsesSummary.NumberOfRespondents,
> >    tb_QuestionResponsesDetail.TotalAnswersForQuestion
> >    ORDER BY tb_Question.QuestionDisplaySequence,
> >    tb_Answer.AnswerDisplaySequence,
> >    tb_Group.GroupDisplaySequence