Result in Query not showing because field not filled in. (Joined tables)

[enquiries]

Hi there,



I have a query, that I have a criteria to show appointments in the past (< Date()) but one result doesn't show up although the appointment end date is a past date, it only shows up when I fill in a field that is in another table that is joined and part of the query. But there's no criteria there for it to not be null.

A bit mixed up.

Thanks for any help Much appreciated.

[June7]

Does this query use INNER JOIN? INNER JOIN requires related records in both tables. Use LEFT or RIGHT (outer JOIN) instead.

Post the query sql statement for analysis.

[enquiries]

Yes, I thought that i had something to do with the join.........below is the SQL statement
SELECT [Applicant Information].[First Name], [Applicant Information].[Last Name], BoardMemberPositions.Company, BoardMemberPositions.[Position Held], BoardMemberExperienceApplicant.[Date of Appointment], BoardMemberExperienceApplicant.[End Date of Appointment], BoardMemberPositions.StateOwnEnterprise, CompletedOrEndedBoardAppointments.[Reasons For Ended Appointment], CompletedOrEndedBoardAppointments.[Number of Directors in Company], CompletedOrEndedBoardAppointments.[Number of Employee in Company]
FROM BoardMemberPositions INNER JOIN (([Applicant Information] INNER JOIN BoardMemberExperienceApplicant ON [Applicant Information].ApplicantID = BoardMemberExperienceApplicant.ApplicantID) INNER JOIN CompletedOrEndedBoardAppointments ON [Applicant Information].ApplicantID = CompletedOrEndedBoardAppointments.ApplicantID) ON BoardMemberPositions.DirectorshipExperienceID = BoardMemberExperienceApplicant.DirectorshipExperie nceID
WHERE (((BoardMemberExperienceApplicant.[End Date of Appointment])<Date()));

Is there anything else that using an outer join will effect besides this? Thanks again for your help

[June7]

INNER or OUTER join, if there are multiple 1-to-many or many-to-many relationships in the query, results might not be what you want.