access connection to 2 tables to find a common name in both tables

Hi all,

I needed to build an excel macro that connects to a database with 2 tables. I can read and write to it. The thing I'm not able to do is to create a query that will take a name and check it in both tables and show the results in excel.
Both tables are the same in layout, only the name is different. They are linked with the same field (ClientCompanyName). there is the field called (SurName). When I want to do a search, I type the surname in a search field (excel cell), press the button and finds that info, but don't know how to do it with 2 tables.

Any clue how to do this?


this is the code that I use for search:

Please Login or Register  to view this content.

If it can be adjusted to have it from 2 tables (it could like From Data And Data2 or similar)

Greetings.

Hi all,

No one has ideas?

I just need to pull 2 times the rows that have the same last name.
If I have in 2 tables a last name where one person is related to 2 different companies, it needs to show both rows from the 2 databases.
so James Jones could be related in table1 to company X and in table2 to company Y, it will show both rows where I can see James Jones.
How can I do this?

Greetings.

Maybe:

PHP Code: 

SELECT [ID],[Analyst],[TypeHoliday],[DateFrom],[DateTo] FROM Data Where [TypeHoliday] Like '%Some Term%'
UNION ALL
SELECT [ID],[Analyst],[TypeHoliday],[DateFrom],[DateTo] FROM Data2 Where [TypeHoliday] Like '%Some Term%' 


Hi Kyle123,

Just what I needed :-)

Big thanks.