473,322 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Pronblem Counting occurrences regardless of field

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
2 3104
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: francescomoi | last post by:
Hi. I'm using 'strpos', but wonder whether this function exists: find_string($haystack, $needle) which returns an array with all the numeric positions of the occurrences of needle in the...
8
by: DrNoose | last post by:
Hi! I'm writing a program that is supposed to read a line of input, count the words and the number of occurrences of each letter. Then it should prints the number of occurrences of each letter...
8
by: Peter Bungart | last post by:
I have a text field in Access (XP) that lists the occurrences of various plants, which are delimited within the field by commas (e.g., cactus, mesquite, yucca, cliffrose). Is there a way to...
5
by: chrisc | last post by:
Hello, Hope this is the right place for this... I am creating a testing database for components as they come off a production line. My reports need to select faults that are found, as well...
4
by: Dado | last post by:
I have a next situation with the textbox field: A - B = C 1. How to fill the A fill with the data from my previous recordset ? Can I do it with the expression builder ? 2. I want that every...
11
by: b_or_not2c | last post by:
Hi, I am trying to count the number of substrings within a string. I am fairly new to programming and need a headstart. Any info would be appreciated. Thanks, A
3
by: newstips6706 | last post by:
The Art of Counting TIME -------------------------------------------------------------------------------- As the research done on the paper titled: "The Number '1'", which yielded the...
1
by: BillH | last post by:
As new Records are added I need to in a Query, count the number of occurrences of several specific Fields that have change from the previous record. As a reference point, each Record is unique for...
25
by: Rick Collard | last post by:
Using DAO 3.6 on an Access 2002 database, I'm getting unexpected results with the FindFirst method. Here's the simple code to test: Public Sub FindIt() Dim db As Database, rs As Recordset...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.