Union within query builder
Can you do a union with the query builder? I need to do a summary report of
counts of records by salesperson where the salesperson ID could be in one of
two fields. The record is counted for the salesperson if their ID is in
either of the fields. I can do this using a union of two selects on the
same table but I can only see how by directly editing the SQL. But doing
that doesn't allow me to set up an autosearch field to limit the records by
a date range. Can this be done in the query editor or is there another way
to do it?
Michael Gregg
counts of records by salesperson where the salesperson ID could be in one of
two fields. The record is counted for the salesperson if their ID is in
either of the fields. I can do this using a union of two selects on the
same table but I can only see how by directly editing the SQL. But doing
that doesn't allow me to set up an autosearch field to limit the records by
a date range. Can this be done in the query editor or is there another way
to do it?
Michael Gregg
This discussion has been closed.
Comments
The Query Designer does not support SQL Unions. You would need to manually
edit the SQL Text.
Some database products support an inline if statement. That might help
because you can have a field like this:
IIF(expression, value1, value2)
Check the documentation for the database product that you are using.
If you must use a Union and want to use AutoSearch you will have to handle
it manually. Here is an example that you can download..
www.digital-metaphors.com/tips/EditSQLAndSearch.zip
--
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com