Help with count query

[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

 

[Duane Hookom]

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.

 

[Gary Walter]

Hi Garry,

This sounds to me like a candidate
for fImportToThinTable

http://groups-beta.google.com/groups?hl=en&q=fImportToThinTable

Once you have it in a thin table,
getting count will be no big deal.

good luck,

gary

 

[garry]

Hi Gary

Many thanks for this. I'll give it a try.

 

[garry]

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