-------------------------- Table1: Activities ActivityID ActivityDesc -------------------------- Table2: StaffActivities StaffID ActivityID IsVisible -------------------------- Table3: Data Date StaffID ActivityID Value --------------------------On the form there is a grid that i want to show all of the values in activities and all of the values from Table3 where the StaffID, ActivityID and Date match for the current selected date. Right now I am having to use 3 separate queries and code manipulation to achieve this. I keep thinking there must be a better way.
SELECT Activity.ActivityDesc, Activity.ActivityDesc, Data.Value, StaffActivity.IsVisible FROM Activity INNER JOIN (StaffActivity INNER JOIN Data ON (Data.ActivityID = StaffActivity.ActivityID) AND (StaffActivity.StaffID = Data.StaffID)) ON Activity.ActivityID = Data.ActivityID WHERE (((Data.StaffID)=5))This of course only gives me values where there is an entry in the StaffActivities table. I want the users to always see the full list of Activities (in case someone adds a new one in after the fact) reagardless of if they have gone into their profile to mark activities as visible or invisible.