Hi
I am trying to construct a search engine in asp using an access database.
I have 3 (possibly 1 or 2 more in the future) tables that need querying:
Images
ProductCode
HiRes
Products
ProductsURL
ProductCode
ProductTitle
ProductDescription
ProductSpec
Resources
resourcesURL
ProductCode
DataSheet
HandBook
Drivers1
I have tried a union all approach but it only returns the field HiRes:
SELECT HiRes
FROM Images
WHERE
((ProductCode Like '*sftgsfghf*') or (HiRes Like '*sftgsfghf*'))
UNION
SELECT productsURL
FROM Products
WHERE
((ProductCode Like '*sftgsfghf*') or (ProductTitle Like '*sftgsfghf*') or (ProductDescription Like '*sftgsfghf*') or (ProductSpec Like '*sftgsfghf*'))
UNION
SELECT resourcesURL
FROM Resources
WHERE
((ProductCode Like '*sftgsfghf*') or (DataSheet Like '*sftgsfghf*') or (HandBook Like '*sftgsfghf*') or (Drivers1 Like '*sftgsfghf*'))
I need to return HiRes, productsURL, resourcesURL. Any ideas how I can achieve this or is there another approach?