Tricky Access Query - Please Help!!!

[office drone]

I'm working with an Access 2003 database that records users'
activities. There are two tables (related to this question): a USERS
table that records the users, and an ACTIVITIES table which the users
record their activiteis in. The two tables are connected by the unique
user ids, USER_ID, of each user. I need to create a query that will
show me which users have NOT entered any activities. (ie a query that
shows the USER_IDs that are found in the USERS table, but not in the
ACTIVITIES table) Is there any way to do this, because I'm completely
stumped.


As well, in an ideal world I would be able to filter this down so that
it shows which users haven't entered activities for a specific
date/month... (there's a date field in every record in the ACTIVITIES
table)... But that would be an added perk!

If you can offer any advice or direction, it would be greatly
appreciated.

Thanks!

 

[Bob L.]

Just do a query containing all of the records from USERS with Is Null as the
criteria for the USER_ID in the activities table

Bob L.

 

[office drone]

If only it were that simple... I've tried making the criteria for the
USER_ID in the Activity table Is Null, I've tried making the USER_ID in
the Users table Is Null... all I get is a blank query table back. Below
is how you would see the tables (simplified) in the database:

USERS table:

USER_ID NAME TEAM....
1 xxx xxx
2 xxx xxx
3 xxx xxx
4 xxx xxx
5 xxx xxx
6 xxx xxx
7 xxx xxx
8 xxx xxx
9 xxx xxx
10 xxx xxx
11 xxx xxx

ACTIVITIES table:

USER_ID TASK WEEK_END....
1 xxx xxx
1 xxx xxx
3 xxx xxx
4 xxx xxx
5 xxx xxx
5 xxx xxx
5 xxx xxx
8 xxx xxx
9 xxx xxx

So in theory, there aren't null values for the users that haven't
entered any tasks yet. Ideally, I need to show a query that would only
show me Users 2, 6, and 7 because they aren't in the Activities table.
As well, it might help to know that there is a one to many relationship
between teh User table and Activity table.

Any other suggestions?

 

[office drone]

If only it were that simple... I've tried making the criteria for the
USER_ID in the Activity table Is Null, I've tried making the USER_ID in
the Users table Is Null... all I get is a blank query table back. Below
is how you would see the tables (simplified) in the database:

USERS table:

USER_ID NAME TEAM....
1 xxx xxx
2 xxx xxx
3 xxx xxx
4 xxx xxx
5 xxx xxx
6 xxx xxx
7 xxx xxx
8 xxx xxx
9 xxx xxx
10 xxx xxx
11 xxx xxx

ACTIVITIES table:

USER_ID TASK WEEK_END....
1 xxx xxx
1 xxx xxx
3 xxx xxx
4 xxx xxx
5 xxx xxx
5 xxx xxx
5 xxx xxx
8 xxx xxx
9 xxx xxx

So in theory, there aren't null values for the users that haven't
entered any tasks yet. Ideally, I need to show a query that would only
show me Users 2, 6, and 7 because they aren't in the Activities table.
As well, it might help to know that there is a one to many relationship
between teh User table and Activity table.

Any other suggestions?

 

[Bob L.]

Are you sure you have the join properties set so that it will include all
of the records for users and only the matching records for activities?