I have a table tblCommission with a lookup field. Since we can pay commission to either an entity or an employee, the lookup field uses a union:
SELECT EntityID, Entity, "tblSaleEntities" AS tbl
FROM tblSaleEntities
UNION ALL
SELECT EmployeeID, Employee, "tblEmployees" AS tbl
FROM tblEmployees
WHERE ((Current = True) AND (SalesTeam = True));
The field is bound to column 1, but since it's taking from two tables, an entity and an employee could share the same ID number, so when I report I want to filter based on the bound column as well as the [tbl] column. Is this possible if it's not the bound column or do I need to figure out how to store the information in a new field in the table?
If it is possible, how do I reference it in a report query or VBA where clause?
Thank you in advance!!