I figured it out.
I alias'd a cartesian table, then left joined the table with the forms the employees have filled out, and got rid of the nulls
SELECT A.EMPLID, A.FormType, C.FullName
FROM ((SELECT tblEmployee.EMPLID, tblFormTypes.FormType
FROM tblEmployee, tblFormTypes) AS A
LEFT JOIN tblEmployeeForms AS B ON (A.FormType= B.FormType) AND (A.EMPLID= B.EMPLID))
LEFT JOIN tblEmployee as C ON A.EMPLID= C.EMPLID
WHERE B.EMPLID Is Null AND B.FormType Is Null
--- In MS_Access_Professionals@yahoogroups.com, "Lee" <leevt99@...> wrote:
>
> Hi,
> I don't know why I can't get this...
>
> I have a list of forms and a list of employees. Each employee has to fill out each form.
> So we have the employee table (tblEmployee), the form info table (tblForms), and then the "many" table (tblEmployeeForms) that tells you which employee has filled out which form.
>
> There are 7 forms, it's easy enough to figure out which forms the employees HAVE filled out, but I can't figure out which ones they HAVEN'T filled out.
>
> So if Bob filled out forms A, B, and C, but not D-G, it's easy for me to show
> Bob A
> Bob B
> Bob C
>
> What do I need to do to show the missing:
> Bob D
> Bob E
> Bob F
> Bob G
>
> And what if Bubba hasn't filled out F and G? I need it to work for multiple employees in the same query:
> Bob D
> Bob E
> Bob F
> Bob G
> Bubba F
> Bubba G
>
> I've tried subqueries, exist statements, different joins, aliasing a query as a table, union, I can't get anything to work! I have a strange feeling I'm over-complicating it in my head =/
>
> Thanks
> -Lee
>
Tidak ada komentar:
Posting Komentar