Results 1 to 6 of 6

Thread: Survey (A2K)

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Survey (A2K)

    OK
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Survey (A2K)

    Start by creating a query based on tResponse. Add the field representing the person (fkUserID? or fkDemoID?) and the nSurveyRecord field.
    Select View | Totals.
    Set the Total option for nSurveyRecord to Max.
    Enter >1 in the Criteria line under nSurveyRecord.
    Save this query as (say) qryLastSurvey.
    This query returns the last survey record number for all persons who have at least two of them (if there is only one, there is nothing to compare).

    Create a new query, and add tResponse, qryLastSurvey and tResponse (yes, a second time).
    Join both instances of tResponse to qryLastSurvey on the field representing rhe person.
    Add nSurveyRecord from the first tResponse to the query grid.
    Set the criteria to [MaxOfnSurveyRecord]-1.
    Add nSurveyRecord from the second tResponse to the query grid.
    Set the criteria to [MaxOfnSurveyRecord].
    You can clear the Show check box for both these columns.
    Add the field representing the person to the query grid, as well as the pkResponseID field from both tResponse tables.
    Save this query as (say) qryResponseIDs.
    This query will return the pkResponseID of the next to last and last records for each person.

    The next query will involve this query and two instances of t_Rand_Responses, but try to get the others done first, and check whether they work as expected.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Survey (A2K)

    Thanks Hans,

    I'm working on it and will let you know how it turned out once I'm complete.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Survey (A2K)

    Hans,

    Brilliant ... Brilliant... Brilliant....NEVER IN A MILLION YEARS...NEVER....WOULD I HAVE COME UP WITH THAT SOLUTION!!!!!

    You said, "The next query will involve this query and two instances of t_RAND_Responses....", everything is working as suspected. The first query, q_LastSurvey, returns the most recent surveys and the second query, q_ResponsIDs, produces the LAST Survey and the one prior to it with their respective responses.

    So, now I'm ready for the next step...and so eager to be enlightened even more.....
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Survey (A2K)

    Ok, glad it works so far.

    Create a new query in design view.
    Add t_RAND_Responses, q_ResponseIDs and another instance of t_RAND_Responses.
    Join the first instance of t_RAND_Responses to q_ResponseIDs on fkResponseID vs the first pkResponseID field in q_ResponseIDs.
    Join the second instance of t_RAND_Responses to q_ResponseIDs on fkResponseID vs the second pkResponseID field in q_ResponseIDs.
    Join the two t_RAND_Responses tables on fkQuestionID (not on fkResponseID!).
    Add the field identifying the person from q_ResponseIDs to the query grid.
    Add fkQuestionID and Response from the first t_RAND_Responses table to the query grid.
    Add fkQuestionID from the second t_RAND_Responses table to the query grid.
    Save this query as (say) q_CompareResponses.

    This query should return what you need for the report. You'd group the report by person, and sort it on fkQuestionID within a person.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Survey (A2K)

    Words just don't express my gratitude for all your help on this....and so many other projects. You make it seem so effortless.

    Thank you so very much.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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