Here is what I am trying to accomplish:

tables:

tbl_users
user_id autonumber
name text

tbl_readfile
readfile_id autonumber
readfiletitle text

tbl_signedoffreadfiles
signreadfile_id autonumber
user_id number
readfile_id number

So here is what I want: a query that lists readfiles that don't have an entry in the signedoff table for each user. For example if I have user 1 & 2, and 3 readfiles (1,2,3) and user 1 has signed off 1 & 2 and user 2 has signed off 2, the output should be something like:

user _id readfile_id
1 3
2 1
2 3

Does this make sense? It would also be equivalent to having a question bank and seeing which questions weren't answered by each user.

Thanks for your help.

you can do it in 2 steps:

a) create a query ( I named it qry_Select_All )

SELECT tbl_users.user_id, tbl_readfile.readfile_id
FROM tbl_readfile, tbl_users;

b) get the unmatached with:

SELECT qry_Select_All.user_id, qry_Select_All.readfile_id
FROM qry_Select_All LEFT JOIN tbl_signedoffreadfiles ON (qry_Select_All.readfile_id = tbl_signedoffreadfiles.readfile_id) AND (qry_Select_All.user_id = tbl_signedoffreadfiles.user_id)
WHERE (((tbl_signedoffreadfiles.signread_id) Is Null));


Note: you should not use "name" as a Field name, because is an Access reservedword.

Hi




<Edit: Didn't refresh before posting - thought it had gone unanswered :-)>

Edited by: LPurvis on Fri Dec 19 12:23:19 EST 2008.

Hey Leigh! Long time no post. Not sure where you were hiding.

Anyway, don't you need some sort of LEFT JOIN in there somewhere? I didn't look that close, but I think they're looking for unanswered questions.

Oh pants - what a clart.
I drafted it out conceptually with a Left Join and then when inserting the proper field names to make it "plug and play" I've clearly free typed it again. :-s

Though essentially redundant now - FWIW proper version below.



Thanks Walter.
(Been on a posting break - still am actually. Too busy with things otherwise. Catch you properly in the new year hopefully!)

Walter,

Thank you so much! Worked like a champ. This place is a wealth of help and information. Maybe someday I will know enough to contribute. Thanks again.

Jeff

Just for the record, I think it should be Leigh you should thank. I just pointed out an oversight in his code.

You sir are correct . Apparently I have a reading disorder. Thanks all!