Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    report field ordering (2002)

    Hi, I have a relationship between evaluations and responses, where each evaluation can have many responses. Each response has 5 questions. On creating a report summarising the responses, I have evaluation fields as the header, then in the detail the standard behaviour is getting the first response questions 1, 2, 3, 4, 5, next response's 1, 2, 3, 4, 5.

    In this instance I want to report by grouping all responses to question 1, then all responses to question 2 etc. Can this be done? If so is there anything special to be done with the query design?

    Regards, Roger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: report field ordering (2002)

    I have difficulty understanding what you mean by "Each response has 5 questions", but perhaps a crosstab query is what you need.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: report field ordering (2002)

    In the Report Design can you Group By question?
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report field ordering (2002)

    Sorry - I should have said: for each "response" record, there are 5 fields - "question 1", "question 2" etc. I want to get all entries for the field "question 1" together on the report, then entries for "question 2" etc. The normal report outcome is "question 1", "question 2" . . . "question 5", then for the next response "question 1", "question 2" . . . "question 5".

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report field ordering (2002)

    No I can't do that. The grouping options in report design are for the fields in the table on the 1 side of the 1 to many relationship. I am wanting to group by the fields on the "many" side.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: report field ordering (2002)

    Can you show us what your table deign is and what the report should look like.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: report field ordering (2002)

    Sorry I don't follow that.

    Grouping options lets you group by any field in the report's record source.

    Or do you have a Report and Subreport, with the main report looking at 1 side and the subreport the many?
    Regards
    John



  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report field ordering (2002)

    Table name: tblEvaluations
    Fields: EvaluationID (primary key, autonumber data type), ProgrammeName, EvaluationDate, NumberSent, Leader (this is a staff member's name), & Location.

    Table name: tblProgrammeResponses
    Fields: ProgrammeResponsesID (primary key), Q1Programme, Q2Programme, Q3Programme, Q4Programme, Q5Programme, & EvaluationID (foreign key, data type number)

    The relationship is: 1 record in tblEvaluations can have many records in tblProgrammeResponses, via the EvaluationID field.

    I want the report to have as as a header, fields from tblEvaluations. One record at a time. For each record, there will be some number of responses, in tblProgrammeResponses. Say, there are 8 records.

    Next on the report, data for the 8 responses to Q1Programme.
    Then data for the 8 responses from Q2Programme
    Then data for the 8 responses Q3Programme
    Then data for the 8 responses Q4Programme
    Then data for the 8 responses Q5Programme.

    I hope this is clearer?

    The query I have created as the record source for the report contains the 2 tables mentioned above, and all the fields from each table. The 1 to many relationship is automatically there. Is there a problem with this query maybe?

    This may not be the correct approach at all - perhaps I need to be using a report and a number of sub-reports?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: report field ordering (2002)

    I think the design of tblProgrammeResponses is unfortunate. It would be better to have a separate record for each question:

    ProgrammeResponsesID, EvaluationID, QuestionID, Response

    where QuestionID can be 1, 2, 3, 4 or 5. That way, you can sort and group the data any way you want.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: report field ordering (2002)

    As Hans has pointed out the table design is not quite right.

    You will probably have to design a Union query to overcome the limitations in the table design. But that is an awful way to go since if you increase the number of questions you will have to change the union query. That will not be a problem with Hans solution.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: report field ordering (2002)

    Because you don't have a QuestionNo field you can't group on that.

    With your current design you could do what you ask by having 5 separate subreports, one for each question.
    Regards
    John



  12. #12
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: report field ordering (2002)

    My thanks to everyone for their replies and patience. I'll look at the sub-report option as the first option and see if this is workable, otherwise come back and create a new post to restructure the tables.

    Cheers, Roger

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •