How to

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My db has 6 fields all populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of the numbers
(say number 8 comes up) in all of the fields?
 
It sounds like you can normalize your data first with a union query
==quniNumbers=======
SELECT Field1 as TheNumber, 1 As FieldNum
FROM tblNoNameGiven
UNION ALL
SELECT Field2, 2
FROM tblNoNameGiven
UNION ALL
SELECT Field3, 3
FROM tblNoNameGiven
UNION ALL
SELECT Field4, 4
FROM tblNoNameGiven
UNION ALL
SELECT Field5, 5
FROM tblNoNameGiven
UNION ALL
SELECT Field6, 6
FROM tblNoNameGiven;

Then create a totals query from quniNumbers:
SELECT TheNumber, Count(TheNumber) As CountOf
FROM quniNumbers
GROUP BY TheNumber;
 
My db has 6 fields all populated with numbers from 01-55 and there are 1404
records. How can I write a query to tell me how many time each of the numbers
(say number 8 comes up) in all of the fields?
Click to expand...

It sounds like your table structure may not be properly normalized...

A UNION query will help here, if you're stuck with this table
structure: first create a query in the SQL window like

SELECT Field1 As MyNum FROM MyTable WHERE Field1 IS NOT NULL
UNION ALL
SELECT Field2 FROM MyTable WHERE Field2 IS NOT NULL
UNION ALL
<etc for all six fields>

Then base a Totals query on this saved UNION query; add the MyNum
field to the query twice, once for the Group By and the second for the
Count.

John W. Vinson[MVP]
 
Is this not corect?

SELECT qryMyNum.MyNum, Count(Count(MyNum)) AS [CountOfCount(MyNum)]
FROM qryMyNum
GROUP BY qryMyNum.MyNum;

The Union IS below



UNION CLAUSE
SELECT pb.[pb1] As MyNum

FROM pb
WHERE pb.[pb1] IS NOT NULL

UNION ALL

SELECT pb.[pb2]

FROM pb

WHERE pb.[pb 2] IS NOT NULL


UNION ALL

SELECT pb.[pb3]

FROM pb
WHERE pb.[pb3] IS NOT NULL


UNION ALL

SELECT pb.[pb4]

FROM pb
WHERE pb.[pb4] IS NOT NULL


UNION ALL

SELECT pb.[pb5]

FROM pb
WHERE pb.[pb 5] IS NOT NULL


UNION ALL SELECT pb.[pb6]

FROM pb
WHERE pb.[pb 6] IS NOT NULL;
 
Is this not corect?

SELECT qryMyNum.MyNum, Count(Count(MyNum)) AS [CountOfCount(MyNum)]
FROM qryMyNum
GROUP BY qryMyNum.MyNum;

The Union IS below
Click to expand...

If the Union query is saved as qryMyNum then yes, this should work.
Doesn't it?

John W. Vinson[MVP]
 
Nope...

John Vinson said:
Is this not corect?

SELECT qryMyNum.MyNum, Count(Count(MyNum)) AS [CountOfCount(MyNum)]
FROM qryMyNum
GROUP BY qryMyNum.MyNum;

The Union IS below
Click to expand...

If the Union query is saved as qryMyNum then yes, this should work.
Doesn't it?

John W. Vinson[MVP]
Click to expand...
 
I'm not sure how you got the extra Count(MyNum) in there but try:

SELECT MyNum, Count(MyNum) AS CountOfMyNum
FROM qryMyNum
GROUP BY MyNum;

--
Duane Hookom
MS Access MVP


tamxwell said:
Nope...

John Vinson said:
Is this not corect?

SELECT qryMyNum.MyNum, Count(Count(MyNum)) AS [CountOfCount(MyNum)]
FROM qryMyNum
GROUP BY qryMyNum.MyNum;

The Union IS below
Click to expand...

If the Union query is saved as qryMyNum then yes, this should work.
Doesn't it?

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Nope...
Click to expand...

"Doctor, I don't feel good. What should I take?"

Please describe in what WAY it doesn't work. You can see your
screen... we cannot.

John W. Vinson[MVP]
 
It does not return any results. No error messages, just no results. When I
click on the query it asks for Parameter Value pb.pb1, then pb.pb2,
etc...but no results.
 
Does the union query by itself work? I did notice your field names in the
union query are incorrect. Sometimes you have spaces, other times you don't.

SELECT pb.[pb2]
FROM pb
WHERE pb.[pb 2] IS NOT NULL
 
No does not work. I did notice the spaces and corrected . It just keeps
prompting mr for the parameter value of pb.pb1, pb.pb2, etc. I also wrote
another UNION a little different. Still prompts for Parameter Value.



SELECT pb.[pb1] As MyNum

FROM pb
WHERE pb.[pb1] IS NOT NULL

UNION ALL

SELECT pb.[pb2]

FROM pb

WHERE pb.[pb2] IS NOT NULL


UNION ALL

SELECT pb.[pb3]

FROM pb
WHERE pb.[pb3] IS NOT NULL


UNION ALL

SELECT pb.[pb4]

FROM pb
WHERE pb.[pb4] IS NOT NULL


UNION ALL

SELECT pb.[pb5]

FROM pb
WHERE pb.[pb5] IS NOT NULL


UNION ALL SELECT pb.[pb6]

FROM pb
WHERE pb.[pb6] IS NOT NULL;

Duane Hookom said:
Does the union query by itself work? I did notice your field names in the
union query are incorrect. Sometimes you have spaces, other times you don't.

SELECT pb.[pb2]
FROM pb
WHERE pb.[pb 2] IS NOT NULL

--
Duane Hookom
MS Access MVP
--

tamxwell said:
It does not return any results. No error messages, just no results. When I
click on the query it asks for Parameter Value pb.pb1, then pb.pb2,
etc...but no results.
Click to expand...
Click to expand...
 
Do you have a table named pb with fields pb1, pb2,...

Have you considered providing us with the actual table structure?

--
Duane Hookom
MS Access MVP
--

tamxwell said:
No does not work. I did notice the spaces and corrected . It just keeps
prompting mr for the parameter value of pb.pb1, pb.pb2, etc. I also wrote
another UNION a little different. Still prompts for Parameter Value.



SELECT pb.[pb1] As MyNum

FROM pb
WHERE pb.[pb1] IS NOT NULL

UNION ALL

SELECT pb.[pb2]

FROM pb

WHERE pb.[pb2] IS NOT NULL


UNION ALL

SELECT pb.[pb3]

FROM pb
WHERE pb.[pb3] IS NOT NULL


UNION ALL

SELECT pb.[pb4]

FROM pb
WHERE pb.[pb4] IS NOT NULL


UNION ALL

SELECT pb.[pb5]

FROM pb
WHERE pb.[pb5] IS NOT NULL


UNION ALL SELECT pb.[pb6]

FROM pb
WHERE pb.[pb6] IS NOT NULL;

Duane Hookom said:
Does the union query by itself work? I did notice your field names in the
union query are incorrect. Sometimes you have spaces, other times you
don't.

SELECT pb.[pb2]
FROM pb
WHERE pb.[pb 2] IS NOT NULL

--
Duane Hookom
MS Access MVP
--

tamxwell said:
It does not return any results. No error messages, just no results.
When I
click on the query it asks for Parameter Value pb.pb1, then pb.pb2,
etc...but no results.

:

On Tue, 4 Oct 2005 05:57:03 -0700, "tamxwell"

Nope...


If the Union query is saved as qryMyNum then yes, this should work.
Doesn't it?

"Doctor, I don't feel good. What should I take?"

Please describe in what WAY it doesn't work. You can see your
screen... we cannot.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
The Table, named PB and the structure is Field 1 is ID, Field 2 is Date of,
Field 3 is pb#1, Field 4 is pb#2, Field 5 is pb#3, Field 6 is pb#4, Field 7
is pb#5, Field 8 is pb#6.



Duane Hookom said:
Do you have a table named pb with fields pb1, pb2,...

Have you considered providing us with the actual table structure?

--
Duane Hookom
MS Access MVP
--

tamxwell said:
No does not work. I did notice the spaces and corrected . It just keeps
prompting mr for the parameter value of pb.pb1, pb.pb2, etc. I also wrote
another UNION a little different. Still prompts for Parameter Value.



SELECT pb.[pb1] As MyNum

FROM pb
WHERE pb.[pb1] IS NOT NULL

UNION ALL

SELECT pb.[pb2]

FROM pb

WHERE pb.[pb2] IS NOT NULL


UNION ALL

SELECT pb.[pb3]

FROM pb
WHERE pb.[pb3] IS NOT NULL


UNION ALL

SELECT pb.[pb4]

FROM pb
WHERE pb.[pb4] IS NOT NULL


UNION ALL

SELECT pb.[pb5]

FROM pb
WHERE pb.[pb5] IS NOT NULL


UNION ALL SELECT pb.[pb6]

FROM pb
WHERE pb.[pb6] IS NOT NULL;

Duane Hookom said:
Does the union query by itself work? I did notice your field names in the
union query are incorrect. Sometimes you have spaces, other times you
don't.

SELECT pb.[pb2]
FROM pb
WHERE pb.[pb 2] IS NOT NULL

--
Duane Hookom
MS Access MVP
--

It does not return any results. No error messages, just no results.
When I
click on the query it asks for Parameter Value pb.pb1, then pb.pb2,
etc...but no results.

:

On Tue, 4 Oct 2005 05:57:03 -0700, "tamxwell"

Nope...


If the Union query is saved as qryMyNum then yes, this should work.
Doesn't it?

"Doctor, I don't feel good. What should I take?"

Please describe in what WAY it doesn't work. You can see your
screen... we cannot.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Create 6 queries like the following:
select count(*) as [Count1] from PB where [field 1] is not null and [field
1] = 8
select count(*) as [Count2] from PB where [field 2] is not null and [field
2] = 8
..
..
..
..
then do the following
SELECT count1 + count2 + count3 + count4 + count5 + count6 as [Total 8s]
from qry1, qry2, qry3, qry4, qry5, qry6

This provides a easy way to find errors.
 
You actually have "#" in your field names but when you created your union
query, you ignored the "#"?

You should be able to figure out that the select statements are based on
your table and field names, not something you make up.

--
Duane Hookom
MS Access MVP


tamxwell said:
The Table, named PB and the structure is Field 1 is ID, Field 2 is Date
of,
Field 3 is pb#1, Field 4 is pb#2, Field 5 is pb#3, Field 6 is pb#4, Field
7
is pb#5, Field 8 is pb#6.



Duane Hookom said:
Do you have a table named pb with fields pb1, pb2,...

Have you considered providing us with the actual table structure?

--
Duane Hookom
MS Access MVP
--

tamxwell said:
No does not work. I did notice the spaces and corrected . It just keeps
prompting mr for the parameter value of pb.pb1, pb.pb2, etc. I also
wrote
another UNION a little different. Still prompts for Parameter Value.



SELECT pb.[pb1] As MyNum

FROM pb
WHERE pb.[pb1] IS NOT NULL

UNION ALL

SELECT pb.[pb2]

FROM pb

WHERE pb.[pb2] IS NOT NULL


UNION ALL

SELECT pb.[pb3]

FROM pb
WHERE pb.[pb3] IS NOT NULL


UNION ALL

SELECT pb.[pb4]

FROM pb
WHERE pb.[pb4] IS NOT NULL


UNION ALL

SELECT pb.[pb5]

FROM pb
WHERE pb.[pb5] IS NOT NULL


UNION ALL SELECT pb.[pb6]

FROM pb
WHERE pb.[pb6] IS NOT NULL;

:

Does the union query by itself work? I did notice your field names in
the
union query are incorrect. Sometimes you have spaces, other times you
don't.

SELECT pb.[pb2]
FROM pb
WHERE pb.[pb 2] IS NOT NULL

--
Duane Hookom
MS Access MVP
--

It does not return any results. No error messages, just no results.
When I
click on the query it asks for Parameter Value pb.pb1, then pb.pb2,
etc...but no results.

:

On Tue, 4 Oct 2005 05:57:03 -0700, "tamxwell"

Nope...


If the Union query is saved as qryMyNum then yes, this should
work.
Doesn't it?

"Doctor, I don't feel good. What should I take?"

Please describe in what WAY it doesn't work. You can see your
screen... we cannot.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Hey,
Sorry, you are right I should have looked more closely. I changed it still
did not work. I did not mean to waste you time. This db is for a person here
in the office that did not record this info when it first came in, and I have
5 other db that actually are not a waste of time . Again, I am sorry.

Duane Hookom said:
You actually have "#" in your field names but when you created your union
query, you ignored the "#"?

You should be able to figure out that the select statements are based on
your table and field names, not something you make up.

--
Duane Hookom
MS Access MVP


tamxwell said:
The Table, named PB and the structure is Field 1 is ID, Field 2 is Date
of,
Field 3 is pb#1, Field 4 is pb#2, Field 5 is pb#3, Field 6 is pb#4, Field
7
is pb#5, Field 8 is pb#6.



Duane Hookom said:
Do you have a table named pb with fields pb1, pb2,...

Have you considered providing us with the actual table structure?

--
Duane Hookom
MS Access MVP
--

No does not work. I did notice the spaces and corrected . It just keeps
prompting mr for the parameter value of pb.pb1, pb.pb2, etc. I also
wrote
another UNION a little different. Still prompts for Parameter Value.



SELECT pb.[pb1] As MyNum

FROM pb
WHERE pb.[pb1] IS NOT NULL

UNION ALL

SELECT pb.[pb2]

FROM pb

WHERE pb.[pb2] IS NOT NULL


UNION ALL

SELECT pb.[pb3]

FROM pb
WHERE pb.[pb3] IS NOT NULL


UNION ALL

SELECT pb.[pb4]

FROM pb
WHERE pb.[pb4] IS NOT NULL


UNION ALL

SELECT pb.[pb5]

FROM pb
WHERE pb.[pb5] IS NOT NULL


UNION ALL SELECT pb.[pb6]

FROM pb
WHERE pb.[pb6] IS NOT NULL;

:

Does the union query by itself work? I did notice your field names in
the
union query are incorrect. Sometimes you have spaces, other times you
don't.

SELECT pb.[pb2]
FROM pb
WHERE pb.[pb 2] IS NOT NULL

--
Duane Hookom
MS Access MVP
--

It does not return any results. No error messages, just no results.
When I
click on the query it asks for Parameter Value pb.pb1, then pb.pb2,
etc...but no results.

:

On Tue, 4 Oct 2005 05:57:03 -0700, "tamxwell"

Nope...


If the Union query is saved as qryMyNum then yes, this should
work.
Doesn't it?

"Doctor, I don't feel good. What should I take?"

Please describe in what WAY it doesn't work. You can see your
screen... we cannot.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Hey,
I changed the field names and it works perfectly. Again, sorry you seem a
bit agitated. Did not mean to mess with your head.
Todd

Duane Hookom said:
You actually have "#" in your field names but when you created your union
query, you ignored the "#"?

You should be able to figure out that the select statements are based on
your table and field names, not something you make up.

--
Duane Hookom
MS Access MVP


tamxwell said:
The Table, named PB and the structure is Field 1 is ID, Field 2 is Date
of,
Field 3 is pb#1, Field 4 is pb#2, Field 5 is pb#3, Field 6 is pb#4, Field
7
is pb#5, Field 8 is pb#6.



Duane Hookom said:
Do you have a table named pb with fields pb1, pb2,...

Have you considered providing us with the actual table structure?

--
Duane Hookom
MS Access MVP
--

No does not work. I did notice the spaces and corrected . It just keeps
prompting mr for the parameter value of pb.pb1, pb.pb2, etc. I also
wrote
another UNION a little different. Still prompts for Parameter Value.



SELECT pb.[pb1] As MyNum

FROM pb
WHERE pb.[pb1] IS NOT NULL

UNION ALL

SELECT pb.[pb2]

FROM pb

WHERE pb.[pb2] IS NOT NULL


UNION ALL

SELECT pb.[pb3]

FROM pb
WHERE pb.[pb3] IS NOT NULL


UNION ALL

SELECT pb.[pb4]

FROM pb
WHERE pb.[pb4] IS NOT NULL


UNION ALL

SELECT pb.[pb5]

FROM pb
WHERE pb.[pb5] IS NOT NULL


UNION ALL SELECT pb.[pb6]

FROM pb
WHERE pb.[pb6] IS NOT NULL;

:

Does the union query by itself work? I did notice your field names in
the
union query are incorrect. Sometimes you have spaces, other times you
don't.

SELECT pb.[pb2]
FROM pb
WHERE pb.[pb 2] IS NOT NULL

--
Duane Hookom
MS Access MVP
--

It does not return any results. No error messages, just no results.
When I
click on the query it asks for Parameter Value pb.pb1, then pb.pb2,
etc...but no results.

:

On Tue, 4 Oct 2005 05:57:03 -0700, "tamxwell"

Nope...


If the Union query is saved as qryMyNum then yes, this should
work.
Doesn't it?

"Doctor, I don't feel good. What should I take?"

Please describe in what WAY it doesn't work. You can see your
screen... we cannot.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Sorry if I seemed "agitated". I guess it might be due to the fact that I
find it frustrating when I try to help and the OP either mis-informs or
under-informs. If your original question was:

My db has 6 fields all populated with numbers from 01-55
and there are 1404 records. How can I write a query to tell
me how many time each of the numbers (say number 8
comes up) in all of the fields?

My table is "PB" and my fields are ID, [Date of], PB#1,
PB#2, PB#3,...

John and I both supplied our WAGs about your table and field names and it
wasn't until several days and a dozen replies that this information was
discovered. Maybe we need to first ask you "what are your table and field
names". Some times we do and some times we don't.
--
Duane Hookom
MS Access MVP
--

tamxwell said:
Hey,
I changed the field names and it works perfectly. Again, sorry you seem a
bit agitated. Did not mean to mess with your head.
Todd

Duane Hookom said:
You actually have "#" in your field names but when you created your union
query, you ignored the "#"?

You should be able to figure out that the select statements are based on
your table and field names, not something you make up.

--
Duane Hookom
MS Access MVP


tamxwell said:
The Table, named PB and the structure is Field 1 is ID, Field 2 is Date
of,
Field 3 is pb#1, Field 4 is pb#2, Field 5 is pb#3, Field 6 is pb#4,
Field
7
is pb#5, Field 8 is pb#6.



:

Do you have a table named pb with fields pb1, pb2,...

Have you considered providing us with the actual table structure?

--
Duane Hookom
MS Access MVP
--

No does not work. I did notice the spaces and corrected . It just
keeps
prompting mr for the parameter value of pb.pb1, pb.pb2, etc. I also
wrote
another UNION a little different. Still prompts for Parameter
Value.



SELECT pb.[pb1] As MyNum

FROM pb
WHERE pb.[pb1] IS NOT NULL

UNION ALL

SELECT pb.[pb2]

FROM pb

WHERE pb.[pb2] IS NOT NULL


UNION ALL

SELECT pb.[pb3]

FROM pb
WHERE pb.[pb3] IS NOT NULL


UNION ALL

SELECT pb.[pb4]

FROM pb
WHERE pb.[pb4] IS NOT NULL


UNION ALL

SELECT pb.[pb5]

FROM pb
WHERE pb.[pb5] IS NOT NULL


UNION ALL SELECT pb.[pb6]

FROM pb
WHERE pb.[pb6] IS NOT NULL;

:

Does the union query by itself work? I did notice your field names
in
the
union query are incorrect. Sometimes you have spaces, other times
you
don't.

SELECT pb.[pb2]
FROM pb
WHERE pb.[pb 2] IS NOT NULL

--
Duane Hookom
MS Access MVP
--

It does not return any results. No error messages, just no
results.
When I
click on the query it asks for Parameter Value pb.pb1, then
pb.pb2,
etc...but no results.

:

On Tue, 4 Oct 2005 05:57:03 -0700, "tamxwell"

Nope...


If the Union query is saved as qryMyNum then yes, this should
work.
Doesn't it?

"Doctor, I don't feel good. What should I take?"

Please describe in what WAY it doesn't work. You can see your
screen... we cannot.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
I know how is can be. Usually I write it first in word just to double check
to make sure all the info is there. This was my fault, I got a little
frustrated at my people here for just assuming that I can build a db or write
a query that will magically do what they should of done to start with. I just
changed the field names and it works perfectly, they will use it once then
I'll throw it away. So, again you guys are really great at answering our
sometime obvious questions, Every once in a while I just need a different set
of eyes.......

Duane Hookom said:
Sorry if I seemed "agitated". I guess it might be due to the fact that I
find it frustrating when I try to help and the OP either mis-informs or
under-informs. If your original question was:

My db has 6 fields all populated with numbers from 01-55
and there are 1404 records. How can I write a query to tell
me how many time each of the numbers (say number 8
comes up) in all of the fields?

My table is "PB" and my fields are ID, [Date of], PB#1,
PB#2, PB#3,...

John and I both supplied our WAGs about your table and field names and it
wasn't until several days and a dozen replies that this information was
discovered. Maybe we need to first ask you "what are your table and field
names". Some times we do and some times we don't.
--
Duane Hookom
MS Access MVP
--

tamxwell said:
Hey,
I changed the field names and it works perfectly. Again, sorry you seem a
bit agitated. Did not mean to mess with your head.
Todd

Duane Hookom said:
You actually have "#" in your field names but when you created your union
query, you ignored the "#"?

You should be able to figure out that the select statements are based on
your table and field names, not something you make up.

--
Duane Hookom
MS Access MVP


The Table, named PB and the structure is Field 1 is ID, Field 2 is Date
of,
Field 3 is pb#1, Field 4 is pb#2, Field 5 is pb#3, Field 6 is pb#4,
Field
7
is pb#5, Field 8 is pb#6.



:

Do you have a table named pb with fields pb1, pb2,...

Have you considered providing us with the actual table structure?

--
Duane Hookom
MS Access MVP
--

No does not work. I did notice the spaces and corrected . It just
keeps
prompting mr for the parameter value of pb.pb1, pb.pb2, etc. I also
wrote
another UNION a little different. Still prompts for Parameter
Value.



SELECT pb.[pb1] As MyNum

FROM pb
WHERE pb.[pb1] IS NOT NULL

UNION ALL

SELECT pb.[pb2]

FROM pb

WHERE pb.[pb2] IS NOT NULL


UNION ALL

SELECT pb.[pb3]

FROM pb
WHERE pb.[pb3] IS NOT NULL


UNION ALL

SELECT pb.[pb4]

FROM pb
WHERE pb.[pb4] IS NOT NULL


UNION ALL

SELECT pb.[pb5]

FROM pb
WHERE pb.[pb5] IS NOT NULL


UNION ALL SELECT pb.[pb6]

FROM pb
WHERE pb.[pb6] IS NOT NULL;

:

Does the union query by itself work? I did notice your field names
in
the
union query are incorrect. Sometimes you have spaces, other times
you
don't.

SELECT pb.[pb2]
FROM pb
WHERE pb.[pb 2] IS NOT NULL

--
Duane Hookom
MS Access MVP
--

It does not return any results. No error messages, just no
results.
When I
click on the query it asks for Parameter Value pb.pb1, then
pb.pb2,
etc...but no results.

:

On Tue, 4 Oct 2005 05:57:03 -0700, "tamxwell"

Nope...


If the Union query is saved as qryMyNum then yes, this should
work.
Doesn't it?

"Doctor, I don't feel good. What should I take?"

Please describe in what WAY it doesn't work. You can see your
screen... we cannot.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top