Search Multiple Tables at once.........?

[Gobi]

Hello, I have a Database with lists of Clients in each.
Every year a new tables is created with the naming convention
"CloseYear"
ie close1999, close2000
There are tables from this year back to 1989.

I need to be able to
1) Go to a Search for
2) Enter Criteria -(Client_Last_Name) (Client_First_Name)
(File_Number)
3) Click Find button on form
4) Display Name of Table in which Criteria Matches on a mesage box or
output to a text file

Here is some of my psuedo code
Open Table Close89
search for criteria
If Found then Place table name in memory
Close Table close89
Open Table Close90
Search for criteria
If Found then Place table name in memory
Close Table Close90
loop until all tables searched
Here is a printout of the text file created by the current Database
Program
whe...Client_Last_Name of "Smith" is searched for

Search Criteria used: ==============
Client Last Name: "Smith"
Client First Name: ""
Search Type: "conservative"
current rec# = 471 of 543
Fields searched = 1.CLIENT & contents = Smith, Elijah

table name = 90close.db
================================================== =
current rec# = 472 of 543
Fields searched = 1.CLIENT & contents = Smith, Pamela N.

table name = 90close.db
================================================== =
current rec# = 473 of 543
Fields searched = 1.CLIENT & contents = Smith, Paul D.

table name = 91close.db
================================================== =
current rec# = 562 of 684
Fields searched = 1.CLIENT & contents = Smith, Alicia T.

table name = 91close.db

I need to be able to do this Access itself or VBA
Does anyone know how to Code this?

[Larry Linson]

Unfortunately, the table structure you have chosen has complicated the task
for you. Had you simply inserted a date field for date of closing, you'd be
searching a single table. Restructuring in that manner is my suggestion for
a solution to your problem.

Sticking with an improper design will cost you more time and effort over
time than correcting the problem now.

The solution you want, which is to return the _table name_ as though it were
data, is a violation of relational database design principles.

If you simply wanted the record(s), a UNION or UNION ALL Query joining the
various closed-tables might work for you.

Larry Linson
Microsoft Access MVP

"Gobi" <jn******@email.com> wrote in message
news:cf**************************@posting.google.c om...

Hello, I have a Database with lists of Clients in each.
Every year a new tables is created with the naming convention
"CloseYear"
ie close1999, close2000
There are tables from this year back to 1989.

I need to be able to
1) Go to a Search for
2) Enter Criteria -(Client_Last_Name) (Client_First_Name)
(File_Number)
3) Click Find button on form
4) Display Name of Table in which Criteria Matches on a mesage box or
output to a text file

Here is some of my psuedo code
Open Table Close89
search for criteria
If Found then Place table name in memory
Close Table close89
Open Table Close90
Search for criteria
If Found then Place table name in memory
Close Table Close90
loop until all tables searched
Here is a printout of the text file created by the current Database
Program
whe...Client_Last_Name of "Smith" is searched for

Search Criteria used: ==============
Client Last Name: "Smith"
Client First Name: ""
Search Type: "conservative"
current rec# = 471 of 543
Fields searched = 1.CLIENT & contents = Smith, Elijah

table name = 90close.db
================================================== =
current rec# = 472 of 543
Fields searched = 1.CLIENT & contents = Smith, Pamela N.

table name = 90close.db
================================================== =
current rec# = 473 of 543
Fields searched = 1.CLIENT & contents = Smith, Paul D.

table name = 91close.db
================================================== =
current rec# = 562 of 684
Fields searched = 1.CLIENT & contents = Smith, Alicia T.

table name = 91close.db

I need to be able to do this Access itself or VBA
Does anyone know how to Code this?

[David W. Fenton]

"Larry Linson" <bo*****@localhost.not> wrote in
news:is*******************@nwrddc03.gnilink.net:

Unfortunately, the table structure you have chosen has complicated
the task for you. Had you simply inserted a date field for date of
closing, you'd be searching a single table. Restructuring in that
manner is my suggestion for a solution to your problem.

Sticking with an improper design will cost you more time and
effort over time than correcting the problem now.

The solution you want, which is to return the _table name_ as
though it were data, is a violation of relational database design
principles.

If you simply wanted the record(s), a UNION or UNION ALL Query
joining the various closed-tables might work for you.

Seems an inadvisable use of the word "joining," since that implies
to me horizontally bringing the tables together, rather then
vertically. A UNION puts records from multiple sources into a single
recordset.

But the real downside of searching a UNION is that the criteria
can't use the indexes in the underlying tables, which will thus be
very, very slow with any large number of records.

Having data for different years in different tables is just a bad
idea all around. Access can handle millions of records, so there
really isn't any justification for separating the things out into
separate tables. And the downside of it is quite clear from the
problem at hand.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry Linson]

"David W. Fenton" wrote

If you simply wanted the record(s), a UNION
or UNION ALL Query joining the various
closed-tables might work for you.
Seems an inadvisable use of the word "joining," since that implies
to me horizontally bringing the tables together, rather then
vertically.

In retrospect, I agree, as "join" has a specific meaning in queries.
A UNION puts records from multiple
sources into a single recordset.
I'd say it "appends" the records returned from each of the UNIONed Queries,
but "append" also has a special meaning. Let's just say it puts all the
records "one after the other".
Having data for different years in different
tables is just a bad idea all around.

Yes, clearly so, and combining them with the addition of an identifying year
(or period) field was my top suggestion.

Larry Linson
Microsoft Access MVP

[Pieter Linden]

jn******@email.com (Gobi) wrote in message news:<cf**************************@posting.google. com>...

Hello, I have a Database with lists of Clients in each.
Every year a new tables is created with the naming convention
"CloseYear"
ie close1999, close2000
There are tables from this year back to 1989.

I need to be able to
1) Go to a Search for
2) Enter Criteria -(Client_Last_Name) (Client_First_Name)
(File_Number)
3) Click Find button on form
4) Display Name of Table in which Criteria Matches on a mesage box or
output to a text file

As Larry said, normalize! Create a new table
(Client_Last_Name) (Client_First_Name) (File_Number) (File_Year)

and then append the data in your tables to this one. Then you can do
things the easy way...

SELECT *
FROM tblData
WHERE File_Year BETWEEN 1998 AND 2002;