Multiple tables search

[naveen prasad]

Hi all,
Kindly solve my problem.

I have a mdb file.
tables created t1,t2,t3,t4.

fields are almost same in all tables, but the data is different in all tables.

fields are.. name, age, mobile_number,place.

I want to create a query to get name by mobile_number search.

In query execution the input i want to give is mobile number only, it should
search the mobile number in all tables and get the correct name.

kindly help how can should i create the query.

 

[Marshall Barton]

I have a mdb file.
tables created t1,t2,t3,t4.

fields are almost same in all tables, but the data is different in all tables.

fields are.. name, age, mobile_number,place.

I want to create a query to get name by mobile_number search.

In query execution the input i want to give is mobile number only, it should
search the mobile number in all tables and get the correct name.

SELECT t1.name FROM t1
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t2.name FROM t2
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t3.name FROM t3
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t4.name FROM t4
WHERE mobile_number = [Enter mobile number]

 

[naveen prasad]

Dear thank you very much it really worked...

thanks a million..

I have a mdb file.
tables created t1,t2,t3,t4.

fields are almost same in all tables, but the data is different in all tables.

fields are.. name, age, mobile_number,place.

I want to create a query to get name by mobile_number search.

In query execution the input i want to give is mobile number only, it should
search the mobile number in all tables and get the correct name.

SELECT t1.name FROM t1
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t2.name FROM t2
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t3.name FROM t3
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t4.name FROM t4
WHERE mobile_number = [Enter mobile number]

 

[naveen prasad]

Dear kindly need a great help here again.

In the Out put can I also get the table name where the mobile number exists.

Kindly advice How should write query for this.

thank you

Dear thank you very much it really worked...

thanks a million..

I have a mdb file.
tables created t1,t2,t3,t4.

fields are almost same in all tables, but the data is different in all tables.

fields are.. name, age, mobile_number,place.

I want to create a query to get name by mobile_number search.

In query execution the input i want to give is mobile number only, it should
search the mobile number in all tables and get the correct name.

SELECT t1.name FROM t1
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t2.name FROM t2
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t3.name FROM t3
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t4.name FROM t4
WHERE mobile_number = [Enter mobile number]

 

[PieterLinden via AccessMonster.com]

Hi all,
Kindly solve my problem.

I have a mdb file.
tables created t1,t2,t3,t4.

fields are almost same in all tables, but the data is different in all tables.

fields are.. name, age, mobile_number,place.

I want to create a query to get name by mobile_number search.

In query execution the input i want to give is mobile number only, it should
search the mobile number in all tables and get the correct name.

kindly help how can should i create the query.

Personally, I think you should redesign. If you have lots of optional fields,
you can put them in a separate table, but I would put a field in the table
that identifies what kind of "person" or whatever this is. Then you can put
all the stuff in one table and use the indexes. If you use a union query,
they all get ignored. When your tables get to over about 100 records,
performance will be terrible.

 

[Marshall Barton]

SELECT t1.name. "t1" As Table FROM t1
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t2.name, "t2" FROM t2
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t3.name, "t3" FROM t3
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t4.name, "t4" FROM t4
WHERE mobile_number = [Enter mobile number]

In the Out put can I also get the table name where the mobile number exists.

Dear thank you very much it really worked...

naveen prasad wrote:
I have a mdb file.
tables created t1,t2,t3,t4.

fields are almost same in all tables, but the data is different in all tables.

fields are.. name, age, mobile_number,place.

I want to create a query to get name by mobile_number search.

In query execution the input i want to give is mobile number only, it should
search the mobile number in all tables and get the correct name.


SELECT t1.name FROM t1
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t2.name FROM t2
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t3.name FROM t3
WHERE mobile_number = [Enter mobile number]
UNION ALL
SELECT t4.name FROM t4
WHERE mobile_number = [Enter mobile number]

 

[nrgins via AccessMonster.com]

I wonder if using "Table" would create problems, since it's a reserved word.
Maybe "TblName" instead?

Anyway, I liked your solution of using dynamic SQL to embed the search
criteria into each component of the union query. Must faster than creating a
union query and running a Select statement against it!

 

[Marshall Barton]

Good point. TblName would definitely elimiate the issue.

 

[naveen prasad]

Both ways worked excellent, tblname & "t1"

Thanks a million to you all for helping me.


regards