how to calculate the most recurrent value

[Gianluca]

Hi, i have to make a form with multiple sections.
Each section has n questions to which corresponds a numerical value from 1 to 4.
Example:

Question 1 value 2
Question 2 value 1
Question 3 value 3
Question 4 value 2
Question 5 value 4
Question 6 value 2

I have to choose the most recourrent number like the Excel MODA function then in this example is 2.
But if there are more then one recurrence with the same times i must choose the highest one.
Example because my mathematical english language is bad...!!!!

Question 1 value 2
Question 2 value 1
Question 3 value 3
Question 4 value 2
Question 5 value 4
Question 6 value 4

In the example above number 2 and 4 recur the same number of times and i must choose the number 4 because is the higher.

There is not a MODA funtion in Access, even if Excel, with the second example returns the most recurrent but the lower value 2.
Any ideas?

 

[Minty]

Use the Totals function on the query to count and sort the number of answers.
Then use a DMax on that query.

 

[Gianluca]

Can you explain me better.
For example i have created a table tblSection1 and each rows contains the section1 answer:

tblSection1
Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
1 2 0 2 3 0 3 0

The result must be: 3. Because the most recurrets numbers are 2 and 3 but the higher between 2 and 3 is 3.

I would not necessarily solve the problem using only SQL, maybe it's easier to use Access VB.
So, before update maybe i can use VB to calculate the value and store it in the table during the before_update event.
Thanks

 

[Minty]

Okay- your table design is going to make this very awkward, and if you ever add a question or section you will have to rewrite everything. You should have your table layout something like follows; (This is very of the top of my head as I normally draw these things out first but can't at the moment!)

tblQuestions
Fields
QuestionID
SectionNo
QuesText

tblAnswers
Fields
QuestionID
Answer
AnswerDate

Then you can easily query the results and filter them by date / section etc. tbh you could even split the section and question into another table , but I suspect that is overkill for your application.

 

[jdraw]

Here are some samples. Based on your post, I created a table with fields
Section, Question and Answer and added some data values. There is a query to find which answers occur most frequently by Section ( I think that is your underlying question[guess]).

The data and result are shown in the jpg.
The query sql is

SELECT tblCSect_ion.section
	,tblCSect_ion.Answer AS AnswerValue
	,Count(tblCSect_ion.Question) AS OccurrenceFrequency
FROM tblCSect_ion
GROUP BY tblCSect_ion.section
	,tblCSect_ion.Answer
	,tblCSect_ion.Section
ORDER BY tblCSect_ion.Section
	,Count(tblCSect_ion.Question) DESC;

Good luck

 

[Gianluca]

work in progress....
Tomorrow I'll post the example of the db that I am creating