From: naveen prasad on
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
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
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
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
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