From: naveen prasad on 6 Jun 2010 05:33 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.
From: Marshall Barton on 6 Jun 2010 06:25 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: naveen prasad on 6 Jun 2010 08:25 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] > . >
From: naveen prasad on 6 Jun 2010 10:25 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] > > . > >
From: PieterLinden via AccessMonster.com on 6 Jun 2010 11:51 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
|
Next
|
Last
Pages: 1 2 Prev: Sum and DateDiff Next: Serious Problem with Access 2010 "Query to complex" |