List forms a user does not have access to - comparing two tables

[scott0_1]

SQL not my friend today!



I have two tables:

• tblUserFormAccess which has ID, FormID and UserID
• tblFormDescription which has ID (fk is FormID), FormName and FormDescription

I use tblUserFormAccess to control what forms users have access to. For example, 1 | 10 | skennedy

I'm trying to create a list of forms in tblFormDescription that a user does not have access to. I've been staring at joins and exists and ins for the last hour in different examples, but I can't figure it out.

Any ideas?

p.s., I really appreciate all the help everyone on this forum offers. I get a lot of help for what probably seems like very mundane problems!

[Micron]

did you try the Unmatched query wizard? It should create the correct join for you based on your input.
EDIT
Although I think you have the fk field in the wrong table. The forms table is a one side. The permissions table is likely a junction (many side) between tblUsers (another one side) and the forms table. The form ID should be the fk in the permissions table.

[June7]

Need a dataset of all possible user/form pairs. This can be generated with a Cartesian query. Every record in each table will associate with every record of other table. Do you have a tblUsers?

SELECT tblUsers.ID, tblFormDescription.ID FROM tblUsers, tblFormDescription;

Then build a FindUnmatched query with the Cartesian dataset and tblUserFormAccess using compound join.

[scott0_1]

Need a dataset of all possible user/form pairs. This can be generated with a Cartesian query. Every record in each table will associate with every record of other table. Do you have a tblUsers?

SELECT tblUsers.ID, tblFormDescription.ID FROM tblUsers, tblFormDescription;

Then build a FindUnmatched query with the Cartesian dataset and tblUserFormAccess using compound join.

I didn't feel so bad once I saw the terms "Cartesian query" and "compound join"! Worked perfectly!

Although I think you have the fk field in the wrong table. The forms table is a one side. The permissions table is likely a junction (many side) between tblUsers (another one side) and the forms table. The form ID should be the fk in the permissions table.

You're completely right. I wrote that after 10 hours of sitting in front of the computer and a couple of which were dealing with this! It was a mixture of tiredness/frustration/laziness! (mostly the latter).

Thanks for the help!