I am tempted to say "proper table design", because it is almost certain that
an arrangment as you describe violates relational database design principles
and that is why you are having trouble. If those fields were records in a
related subtable with some identification to distinguish between them, it
would be the proverbial "duck soup" to count them.
Short of that you can create a Query with one calculated field (in this case
to count the times that "5" was entered in any of the fields).
NoWFive:
IIf([Field1]=5,1,0)+IIf([Field2]=5,1,0)+IIf([Field3]=5,1,0)+IIf([Field4]=5,1
,0)+IIf([Field5]=5,1,0)
That will count the occurrences of "5" in each record. Then in Query Design,
on the menu, View | Totals, and change the "Group By" that appears
automatically under "NoWFive" to "Sum". That will sum the counts and give
you the total. Sounds complicated, perhaps, but it won't be.
Take a look at the SQL for the Totals Query:
SELECT
Sum(IIf([Field1]=5,1,0)+IIf([Field2]=5,1,0)+IIf([Field3]=5,1,0)+IIf([Field4]
=5,1,0)+IIf([Field5]=5,1,0)) AS NoWFive
FROM tblFiveSimilarF ields;
If you put that into a variable, with "X" replacing all fives, then it is
easy to write code to replace the "X" with whatever number you want to
count, and execute the Query from code.
But if the fields, with an additional identification field, were kept in a
related table, it would be just one, simple totals query, counting the id
field, WHERE the value equals the number you want to search for.
Larry Linson
Microsoft Access MVP
"Glenn Cornish" <gl***@southwes t.com.au> wrote in message
news:3f******** @usenet.per.par adox.net.au...
I have 5 fields in a table into which numbers between 1 and 45 can be
entered. What I am having trouble with is being able to find out is how
many times a particular number appears, regardless of which of the 5
fields it was entered into. Can anyone suggest a remedy for this.
Many Thanks
Glenn
