424,303 Members | 1,366 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,303 IT Pros & Developers. It's quick & easy.

Pronblem Counting occurrences regardless of field

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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 tblFiveSimilarFields;

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***@southwest.com.au> wrote in message
news:3f********@usenet.per.paradox.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

Nov 12 '05 #2

P: n/a
"Glenn Cornish" <gl***@southwest.com.au> wrote in message news:<3f********@usenet.per.paradox.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


Less than optimal design.

SELECT fieldA As X
FROM tableA
UNION ALL
SELECT fieldB As X
FROM tableA
....

then do your query on that.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.