Search & update - Another difficult update query

[mac_see]

I have 164 tables in my database (table names = 1, 2, 3, ..... 164). Every
table consists of 5 Fields (Field1, Field2, Field3, Field4 and Field5)
Field1, Field2, Field3 & Field4 has will have 50-150 records (these are
random numbers from 1 to 100). The count of records will differ from table to
table. Example Table (1) may have 75 records and (2) may have 102 records
..... etc.
Field5 is a calculated field that has a value which is difference of Field4
and Field1 (Field4-Field1)

I have another table (table name = check) with five fields; Filed1, Field2
(field name = Min), Field3 (field name = Max), Field4 and Field5. (All fields
are of number INTEGER data type.
Field1 of the check table has 164 records. It contains serial numbers from 1
to 164. Rest all the 4 fields are blank.

I want to run a Query/VBA that will check the minimum number in Field5 of
Table (1) and should update Record 1 of Min field in the check table.
The query should then check the maximum number in Field5 of Table (1) and
should update Record 1 of Max field in the check table.
The query should then check the maximum number in Field4 of Table (1) and
should update Record 1 of Field4 in the check table.
The query should then check the count (number of records) of Field1 of Table
(1) and should update Record 1 of Field5 in the check table.

Do the same task for all 164 tables.


1
=
f1 f2 f3 f4 f5
1 2 15 20 19
5 4 9 18 13
6 8 9 11 5

2
=
f1 f2 f3 f4 f5
2 3 9 20 18
3 5 11 18 15
6 12 13 21 15
12 13 21 28 16


Check
=====
F1 Min Max f4 f5
1 5 19 20 3
2 15 18 20 4

 

[mac_see]

Value of Record 2 of Field 4 of Check table should be 28 and not 20. Sorry
about that.

 

[John Nurick]

By having 164 identically structured tables in the database you are in
effect storing data in the table names. If you put all these records in
a single table, with one field contaning values 1 to 164 to show where
the records came from, it would be very, very much easier to extract
summary data of the kind you seem to need. In particular, you would be
able to process all 164 datasets - if that's what they are - with a
single query.

This is an alternative to my suggestion elsewhere of working at the
textfile level: which of the two is better depends on things I don't
know about your situation.

 

[mac_see]

My issue is resolved.

It was very easy.

Maxi

 

[David C. Holley]

I concur. It is ALWAYS best, regardless of how much you may spit nails
to the contrary, to have all identical data in the same table. I had to
test a DB that was put together by someone who did not adhear to this
and just about had a cow as to how it was design. Specifically, he was
using append & delete queries to move data around when a simple select
query would have done the job.

David H