Multiple tables search
in [Queries]
|
From: Marshall Barton on 6 Jun 2010 12:10 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]
From: nrgins via AccessMonster.com on 6 Jun 2010 14:34 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
From: Marshall Barton on 6 Jun 2010 15:05 Good point. TblName would definitely elimiate the issue. -- Marsh MVP [MS Access] nrgins via AccessMonster.com wrote: >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
From: naveen prasad on 7 Jun 2010 02:57 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/Forums.aspx/access-queries/201006/1 > > . >
First
|
Prev
|
Pages: 1 2 Prev: Sum and DateDiff Next: Serious Problem with Access 2010 "Query to complex" |