Searching multiple tables

[Guest]

I have a database that contains many tables with exactly the same structure.
They're a result of a monthly "snapshot" of the core data in another
(non-Access) application. Each table corresponds to an archive made on that
date, and they serve as an index into the archive files. That way I don't
have to restore a bunch of archives to find the one that's needed.

The tables are created by an import from a text file of fixed-length
records. The import specification is saved, so that process is relatively
painless.

I'd like to be able to search through all of the tables and return a list of
the matching records, including the table names of course. The result is a
kind of running history of the changes made to that item.

I need some advice on how to loop through all of the tables to get the
matching records, and how to record the matches. I got as far as "For Each"
in the Visual Basic help, but don't really know what objects and methods to
use from there.

The number of tables in the database keeps increasing, as I import data each
month, so I'm looking for a solution that can handle that "automatically".

Maybe the methodology I described is completely bizarre, so I'm open to
suggestions!

Thanks,
Randy

 

[Guest]

Randy:

Rather than importing to a new table each month append the rows into a
single table which includes a DateImported column. You should be able to do
this with a simple 'append' query which uses either a link to the source
data, or a temporary table created by means of your existing import routine.
If you also want to record the name of the source table you'd have another
column SourceTable in the single destination table, to which you could assign
a value by means of a parameter in the in the 'append' query. The current
date would be entered automatically using the DATE() function, e.g.

INSERT INTO DestinationTable(SourceTable,DateImported, SomeField,
SomeOtherField,……)
SELECT [Enter Source:], DATE(), SomeField, SomeOtherField,……
FROM SourceTable;

To return a list of "the matching records" you first have to decide what
constitutes a 'match', i.e. which column or columns must have the same
values. Once you've determined that you can use the Find Duplicates query
wizard, or you can write your own quite simply. say the SomeField and
SomeOtherField columns determine the duplication:

SELECT *
FROM DestinationTable AS D1
WHERE (SELECT COUNT(*)
FROM DestinationTable AS D2
WHERE D2.SomeField = D2.SomeField
AND D2.SomeOtherField = D2.SomeOtherField ) > 1;

Ken Sheridan
Stafford, England

 

[Guest]

You can include multiple table in a query using a Union query. The problem
is that since the number of tables increases each month, you would have to
modify the query each month. But then, at some point you will hit a maximum
number of tables that can be unioned (I don't remember the limit off hand).

Multiple tables with identical structures usually indicates a database
design that needs improvement. This is one of those cases. A better
approach would be to consolidate all your tables into one table. You can add
a field to the table that is the date of the load. That will keep the
records separated as they are now. Then, you will need only one query to
perform your searches.

The other change you will need to make is how you do your import. I would
suggest you either Link to the external file or import it to a temporary
table, then use an Append query to add the new records to the table. During
this import process, you can populate the date field.

 

[Guest]

Ken,
Many thanks for the reply. I hadn't thought about linking to a text file and
treating it as a table. That makes the append easy. Add a field for the date
of the archive (probably best to prompt for the date) and that should do it.

Now I just need to combine about 40 tables into one, to get started!

Again, thanks for the fresh look at it.
--
Randy Griffin

Randy:

Rather than importing to a new table each month append the rows into a
single table which includes a DateImported column. You should be able to do
this with a simple 'append' query which uses either a link to the source
data, or a temporary table created by means of your existing import routine.
If you also want to record the name of the source table you'd have another
column SourceTable in the single destination table, to which you could assign
a value by means of a parameter in the in the 'append' query. The current
date would be entered automatically using the DATE() function, e.g.

INSERT INTO DestinationTable(SourceTable,DateImported, SomeField,
SomeOtherField,……)
SELECT [Enter Source:], DATE(), SomeField, SomeOtherField,……
FROM SourceTable;

To return a list of "the matching records" you first have to decide what
constitutes a 'match', i.e. which column or columns must have the same
values. Once you've determined that you can use the Find Duplicates query
wizard, or you can write your own quite simply. say the SomeField and
SomeOtherField columns determine the duplication:

SELECT *
FROM DestinationTable AS D1
WHERE (SELECT COUNT(*)
FROM DestinationTable AS D2
WHERE D2.SomeField = D2.SomeField
AND D2.SomeOtherField = D2.SomeOtherField ) > 1;

Ken Sheridan
Stafford, England

I have a database that contains many tables with exactly the same structure.
They're a result of a monthly "snapshot" of the core data in another
(non-Access) application. Each table corresponds to an archive made on that
date, and they serve as an index into the archive files. That way I don't
have to restore a bunch of archives to find the one that's needed.

The tables are created by an import from a text file of fixed-length
records. The import specification is saved, so that process is relatively
painless.

I'd like to be able to search through all of the tables and return a list of
the matching records, including the table names of course. The result is a
kind of running history of the changes made to that item.

I need some advice on how to loop through all of the tables to get the
matching records, and how to record the matches. I got as far as "For Each"
in the Visual Basic help, but don't really know what objects and methods to
use from there.

The number of tables in the database keeps increasing, as I import data each
month, so I'm looking for a solution that can handle that "automatically".

Maybe the methodology I described is completely bizarre, so I'm open to
suggestions!

Thanks,
Randy

 

[Guest]

Dave,
My thanks to you, too. While Ken beat you by a few minutes, it certainly
gives me a "warm fuzzy" feeling to get two independent answers with the same
advice.

 

[John W. Vinson]

I have a database that contains many tables with exactly the same structure.

Then you have an incorrectly structured database.

They're a result of a monthly "snapshot" of the core data in another
(non-Access) application. Each table corresponds to an archive made on that
date, and they serve as an index into the archive files. That way I don't
have to restore a bunch of archives to find the one that's needed.

The tables are created by an import from a text file of fixed-length
records. The import specification is saved, so that process is relatively
painless.

Could you use an APPEND query to load each batch into a master log
table? This should probably include some new field to identify which
snapshot was the source of the data.

I'd like to be able to search through all of the tables and return a list of
the matching records, including the table names of course. The result is a
kind of running history of the changes made to that item.

A UNION query can do this, but loading the information all into one
table will likely be much more efficient. See UNION in the online help
to see how to set it up (it may be worthwhile for testing, at any
rate).

I need some advice on how to loop through all of the tables to get the
matching records, and how to record the matches. I got as far as "For Each"
in the Visual Basic help, but don't really know what objects and methods to
use from there.

Code is going to be MUCH less efficient than properly designed
queries.

The number of tables in the database keeps increasing, as I import data each
month, so I'm looking for a solution that can handle that "automatically".

Maybe the methodology I described is completely bizarre, so I'm open to
suggestions!

I'll suggest one table with monthly appends, then!

John W. Vinson [MVP]

 

[Guest]

John,
Thanks. I'm getting the same answer from multiple people, so I should
probably take the advice.

It's not so much that the database was incorrectly designed, it's that it
wasn't designed at all! The original intent was to have the key info
available to search so that a full restore of an archive wasn't required to
search for one record that might not even be there. Of course now there's
getting to be an unmanageable number of tables.

I'll tackle the job of merging them into one.

Thanks again, everyone,
Randy