Selecting two fields from each union query

[toyt78]

The following SQL in Access 2000 works great for me where it finds all the hits for both tables for FirstField and SecondField:

SELECT FirstField as Field1, 'Query1' as QueryUsed
FROM tblOne
WHERE FirstField like 'Ohio'
UNION ALL
SELECT SecondField as Field1, 'Query2' as QueryUsed
FROM tblTwo
WHERE SecondField like 'Ohio'

But now I want to fetch another table field from each select statement and I tried the following but it doenst work:

SELECT FirstField as Field1, idField as Field2, 'Query1' as QueryUsed
FROM tblOne
WHERE FirstField like 'Ohio' 
UNION ALL
SELECT SecondField as Field1, id2Field2 as Field2, 'Query2' as QueryUsed
FROM tblTwo
WHERE SecondField like 'Ohio'

Please advise.







<cfif QueryUsed eq "Query1">

<cfelse>
<!--- Query2 --->

</cfif>

 

[lespaul]

what about your second query doesn't work?

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[earthandfire]

Try leaving out the AS statements in the UNION part ofthe query

 

[cmmrfrds]

Data types need to match. Is the data type of id2Field2 the same as idfield? If not you may need to cast the second field to the matching type.