searching all tables

[Guest]

I have a database that has several tables in it. Each table contains each
years information. I want to be able to search all the tables for one certain
record. This record is a unquie number, but is not in numeric order. I want
to be able to put in the number, and have it search all the tables. How can I
do this?

This is what I have for one table, but I would like to have only one query
and have it search all the tables. These are tables that contain 250,000
records.
SELECT [2006].*
FROM 2006
WHERE (([2006].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])

 

[Marshall Barton]

I have a database that has several tables in it. Each table contains each
years information. I want to be able to search all the tables for one certain
record. This record is a unquie number, but is not in numeric order. I want
to be able to put in the number, and have it search all the tables. How can I
do this?

This is what I have for one table, but I would like to have only one query
and have it search all the tables. These are tables that contain 250,000
records.
SELECT [2006].*
FROM 2006
WHERE (([2006].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])

Try something like:

SELECT [2006].*
FROM 2006
WHERE ([fieldname] = [Enter number(Ex:
03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2005].*
FROM 2005
WHERE ([fieldname] = [Enter number(Ex:
03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2004].*
FROM 2004
WHERE ([fieldname] = [Enter number(Ex:
03-B0-C-000012851-0001) ])

 

[Guest]

The #1 problem is having a table for each year. You should have one table
with a Date or Year field.

To deal with your less than optimum current situation, you'll need to use
UNION queries to join the different tables. They are slow and inefficient.
Also you'll have to remember to add to the query every year.

UNION ALL is faster than a UNION query however UNION ALL allows duplicates
to be returned. If dupes are a problem, remove the word ALL.

SELECT [2006].*
FROM 2006
WHERE (([2006].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2005].*
FROM 2005
WHERE (([2005].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2004].*
FROM 2004
WHERE (([2004].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])

And so on....

 

[John W. Vinson]

I have a database that has several tables in it. Each table contains each
years information.

Then you have an incorrectly designed database. Much better would be to have
ONE table with all of the data, with an indexed field for the year (not named
Year since that's a reserved word).

I want to be able to search all the tables for one certain
record. This record is a unquie number, but is not in numeric order. I want
to be able to put in the number, and have it search all the tables. How can I
do this?

This is what I have for one table, but I would like to have only one query
and have it search all the tables. These are tables that contain 250,000
records.
SELECT [2006].*
FROM 2006
WHERE (([2006].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])

With your current design you'll need a UNION query:

SELECT [2005].*
FROM 2005
WHERE (([2005].[fieldname]) = [Enter number(Ex: 03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2006].*
FROM 2006
WHERE (([2006].[fieldname]) = [Enter number(Ex: 03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2007].*
FROM 2007
WHERE (([2007].[fieldname]) = [Enter number(Ex: 03-B0-C-000012851-0001) ])

Note that I changed the LIKE (which uses wildcards) to = to retrieve the exact
value. Be sure that [fieldname] is indexed in all of the tables or performance
will be dreadful; with the index it will just be poor <g>.

John W. Vinson [MVP]

 

[Guest]

Yea not well thought out but it what I have for now.

Thanks for the help,
PIXIE

The #1 problem is having a table for each year. You should have one table
with a Date or Year field.

To deal with your less than optimum current situation, you'll need to use
UNION queries to join the different tables. They are slow and inefficient.
Also you'll have to remember to add to the query every year.

UNION ALL is faster than a UNION query however UNION ALL allows duplicates
to be returned. If dupes are a problem, remove the word ALL.

SELECT [2006].*
FROM 2006
WHERE (([2006].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2005].*
FROM 2005
WHERE (([2005].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2004].*
FROM 2004
WHERE (([2004].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])

And so on....
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a database that has several tables in it. Each table contains each
years information. I want to be able to search all the tables for one certain
record. This record is a unquie number, but is not in numeric order. I want
to be able to put in the number, and have it search all the tables. How can I
do this?

This is what I have for one table, but I would like to have only one query
and have it search all the tables. These are tables that contain 250,000
records.
SELECT [2006].*
FROM 2006
WHERE (([2006].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])

 

[Guest]

Is there a way to cut down the request( parameters) asked to one entery for
all?

The #1 problem is having a table for each year. You should have one table
with a Date or Year field.

To deal with your less than optimum current situation, you'll need to use
UNION queries to join the different tables. They are slow and inefficient.
Also you'll have to remember to add to the query every year.

UNION ALL is faster than a UNION query however UNION ALL allows duplicates
to be returned. If dupes are a problem, remove the word ALL.

SELECT [2006].*
FROM 2006
WHERE (([2006].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2005].*
FROM 2005
WHERE (([2005].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])
UNION ALL
SELECT [2004].*
FROM 2004
WHERE (([2004].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])

And so on....
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a database that has several tables in it. Each table contains each
years information. I want to be able to search all the tables for one certain
record. This record is a unquie number, but is not in numeric order. I want
to be able to put in the number, and have it search all the tables. How can I
do this?

This is what I have for one table, but I would like to have only one query
and have it search all the tables. These are tables that contain 250,000
records.
SELECT [2006].*
FROM 2006
WHERE (([2006].[fieldname]) Like [Enter number(Ex: 03-B0-C-000012851-0001) ])

 

[Marshall Barton]

Is there a way to cut down the request( parameters) asked to one entery for
all?

Use a form text box for the date. Change the parameter from
whatever you have to some thing like:
Forms![name of the form],[name of the text box]

 

[John W. Vinson]

Use a form text box for the date. Change the parameter from
whatever you have to some thing like:
Forms![name of the form],[name of the text box]

I think Marsh made a small typo - should be ! or . rather than a comma there.

John W. Vinson [MVP]