By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Count fields help

P: n/a
Hi All,

Can anyone help me with a problem that has been driving me mad.
I have a table with 10 fields in it for staff to enter their initials
to register a vote. I need to count the fields that contain text to
tally the votes. At the moment I have a query that counts the votes
for each record by using
Abs(Not(IsNull([tblvotes]![strvote1])))+Abs(Not(IsNull([tblvotes]![strvote2])))...............Abs(Not(IsNull([tblvotes]![strvote10])))
While this works I am sure there is a more elegant way of doing this.

Thanks in advance

Phil

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(Phil Kershaw) wrote in news:41*************@news.eclipse.co.uk:
Hi All,

Can anyone help me with a problem that has been driving me
mad. I have a table with 10 fields in it for staff to enter
their initials to register a vote. I need to count the fields
that contain text to tally the votes. At the moment I have a
query that counts the votes for each record by using
Abs(Not(IsNull([tblvotes]![strvote1])))+Abs(Not(IsNull([tblvote
s]![strvote2])))...............Abs(Not(IsNull([tblvotes]![strvo
te10]))) While this works I am sure there is a more elegant
way of doing this.

Thanks in advance

Phil

A far better way of building your database structure would be to
take the 10 fields out of your existing table, and put 1 vote per
record in a child table with many vote records for the one vote
question record.
Then you have each staff member's vote on a separate line.

The structure would look lit this (sorta)

Question
QuestionID.QuestionText,DateAsked.......
0001 Am I ugly 04.10.01
0002 Am I stupid 04.10.02

Votes
QuestionID VoterInitials,Iagree
0001 aaa NO
0001 RPQ NO
0001 MDQ YES
0002 MDQ YES
0002 aaa Yes

then your counting query becomes
SELECT count(iagree) Where questionID= 0002 and Iagree = Yes

Doing it this way means you don;t have to change anything as your
business grows. If all the votes are in the one record, and you
hire an eleventh person, you have to modify the structure, the
query, the forms, etc. Lotsa work for nothing.
--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.