Trouble with inner join query for report 2

[KerryL]

My main data table is populated in part by a number of cbo's that pull data from a lookup table. I'm trying to build a query that will be used for a report listing all records within a date range.

IOW, all I want the query to do is list all records within the date range. However, it keeps filtering the records by more than that and I don't understand why.

Here's my sql:

SELECT tblWARNData.NoticeNo, tblWARNData.EntryDate, tblWARNData.CoName, tblLookupData.LUDescr AS [Incident Descr], tblLookupData_1.LUDescr AS [Notif Descr], tblLookupData_2.LUDescr AS [Layoff Reason 1], tblLookupData_3.LUDescr AS [Layoff Reason 2], tblWARNData.RespType
FROM (((tblWARNData INNER JOIN tblLookupData ON tblWARNData.IncidType = tblLookupData.LookupID) INNER JOIN tblLookupData AS tblLookupData_1 ON tblWARNData.NotifType = tblLookupData_1.LookupID) INNER JOIN tblLookupData AS tblLookupData_2 ON tblWARNData.LayfReas1 = tblLookupData_2.LookupID) INNER JOIN tblLookupData AS tblLookupData_3 ON tblWARNData.LayfReas2 = tblLookupData_3.LookupID
WHERE (((tblWARNData.EntryDate) Between CDate([forms]![frmRptCriteria]![txtBeginDate]) And CDate([Forms]![frmRptCriteria]![txtEndDate])));

 

[lespaul]

when you use inner joins you have to have a record in EVERY table in order to get a result. So if LayfReas1 is null for a record, it won't show up in the query.

If you need to show information when there's only data in one of the tables you need to change to a LEFT or RIGHT join. See the Understanding Joins article linked below for more details.

Leslie

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

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

Demo of Leslie's advice:

SELECT W.NoticeNo, W.EntryDate, W.CoName, I.LUDescr AS [Incident Descr], N.LUDescr AS [Notif Descr], L1.LUDescr AS [Layoff Reason 1], L2.LUDescr AS [Layoff Reason 2], W.RespType
FROM (((tblWARNData AS W
LEFT JOIN tblLookupData AS I ON W.IncidType = I.LookupID)
LEFT JOIN tblLookupData AS N ON W.NotifType = N.LookupID)
LEFT JOIN tblLookupData AS L1 ON W.LayfReas1 = L1.LookupID)
LEFT JOIN tblLookupData AS L2 ON W.LayfReas2 = L2.LookupID
WHERE W.EntryDate Between CDate([Forms]![frmRptCriteria]![txtBeginDate]) And CDate([Forms]![frmRptCriteria]![txtEndDate])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KerryL]

Thank you both. I modified the Join Properties in my query design and now I've getting the results I expected.