Count across fields in a table 
Author Message
 Count across fields in a table

Looking for some help getting started.  My table has approx.100 fields and
each field contains an integer (number 1-12). Each number refers to a
certain area of traning.  I need a total number of times each integer
appears in each record (1000 records).  I assume this will require a record
set and then looping through looking for total number of 1s, total number of
2s, etc.
Any advice would be appreciated.


Thu, 13 Nov 2003 00:26:41 GMT  
 Count across fields in a table
Hi,

You are the very laborious kind... :-)

Make a new query, bring your table, click on the summation button on the
toolbar, a new line, total, appear in the grid. Drag the field F1 in the
grid, keep the GroupBy. Drag the field F1 in the grid, again, change the
GroupBy to Count. That's almost it. Take a look at the data, that produce
the number of time each integer appears in ... F1. Now, thanks to your table
which seems to be not normalized, you have 100 fields, not just one, on
which we have to do the same thing. Well, every thing is not lost. Quit and
DO NOT save that query.

Make a new query, go in SQL view, type:

SELECT F1, 1 As WasFieldName FROM myTable
UNION ALL
SELECT F2, 2 FROM myTable
UNION ALL
SELECT F3, 3 FROM myTable
...

UNION ALL
SELECT F100, 100 FROM myTable

If you take a look at the data, your table is now normalized, and there is
only ONE column from which we have to count the data (it is called F1).
There is another column, WasFieldName, supplying the original field number
from which the data comes from. Save that query, say QUnion. Now, you do the
first query, but use QUnion as table, and that's all.

Sure, the typing or QUnion is long. It would have been easier if you would
have used a normalized table right at the start. They are harder to read
than non-normal table, but WORKING with them is much easier. Furthermore, to
CONSULT data, we use FORM, not TABLE, don't we? So, why not using normal
tables then?

Hoping it may help,
Vanderghast, Access MVP



Quote:
> Looking for some help getting started.  My table has approx.100 fields and
> each field contains an integer (number 1-12). Each number refers to a
> certain area of traning.  I need a total number of times each integer
> appears in each record (1000 records).  I assume this will require a
record
> set and then looping through looking for total number of 1s, total number
of
> 2s, etc.
> Any advice would be appreciated.



Fri, 14 Nov 2003 19:54:15 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Count table fields with specific value in SCR8.

2. Field count of a table with DAO

3. getting field count on an Access table

4. Scanning tables and moving data across tables

5. Count across the page rather than down

6. Count help- across not down

7. Create new fields in a table based off of fields in another table

8. Create new field in existing table exactly like field in second table

9. updating fields in table from field in another table

10. Fill field in one table from field in another table

11. Eliminate records of one table based on another table record count

12. Counting tables BUT not system tables

 

 
Powered by phpBB® Forum Software