Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Query to calculate percentage results in a survey

Author  Topic  pajmage Starting Member 1 Post Posted - 2010-10-19 : 06:33:29 Hello all. I'm having asome major issues writing a piece of database software fr the school I work at. I'm wondering if anyone here can help me. Using Access 2003 I have managed to put the results of a survey on the way lessons are taught in a school into a table in the database. The table structure is set up like: Pupil ID, Surname, Forename, Point ICT, Point Art, Choice ICT, Choice Art... There are 10 columns for each subject and 15 subjects so that gives a total of 153 columns in the table and an example record, using the structure identified above would be: Pupil ID, Surname, Forename, Point ICT, Point Art, Choice ICT, Choice Art 207115, Bloggs, Joe, A, D, A, A Now, What I need to do is create a Query that will total up all the Agrees (A) and Disagrees (D) in each column and work out a the percentage of pupils that agree in each column. The idea being that I can then create a report for each department/teacher Showing the results of the survey in Graph form. The problem I am having is creating a query that will calculate these percentages. I have managed to find some information from different websites and have come up with an SQL statement of: SELECT KS3.[Choice ICT], Count(*) AS [Results Choice ICT] FROM [Pupil Data] INNER JOIN KS3 ON [Pupil Data].Surname = KS3.Surname GROUP BY KS3.[Choice ICT], [Pupil Data].[ICT Teacher], KS3.[Year group] HAVING ((([Pupil Data].[ICT Teacher]) Like "* jones") AND ((KS3.[Year group]) Like "*" & [Enter Year Group] & "*")); This calculates the total number of 'Agrees' and 'Disagrees' for the Choice ICT column, but I cannot for the life of me figure out how to expand this statement in order to do the same thing for all 10 of the columns that deal with ICT, nor can I figure out how to then, using the numbers calculated, work out a percentage total of 'Agrees' Any and all help would be greatly appreciated. - Paul