I have created a commission report based on monthly activity.
However, I would like for the report to also show when a surveyor has
laid an egg for the month. However, when I do the join to the ARBill
table, even tho I join as "all from employee and only those that match
from ARBill" it still drops the employees without AR records.
What did I miss?
Rosy
On Oct 24, 12:45 pm, Rosy
> I have created a commission report based on monthly activity.
> However, I would like for the report to also show when a surveyor has
> laid an egg for the month. However, when I do the join to the ARBill
> table, even tho I join as "all from employee and only those that match
> from ARBill" it still drops the employees without AR records.
>
> What did I miss?
>
> Rosy
Hi Rosy,
I am guessing that you are limiting your recordset by something - like
maybe the month? If you say 'show me all the activity in Oct', and
the employee had no activity in Oct they won't show up.
Now as to how to fix this, it gets a little more complicated based on
how you are getting the criteria for the report.
Based on the most common methods I have seen here is something you can
try.
Create a query on the ARBill table that limits the records to the
month you want to see - some people put a [please enter the month
number] in the criteria under month and it will allow you to enter it
when the data is requested. Let's call that query qArBill. Now
recreate your report dataset query the same way you did using the
outer join (show all employees), only don't limit it by date, and you
should be all set.
You may be thinking why not just use (for October) 10 or Null in the
criteria for the month - well if the employee had activity on another
month, they won't show.
If you really want to delve into fun sql, you can create a derived
table. They keep your query lists cleaner, but are hard to maintain
because Access messes up the code.
Select * FROM employee LEFT JOIN (SELECT * FROM ARBill WHERE
Month=[please enter the month number]) as aARBill on
employee.employeeID=aARBILL.employeeID
Good Luck
P