Text Field Calculation

P

Paul

I have a record containing 4 text fields ... category1, 2, 3 and 4.
each field holds a text value based upon a category table.
The record fields may hold repeat values, eg. Category1 and 3 could hold the
same value.

I want to count the number of instances of each text value and group by
category.

Could you please help.
TIA.

Paul
 
D

Douglas J. Steele

At least part of the problem you're running into is due to the fact that
your database doesn't appear to have been normalized. Having fields in a
table with names like category1, category2, etc. is a sure sign that you
have a repeating group which should be resolved by using a second table,
with each category being a different row in that new table. Then your query
would become quite simple.
 
D

Douglas J. Steele

I guess I should have mentioned that, if you're stuck with the design, you
can at least simulate the proper design through a UNION query.

Create a query along the lines of:

SELECT Field1, Field2, 1 AS CategoryNumber, Category1 AS Category
FROM MyTable
UNION ALL
SELECT Field1, Field2, 2 AS CategoryNumber, Category2 AS Category
FROM MyTable
UNION ALL
SELECT Field1, Field2, 3 AS CategoryNumber, Category3 AS Category
FROM MyTable
UNION ALL
SELECT Field1, Field2, 4 AS CategoryNumber, Category4 AS Category
FROM MyTable

Try doing your calculation on this saved query instead:

SELECT Category, Count(*)
FROM MyUnionQuery
GROUP BY Category

(Note the use of UNION ALL in the query above. If you just use UNION, you'll
lose duplicates.)
 
Top