Union Querry problem!

[Guest_imported]

Hi Guys!

I have 2 tables in MS Access.
One is a contact table which stores the personal information of contacts and other is an activity table which stores the activities of all the contacts I have done.

now i have created a search engine in which whenevr i give a keyword i compare it with all the fields of Contact table
and this query is in union with the query which searches the activity table.
Everything works fine but the problem is whenevr i do not enter anything an then search then it gives an error saying that the output can be only one record.

my querry was like this

select * from contacts where field1 like %variable% or field2 like %var%

Union

select * from activity where field1 like %keyword%

structure of table is as follows

contact

fields
ID -- autonumber (indexed)
name
company

etc


activity
key- autonumber (indexed)
id-- correspionding the contact
description--desc of activity
etc


these tables are in MS Access

Please instruct me what the error is or tell me an alternate query so that if i give a key word then it searches all the fields of contact and activity and give outh the record id of contacts.


Thanx in advance.
GG

 

[MikeLacey]

GG,

I think you'll find that the two Select statements (that you wish to treat as one -- with the UNION keyword) need to return identical column lists.

So, if they're looking at different tables you need to specify the columns, so that they look the same to UNION.

E.G.

Select USER_NAME, DEPT
From USER_TABLE

Union

Select USER_NAME, DEPT
From USER_CONTACTS;