A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multiple tables search



 
 
Thread Tools Display Modes
  #1  
Old June 6th, 2010, 10:33 AM posted to microsoft.public.access.queries
naveen prasad[_2_]
external usenet poster
 
Posts: 80
Default 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  
Old June 6th, 2010, 11:25 AM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old June 6th, 2010, 01:25 PM posted to microsoft.public.access.queries
naveen prasad[_2_]
external usenet poster
 
Posts: 80
Default 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  
Old June 6th, 2010, 03:25 PM posted to microsoft.public.access.queries
naveen prasad[_2_]
external usenet poster
 
Posts: 80
Default 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  
Old June 6th, 2010, 04:51 PM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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

  #6  
Old June 6th, 2010, 05:10 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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]
  #7  
Old June 6th, 2010, 07:34 PM posted to microsoft.public.access.queries
nrgins via AccessMonster.com
external usenet poster
 
Posts: 3
Default 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

  #8  
Old June 6th, 2010, 08:05 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Multiple tables search

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


  #9  
Old June 7th, 2010, 07:57 AM posted to microsoft.public.access.queries
naveen prasad[_2_]
external usenet poster
 
Posts: 80
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.