If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Multiple tables search
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. |
#2
|
|||
|
|||
Multiple tables search
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] -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Multiple tables search
Dear thank you very much it really worked...
thanks a million.. "Marshall Barton" wrote: 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] -- Marsh MVP [MS Access] . |
#4
|
|||
|
|||
Multiple tables search
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 "naveen prasad" wrote: Dear thank you very much it really worked... thanks a million.. "Marshall Barton" wrote: 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] -- Marsh MVP [MS Access] . |
#5
|
|||
|
|||
Multiple tables search
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] naveen prasad wrote: In the Out put can I also get the table name where the mobile number exists. "naveen prasad" wrote: Dear thank you very much it really worked... "Marshall Barton" wrote: 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] -- Marsh MVP [MS Access] |
#6
|
|||
|
|||
Multiple tables search
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 wrote: SELECT t1.name. "t1" As Table FROM t1 -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
Multiple tables search
naveen prasad wrote:
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201006/1 |
#8
|
|||
|
|||
Multiple tables search
Both ways worked excellent, tblname & "t1"
Thanks a million to you all for helping me. regards "PieterLinden via AccessMonster.com" wrote: naveen prasad wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201006/1 . |
Thread Tools | |
Display Modes | |
|
|