I have a query that performs the following function: Counts Actual Active Employees sorted by Region, CostCenter, FinanceJobCode, FTPTStatus and AuthStatus. Calculations are performed by Month (using a Roster Month end date).
I created a query called "YTDAvgHCQuery" that pulls separate queries performed for each month with counts for each month. I have the queries joined by CostCenter, FinanceJobCode, FTPTStatus and AuthStatus.
My problem is: If a new cost center comes into play starting in June, since the cost center did not exist in May, it will not display in the query. If I add a CostCenter List table to the query and join the CostCenter field to the queries contained in the query and set the Join Property to include all records in the CostCenter list table and only those that match to the query, I receive an "ambiguous outer join" error message.
How can I have the final YTDAvgHCQuery include CostCenters that exist for one month and not for another?
I hope I provided enough information. If you have any questions, please let me know.