How to Search Fields from Multi-Tables Form?

[Yeisha2008]

Hi,

I have home page using MS ACCESS DATABASE 2010, with search box below example:

[IMG]file:///C:%5CUsers%5Cdeshtlel%5CAppData%5CLocal%5CTemp%5Cm sohtmlclip1%5C01%5Cclip_image001.png[/IMG]

And I have four tables like below and will be add up until 10 or more. I'm trying to create a query to combine them all. At Home Page, when I search any field, all value of that specific field from all the tables needs to pop up, below are examples.

Table 1

a	b	c	d	e	f

Table 2

a	b	c	d	e	f

Table 3

a	b	c	d	e	f

Table 4

a	b	c	d	e	f

I tried to watch tutorial video and search at any forums but could not find a way for any help. Will Highly Appreciated your help.

[pbaldy]

Your image doesn't show, but you can use a UNION query to combine multiple tables:

SELECT Field1, Field2
FROM Table1
UNION ALL
SELECT Field1, Field2
FROM Table2

Note that having tables like that is often a sign of an unnormalized database, so I'd be curious what they contain.

[Yeisha2008]

Your image doesn't show, but you can use a UNION query to combine multiple tables:

SELECT Field1, Field2
FROM Table1
UNION ALL
SELECT Field1, Field2
FROM Table2

Note that having tables like that is often a sign of an unnormalized database, so I'd be curious what they contain.

Hi Thanks for trying to help. Please see attached image... trying to attached the dbase,
but always error. For u to figure out. thanks!!!

[pbaldy]

Perhaps I'm dense, but that doesn't really clarify the question for me.

[Yeisha2008]

If i will multi search destination all their information will pop up on the table below from all tables.
like if i will search Cayman Island, type and area codes. all information will pop up! Thanks so much!

[uncletreetrunk]

Yeisha,

Try posting a stripped down version of your database so pbaldy can try to help. (He's really helpful!)

Take out sensitive material and go to Office>Manage>Compact and Repair

[pbaldy]

The sample db here demonstrates one method of doing that type of thing:

http://www.baldyweb.com/BuildSQL.htm

[Yeisha2008]

The sample db here demonstrates one method of doing that type of thing:

http://www.baldyweb.com/BuildSQL.htm

hi,

kinda confusing with sql , i attached the sample database that im doing. pls help.

Thanks!

[pbaldy]

For starters, that's missing the subforms. In any case, I don't see that you've tried to incorporate that or any filtering method. If that method is too complicated for you, you can try basing the subform on a query that uses this technique:

http://access.mvps.org/access/queries/qry0001.htm

[Yeisha2008]

For starters, that's missing the subforms. In any case, I don't see that you've tried to incorporate that or any filtering method. If that method is too complicated for you, you can try basing the subform on a query that uses this technique:

http://access.mvps.org/access/queries/qry0001.htm

Hi,

Sorry I attached the wrong files .. Just really newbie here .. and i can just do simple database,
just learn at youtube videos. But now is really complicated for me, 3 days already can't figure out what
to do.

Thanks really appreciate ur help!