Query advice required

[dazstarr]

Do I need a crosstab query?

Hi All

Im after some help with a query im building.

I have a table called QuizResults which has 10 Yes/No fields. The table also has a field called Site.

Each site will take a quiz and the problem I am having is with the statistics side of things.

What I need is a query that will work out the percentage correct for each site. All the data is stored in this 1 table. There are no relational fields as they are not required. I have tried a few different ways but each method I use involves me making many many queries to work this out.

To summarise I need 10 percentage correct fields, broken down by site.

Please can anyone help?
Many Thanks
Dazstarr

 

[dcobau]

a) count total records

eg. a:count(*)

b) count required records

eg. b:dcount ("[FieldName]","tblName", "[FieldName] = "Criteria")

c: calculate percentage of b over a.

repeat for each field

Dave

 

[dazstarr]

Query Help

Many Thanks for your help!!

I have never tried something like this before....

Do I need to do this in 2 separate queries or can i calculate this in one field?

Many Thanks for your response.

Daz

 

[phatnq2002]

Suppose the Yes/No fields are named like f1, f2, f3, ..., f10

SELECT Site, (IIf(f1, 1, 0) + IIf(f2, 1, 0) + IIf(f3, 1, 0) + IIf(f4, 1, 0) + IIf(f5, 1, 0) + IIf(f6, 1, 0) + IIf(f7, 1, 0) + IIf(f8, 1, 0) + IIf(f9, 1, 0) + IIf(f10, 1, 0))/10 As CorrectPercent FROM QuizResults

 

[dazstarr]

Query Help

Thanks for your post...

But it is not giving me the info i require. The results need to be broken down by each question and each site. So the rows will be each site and 10 columns for the 10 questions with a percentage in each field broken down by site.

I have been assured this is possible but have no idea how to do it : (

Please help!

Daz

 

[dcobau]

hi Daz,

to follow my suggestion you need to have what I suggested in 1 query for the 10 fields you have, so it will be something like this:

TotalCount:count (*)
TempField1:dcount ("[FieldName1]","tblName", "[FieldName1] = "Criteria") PercTempField1: ([TempField1]*[TotalCount]/100)
TempField2:dcount ("[FieldName2]","tblName", "[FieldName2] = "Criteria") PercTempField2: ([TempField2]*[TotalCount]/100)
etc

try it for 1 field first then, if it works, do it for all the fields you require.

Dave