Bob - if the possible answers are consistent and relatively few in number, I'd probably have a Boolean calc field for each possible answer, e.g. answerIsYes, answerIsNo, answerIsMaybe - this would make totalling the responses across a found set very straightforward. It rather depends on how the answers are held though.
Thanks, Dave. I think that might be the way to go. I'll create a summary field for each of the three answers and use the results to create a chart.
If the answer information is not updated every minute of the day, I'd lean to making a summary table of the information.
I'm a big fan of summarizing data and allowing the summary to be static.
I'd set up a new table with the fields: Question #, Question, AnswerYesCount, AnswerNoCount, AnswerMaybeCount.
Then my script would go to this table, create a new record, put a question in those fields, then count all the Yes and put that sum in the Yes field, then count all the Nos for that question and put that in the No field. Do the same with the Maybes. I'd loop through all the questions that way.
ExecuteSQL is a great way to get the data you need for the script and record creation.
This procedure allows all data to be static. Graphs and other reports can access these numbers in this table rather than an unstored calc or a summary field. If the database is hosted and you're accessing it via WAN, reports and graphs may render faster because there's no calculations that have to be done.
I'd also plan to re run the script every time new information is put into the questions table.This time, Id update the script to first look for the question that I'm updating. If a question is already there, simply recount the Yes, No, Maybe answers. If the question is not already in the summary table, create a new record, stuff the question in, and then count.
If you need the values, execute SQL would be an excellent way to go rather than creating summary or other calculation fields.
-- sent from myPhone --