ACCESS FEED: RE: [MS_AccessPros] Query driving me nuts

Rabu, 31 Agustus 2011

RE: [MS_AccessPros] Query driving me nuts

Hi Lee

Try this:

Select tblEmployee.EmployeeName, tblForms.FormName
FROM tblEmployee, tblForms
WHERE tblEmployee.EmployeeID not in
(Select tblEmployeeForms.EmployeeID from tblEmployeeForms
where tblEmployeeForms!FormID=tblForms!FormID);

Note that there is no join between the two tables!

Best regards,
Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lee
Sent: Thursday, 1 September 2011 11:30
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Query driving me nuts

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