Searching unreleated tables

pennwhite · #1 (**permalink**) 02-19-05, 18:09

I have three tables with name and keyword fields - tblRefJournals, tblRefBOoks and tblRefInternet. These tables are not related but they are viewable from the same form/subform by clicking the proper radio button.

I would like to create a subset which would contain all the records that contain (SQL LIKE) keywords that a user enters into a textbox.

I can set a filter for the records which will reflect the results of the SQL SELECT statement but I can't figure out how exactly to do the SQL statement. I've never tried to create an SQL for data from unrelated tables before.

Any help appreciated.

Penn

P.S. I'm working in MS Access 2003 (*.mdb) but I don't think that should make any difference.

r937 · #2 (**permalink**) 02-20-05, 00:02

Code:

select 'journal  ' as source
     , name
  from tblRefJournals
 where keyword like '%foo%'
union all
select 'book     ' as source
     , name
  from tblRefBOoks
 where keyword like '%foo%'
union all
select 'internet ' as source
     , name
  from tblRefInternet
 where keyword like '%foo%'

pennwhite · #3 (**permalink**) 02-20-05, 07:43

Thank you very much. I'll have a good look at 'union all'.

Penn

Internet Services

Web Development

Digital Marketing

Consumer Tech