SQL question...

[rjlohan]

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"
?

[rjlohan]

Come on someone... Beaker?

[rjlohan]

Ah bugger it - I got it...

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

[Beacon]

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

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

[rjlohan]

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?

[Beacon]

Bit

You want to perform a query on an array??

Or

you want to sue the array in the query?

b

[rjlohan]

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).

[Beacon]

1) How are you opening to get that information?

I.e cant you just use

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:

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

[rjlohan]

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.

[rjlohan]

Wanna help me out with this UNION query?

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...

[Beacon]

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

[rjlohan]

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

[Beacon]

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?