Text Field Calculation

  • Thread starter Thread starter Paul
  • Start date Start date
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
 
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.
 
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.)
 
Back
Top