Calculating a field when a support Group By Field can be both blank and not blank

[klkeahey]

I have query that I run with the following fields:
Manager
Emply ID
Sales
Units
Commission

My query calculates the commission based upon the total sales and units per employee. The problem is the same employee can have sales attributed to a manager and sales not attributed to a manager. When this happens, the unit and sales total double.
Is it possible to have the query correctly calculate unit totals, sales total and the commission regardless of whether the manager field is Null or has data in it?
I should have the Emply ID and Manager field are "Group By" fields.

[John_G]

If it doesn't matter whether Manager is blank or not, you could just leave it out of the query altogether.

Without seeing the query, this may not be correct, but Units and Total Sales should not "double" - there will be separate (but very likely different) values for the rows with and without a manager. That depends on how you calculated the Units and Total Sales values, though.

If you do need the Manager value in the query, you could use a DLookup to get the manager for each employee from another table, and group by that expression too.

[klkeahey]

Thanks John.

I definitely need the managers as part of the query. The problem is managers are rearranged or assigned through out the month; therefore, the DLookup doesn't help either.

[John_G]

OK. But if there can be multiple values of "Manager" (including blank) for each employee, then your query will contain multiple rows for each employee as well; that is how "Group By" works.

What is your requirement exactly? Do you want total units & sales by employee, or do you want it broken down by Employee+Manager?

When you run the query with manager in it, for employees appearing more than once are the values in Units and Sales different on each row, or are they the same? If they are the same, I suspect your query has an error in it.