DCount Across Fields and Unique Records

[Caryisms]

I need to build a report that will dcount values in fields grouped by a unique id. How many 1's, 2's, 3's, 4's, and 5's are present in questions 6-18 for records grouped by a course name. The report has to state the question and the totals for 1, 2, 3, 4, and 5. Please let me know if this is do-able. Thank you.

 

[jrbarnett]

Hi,

Yes - it is possible, but the exact answer will depend on the structure of your database. Thinking ahead, your next question will be something like "How?"

If your database is fully normalised, the SQL will be something like:

SELECT Courses.CourseName, Questions.Response, Count (*) As Total
From Courses Inner Join Questions On Courses.CourseID = Questions.QuestionID (Inner Join Answers On Questions.CourseID = Answers.CourseID)
Where Questions.Number >= 6 And Questions.Number <= 18
Group By Courses.CourseName, Questions.Response

John

 

[Caryisms]

Hi John,

The data is all in one table. The field names are Course Name, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Q15, Q16, Q17, and Q18. Obviously, the records consist of different classes with responses between 1 and 5 for questions 6-18. Please let me know what I can do. Thank you.

 

[jrbarnett]

Hi,

Looking at your previous post, your data is not normalised (this is the process of removing repeated data and calculated items stored within the database). The structure of a fully normalised table in your case would be:

Course, Question, Number

and the data would read something like
&quot;Course1&quot;, &quot;Q6&quot;, 3
&quot;Course1&quot;, &quot;Q8&quot;, 4
&quot;Course2&quot;, &quot;Q9&quot;, 1

etc

and then it would be simply a matter of using a crosstab query on this and following the wizard.
However, this isn't possible with the current structure and my workaround is very fiddly as you can see, and certainly not recommended for a long term solution:

SELECT tblCourses.[Course Name], 
(IIf([Q6]="1",1,0)+IIf([Q7]="1",1,0)+IIf([Q8]="1",1,0)+IIf([Q9]="1",1,0)+IIf([Q10]="1",1,0)+IIf([Q11]="1",1,0)+IIf([Q12]="1",1,0)+IIf([Q13]="1",1,0)+IIf([Q14]="1",1,0)+IIf([Q15]="1",1,0)+IIf([Q16]="1",1,0)+IIf([Q17]="1",1,0)+IIf([Q18]="1",1,0)) AS Total1s
(IIf([Q6]="2",1,0)+IIf([Q7]="2",1,0)+IIf([Q8]="2",1,0)+IIf([Q9]="2",1,0)+IIf([Q10]="2",1,0)+IIf([Q11]="2",1,0)+IIf([Q12]="2",1,0)+IIf([Q13]="2",1,0)+IIf([Q14]="2",1,0)+IIf([Q15]="2",1,0)+IIf([Q16]="2",1,0)+IIf([Q17]="2",1,0)+IIf([Q18]="2",1,0)) AS Total2s
(IIf([Q6]="3",1,0)+IIf([Q7]="3",1,0)+IIf([Q8]="3",1,0)+IIf([Q9]="3",1,0)+IIf([Q10]="3",1,0)+IIf([Q11]="3",1,0)+IIf([Q12]="3",1,0)+IIf([Q13]="3",1,0)+IIf([Q14]="3",1,0)+IIf([Q15]="3",1,0)+IIf([Q16]="3",1,0)+IIf([Q17]="3",1,0)+IIf([Q18]="3",1,0)) AS Total3s
(IIf([Q6]="4",1,0)+IIf([Q7]="2",1,0)+IIf([Q8]="4",1,0)+IIf([Q9]="4",1,0)+IIf([Q10]="4",1,0)+IIf([Q11]="4",1,0)+IIf([Q12]="4",1,0)+IIf([Q13]="4",1,0)+IIf([Q14]="4",1,0)+IIf([Q15]="4",1,0)+IIf([Q16]="4",1,0)+IIf([Q17]="4",1,0)+IIf([Q18]="4",1,0)) AS Total4s
(IIf([Q6]="5",1,0)+IIf([Q7]="5",1,0)+IIf([Q8]="5",1,0)+IIf([Q9]="5",1,0)+IIf([Q10]="5",1,0)+IIf([Q11]="5",1,0)+IIf([Q12]="5",1,0)+IIf([Q13]="5",1,0)+IIf([Q14]="5",1,0)+IIf([Q15]="5",1,0)+IIf([Q16]="5",1,0)+IIf([Q17]="5",1,0)+IIf([Q18]="5",1,0)) AS Total5s
FROM tblCourses;
ORDER BY tblCourses.[Course Name];

This query assumes that your question fields store data as text. If they are numeric, you need to remove the double quote marks around the field names, so for example:

IIf([Q6]=&quot;1&quot;,1,0)

becomes

IIf([Q6]=1,1,0)


You will need to change &quot;tblCourses&quot; to the name of the table which stores the course data if this is not its name.

When you run this query, you will get a list of courses and totals of 1 to 5's next to it. It may take some time to run if you have a lot of data, but this unfortunately is the penalty for non normalised databases (they are often a lot slower to execute queries).

To use the query, go to the queries tab, choose new from query design, then click the SQL button in the toolbar and copy and paste the code, overwriting anything that is there.
To run it press the red caution mark button.

John

 

[Caryisms]

John,

Would you please explain the best way to normalize the table/relationship structure. I would like to make this process as easy as possible. What is the impact to the input form? Thank you.

 

[jrbarnett]

Hi,

Go to

http://www.abcdataworks.com

and look in the programmers section, then download the Fumentals of Relational database design document and read it.
It covers normalisation and proper relational database design.
The web site itself is run by Tek-Tipper JeremyNYC.

John

 

[dhookom]

There is a complete survey application &quot;At Your Survey&quot; available for download at

http://www.rogersaccesslibrary.com/OtherLibraries.asp.

AYS can handle most basic surveys without changing a table, field, form, report, code, query,... It is ready to run for lots of surveys and show fairly good normalization.

Duane
MS Access MVP