query to return nulls 1

[patrichek]

Hi, I have a query that gets its data from 2 related tables.
if there is no data in one of the fields (tblfilmstatus.status) in my query, it doesnt show up after i run it. How can i change this?

i make my queries in the design grid but here is my sql statement:

SELECT Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Count(Contacts.Source) AS CountOfSource, Contacts.[Date received], Contacts.Source
FROM Contacts INNER JOIN tblFilmStatus ON Contacts.ContactID = tblFilmStatus.ContactID
WHERE (((Contacts.Source)=[Enter Source:]) AND ((Contacts.[Date received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy])) OR (((Contacts.[Date received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy]) AND (([Enter Source:])='All'))
GROUP BY Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Contacts.[Date received], Contacts.Source;


sorry its so long, but i've had some help building this one from another post.

Thanks!

 

[lespaul]

change the join type:

SELECT Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Count(Contacts.Source) AS CountOfSource, Contacts.[Date received], Contacts.Source
FROM Contacts [b]LEFT[/b] JOIN tblFilmStatus ON Contacts.ContactID = tblFilmStatus.ContactID
WHERE (((Contacts.Source)=[Enter Source:]) AND ((Contacts.[Date received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy])) OR (((Contacts.[Date received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy]) AND (([Enter Source:])='All'))
GROUP BY Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Contacts.[Date received], Contacts.Source;

check out Understanding SQL Joins for an explanation of why.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[patrichek]

Thanks! works perfect! star !