xxxHELP on query countingxxx

[Guest]

Here’s the deal. I have two Tables T1 & T2, a one to many relationship
(T1 has ID, F1, F2, F3) (T2 has ID, X1, X2, X3, X4). I want to create a query that will count every field in T2. Exampl

T2 contains (text items
ID-X1-X2-X3-X
01-13-13-16-1
02-14-13-17-1
03-15-15-12-1
etc

I want my query to count each of the text items

13 appears 4 time
14 appears 1 tim
17 appears 1 tim
15 appears 2 time
et

Is possible?? I’m new to this, so go easy on me, thank

 

[Duane Hookom]

I would start by normalizing your table with a union query
=quniTheValues======
SELECT X1 as TheValue
FROM T2
UNION ALL
SELECT X2
FROM T2
UNION ALL
SELECT X3
FROM T2
UNION ALL
SELECT X4
FROM T2;

Then create a totals query with sql of
SELECT TheValue, Count(TheValue) as NumOf
FROM quniTheValues;

--
Duane Hookom
MS Access MVP

Here's the deal. I have two Tables T1 & T2, a one to many relationship.
(T1 has ID, F1, F2, F3) (T2 has ID, X1, X2, X3, X4). I want to create a

query that will count every field in T2. Example

 

[Guest]

----- Duane Hookom wrote: ----

I would start by normalizing your table with a union quer
=quniTheValues=====
SELECT X1 as TheValu
FROM T
UNION AL
SELECT X
FROM T
UNION AL
SELECT X
FROM T
UNION AL
SELECT X
FROM T2

Then create a totals query with sql o
SELECT TheValue, Count(TheValue) as NumO
FROM quniTheValues

 

[John Spencer (MVP)]

Pardon me,

What Duane was suggesting is that you do two queries.

Make the first query the union query and save it with the name quniTheValues
Now use that saved query as the source for the second query.

If you are getting the error as stated, you have either not saved the union
query or you have not spelled it the same in the query and in the union query's name.

You can apply criteria to your query. Something like
SELECT X1 as theValue
FROM T2
WHERE X1 is Not Null
UNION ALL
SELECT X2
FROM T2
WHERE T2 is Not Null
....

However that should not be necessary, since Count ignores nulls and just counts
the field if there is something in the field.