DISTINCT in a crosstab query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've created a crosstab query to summarize the results of a poll. The user
wants to know the total number of respondants. The method the poll software
uses to identify a unique respondant is "respondentID". I have 11,000 records
in the results table, but only 1000 (or so) respondents (so each respondent
has about 11 questions "attached" to their ID-value). I've created a summary
report that feeds off the crosstab query.

Here's the issue:

How do I add the "respondentID" field to the CTQ? When I add it aws a "group
by", I get 11,000 recs in the quesry results (not good). When I try to
"count", the column completely disappears. I can't use DISTINCT because I
have to use it in the SELECT statement, which wouldn't give me what I need
either (as you can probably guess).

So, I'm casting about here for an alternative way to count the DISTINCT
"respondentID"s and provide that total on the report.

Any ideas would be greatly appreciated!

Thanks
 
It would be impossible to adequately describe the structure in this venue. If
I could ZIP the database and email it to you, that would save a bunch of
time. Please let me know if that would be allowed.

Thanks

Dennis
 
We don't need to know every detail regarding your database. Can't you reply
back with the relevant structure and sample records?
 
Okay, I've copied 3 records into this post. Note that the respondentID
numbers would be duplicated across all questions that respondent answered. So
if there are 10 questions, respondent 12345 would have 10 entries in this
table. Each one would have a different QID (question ID), but the
respondentID would be the same. In the examples below, the QIDs are 600071
and 600072, and the respondent IDs are 80763, 80765 and 75725. (Again, note
that there would be multiple records with the same respondentID. My goal in
the CTQ is to COUNT the number of UNIQUES respondentIDs in the table, so as
to give the corrent number of respondents at the bottom of the final report.)

------------------------
Thank you in advance for your participation. ",6,600071,"What is the
strength of this home / program in your opinion?",0,"",,0,"The strength is
the experience offered by staff who have worked in the DD system for a long
time, many having come from the old state hospital systems.",,,,,"Turtle
Creek"
10,64291,"MSOCS Satisfaction Survey CM",80763,1,516593,"Introduction","It’s
survey time again! This survey should take a few minutes of your time.

Thank you in advance for your participation. ",6,600071,"What is the
strength of this home / program in your opinion?",0,"",,0,"The strength is
the experience offered by staff who have worked in the DD system for a long
time, many having come from the old state hospital systems.",,,,,"Turtle
Creek"
10,64291,"MSOCS Satisfaction Survey CM",80765,1,516593,"Introduction","It’s
survey time again! This survey should take a few minutes of your time.

Thank you in advance for your participation. ",7,600072,"If you could change
one thing about this home / program, what would it be?",0,"",,0,"Sometimes I
think there are attitudes left-over from the old state hospital system -
maybe staff think they know better than others because they are set in their
opinions from having been around a long time. It can be hard to change these
attitudes.",,,,,"Turtle Creek"
20,64291,"MSOCS Satisfaction Survey CM",75725,1,516593,"Introduction","It’s
survey time again! This survey should take a few minutes of your time.
 
If you have a table where RespondentID is the primary key, just add a text
box in the report footer:
=DCount("RespondentID","tblRespondents")
If you don't have a table like this, then create a similar query that groups
by RespondentID and use
=DCount("RespondentID","qgrpRespondents")

If your report is grouped by respondent then you can create a text box in
the RespondentID header
Name: txtRspIDRS
Control Source: =1
Running Sum: Over All
Visible: No

Then in the report footer, add a text box:
Control Source: =txtRspIDRS
 
Back
Top