Entire Query Fails if certain field is empty 1

[BullHalseyUSN]

Greetings, Experts:

Employee Info Database. I have a table [NAME AND ADDRESS] that is related to a table [COLLATERAL DUTY] in a 12M through key MemberID.

I am trying to write a query that retrieves various member info from the [NAME AND ADDRESS] table and also looks in the [COLLATERAL DUTY] table to retrieve any duty the member may have.

In plain English, the query would be something like "tell me the member's salutation, and any comment, his number, and tell me what duty, if any, the member has performed."

Here is the actual Select:

SELECT [NAME AND ADDRESS].SALUTATION, [NAME AND ADDRESS].Comment, [NAME AND ADDRESS].Extension, [COLLATERAL DUTY].Duty
FROM [NAME AND ADDRESS] INNER JOIN [COLLATERAL DUTY] ON [NAME AND ADDRESS].MemberID = [COLLATERAL DUTY].MemberID
WHERE ((([NAME AND ADDRESS].DeptHead)=2) AND (([NAME AND ADDRESS].DeptID)=2));

However, the query FAILS if there is no [COLLATERAL DUTY].Duty. The query WORKS if there is [COLLATERAL DUTY].Duty.

Hopefully I've been clear that I'd like to rewrite the query so that it returns all the other info even if there is nothing in [COLLATERAL DUTY].Duty

How can I make this thing work, please?

Many thanks! Have a great Access Day! BH

 

[Golom]

Change your "INNER JOIN" to "LEFT JOIN" that will retrieve all the records in [NAME AND ADDRESS] even if there are no records in [COLLATERAL DUTY]

 

[BullHalseyUSN]

Jeepers. THAT was easy (for you).

Thanks.

Bravo Zulu