Help with count query

  • Thread starter Thread starter garry
  • Start date Start date
G

garry

Hello everyone.

I have to process a large table with column names that are not fixed.
Each record contains digits. Some columns will have digits from 1 to 5,
some 1 to 6, some 3 to 5 etc. I want to count the number of each type
of digit in each column eg.

A)
Digit Col1 Col2 Col3 ... Col100
1 10 12 0 0
2 5 7 9 5
3 6 8 12 ... 7
4 10 6 1 ... 0
5

etc.

or B)
Name Digit CountofDigit
Col1 1 10
Col1 2 5
Col1 3 6
Col1 4 10
Col2 1 12
Col2 2 7
etc.

Can anyone suggest how to write a query to do this? I can produce
output like B) by programatically writing a separate query for each
column and joining them with a UNION statement. But there may be up to
100 columns, and 50,000 records, and this method is too slow (as well
as being ugly).

Any ideas gratefully received!

Regards
Garry
 
Is there any way that you can set up your tables normalized to begin with
rather than having to normalize with a union query?

If you use UNION ALL, the union query would perform faster. Once you have
your table normalized, you can create your end result with a crosstab query.
 
Hi Duane - I don't think its possible to normalize: but thanks for the
tip, it will be useful in another situation I've got.
Garry
 
Back
Top