Show record not scheduled

[SurveyorinVA]

Hi-
I have a query question that I am not sure how to handle.

I have a table called tblActivity. This table has two key fields:
ActivityID
ActivityName

I have a second table called tblCalendar. This table has the following
fields:
CalendarID
ActivityID
CalendarDate

I am now making a query based upon the criteria for the following:
1.) Activities that have been scheduled (has a record in tblCalendar)
2.) Activities that have not been scheduled (Does not have a record in
tblCalendar)

The part that is getting me, is how do I create a query to show only those
records from tblActivity that do not have a corresponding record in
tblCalendar?

Any thoughts would be greatly appreciated.
Thank you in advance,
Chris

 

[Clifford Bass]

Hi,

Probably the easiest way is to do a left join use the condition of Is
Null on the CalendarID:

select tblActivity.*
from tblActivity left join tblCalendar
on tblCalendar.ActivityID = tblActivity.ActivityID
where tblCalendar.CalendarID Is Null;

Start a new query and close the Show Table dialog. Change the query to
SQL View and paste the above into the window. Change back to Design View to
see what it looks like. Note that you can double-click on the joining line
to change its type between inner join, left join and right join.

An alternative method is to use the Not Exists clause:

select tblActivity.*
from tblActivity
where Not Exists
(select *
from tblCalendar
where tblCalendar.ActivityID = tblActivity.ActivityID);

Hope that helps,

Clifford Bass

 

[SurveyorinVA]

Hi Cliff-

Thank you for your insight. It works great. Have a great night!

Chris

Hi,

Probably the easiest way is to do a left join use the condition of Is
Null on the CalendarID:

select tblActivity.*
from tblActivity left join tblCalendar
on tblCalendar.ActivityID = tblActivity.ActivityID
where tblCalendar.CalendarID Is Null;

Start a new query and close the Show Table dialog. Change the query to
SQL View and paste the above into the window. Change back to Design View to
see what it looks like. Note that you can double-click on the joining line
to change its type between inner join, left join and right join.

An alternative method is to use the Not Exists clause:

select tblActivity.*
from tblActivity
where Not Exists
(select *
from tblCalendar
where tblCalendar.ActivityID = tblActivity.ActivityID);

Hope that helps,

Clifford Bass

Hi-
I have a query question that I am not sure how to handle.

[quoted text clipped - 21 lines]

Thank you in advance,
Chris

 

[Clifford Bass]

Hi Chris,

Good! You are welcome!

Clifford Bass