SQL question...

I have a (1 to many) table which can contain an arbitrary number of fields, all linked to one field in another table.

i.e - table [Answers] may have 5 answers for a single question in table [Questions] - you with me?

What would be appropropriate SQL to find the LARGEST number of answers to a question?

I'm thinking something like "SELECT MAX(COUNT(answerText)) WHERE questionNumber = 1"
?

Come on someone... Beaker?

Ah bugger it - I got it... :p

VB Code:

strSQLQuery = "SELECT MAX(AnswerCount) FROM " _
   & "(SELECT questionNumber, COUNT(questionNumber) AS " _ 
   & "AnswerCount FROM [Answers] GROUP BY questionNumber)"

Well i was typing a reply and saw you'd got it!!

Hey i it's only 9:30am here! :)

Lucky you... I 've been at work for nearly 4 hours.... :(
Then it's off to Uni at 1 for another 3 hours.... :(

OK, here's one then - same problem, but I only want to check a limited set of questionNumber which are in an array... any good ways to do it?

Bit :confused:

You want to perform a query on an array??

Or

you want to sue the array in the query?

b :)

The latter, I'll try and give you a quick overview. I have 2 tables - one for [answers], one for [questions]. [answers] may have 3 entries for question 1, 5 for question 2, 10 for question 3.

I need to know the highest number of answers - in this case it is 10. But say I am only asking question 1 & 2 - I only need the maximum from those 2 questions, which is 5.

So I need a query to tell me the maximum number of answers for a specific set of questions (1 & 2 in the example above above). The possible range is a list of numbers in an array.





A separate question also - how do I modify the working query I posted to check 2 different tables, with effectively the same structure. (One has additional fields, but these are not mentioned in the query).

1) How are you opening to get that information?

I.e cant you just use

VB Code:

strSQLQuery = "SELECT questionnumber FROM tablename"

Shouldnt that return all options anyway?
If you want a count you did it in the one u posted b4!

2) Check out UNION should help you!!

example:

VB Code:

Select name from table1 where name="rj" 
UNION 
select whatname from table2 where whatname="rj"

I can get a count OK, but if I want to only draw that count from a select portion of the records, what do I do...

shall look at UNION - probably what I want. :)

Wanna help me out with this UNION query?

VB Code:

strSQLQuery = "SELECT MAX(AnswerCount) FROM (" _
        & "SELECT MAX(AnswerCount) FROM " _
        & "(SELECT questionNumber, COUNT(questionNumber) AS AnswerCount " _
        & "FROM [Answers] GROUP BY questionNumber) " _
        & "UNION " _
        & "SELECT MAX(AnswerCount) FROM " _
        & "(SELECT questionNumber, COUNT(questionNumber) AS AnswerCount " _
        & "FROM [MultipleChoiceAnswers] GROUP BY questionNumber)" _
        & ")"

It's not working. I had it executing correctly at some point, but it was only returning the MAX from [Answers] not the max from both tables.

The above query won't execute though... :p

You only want a count of certain records selected by a sql statement!

What statement are you using now?

Umm rs.recordcount perhaps??

later
b :)

It's all good. I got it now.
The UNION part, that is...

Sorry as i was replying the computer stuffed up so i went and played table tennis against one of the directors and had a beer to calm me down.

What about the maximum number of multiple choice answers?

This is for your course yes?
;)