Hi, I will see if I can get someone with SQL experience to review.  Thank you.

I've done it in an access database before but had to use UNION ALL

If this is for the Providex version UNION Queries & Subqueries are not supported.

Unions are fine but using UQE for Providex.  I'm seeing if we can check the code.  Hopefully we can have an answer on that too. 

Sandra - I have a word doc titled "Universal Query Engine" for the Providex database.

At the end of the doc it states:

Current Limitations

- UNION Queries are not supported

-  Subqueries are not supported

-  Column names MUST be prefixed with the Table/Alias name eg. Customer.Name where  Customer is a table of customers.

-  Certain string concatenations will fail if the data types of the operands are not strings. It’s always safer to cast an operand to a string to be safe. Eg. The expression ISNULL(Order.Total, 0) + ‘ is the order value’ will fail because Order.Total is not a string. 

Good point. I will confirm that, however, it might not be supported by Sage technical support since it is used more for customized reports but not necessarily means does not work. 

So the code was tested against 100 Providex and we received an error as well.  One suggestion, however, is to see if you can put each query into a separate container, create two separate reports then use a union report outputting to the same sheet to combine them.

Thank you for the response Sandra. That's what I ended up doing.