In access, query only shows results when there are records in EVERY field

Question

I am creating a database that tracks employers and data connected to an employer.

I have a parameter query set up that pulls records related to a selected employer.

Currently, if one of the fields has no records, but some of the fields do, when I run the query it comes up blank. I would like it to show all the records available related to the employer.

SELECT 
    [Employer Contact].Employer,
    [Employer Type/Intern Functions].Type,  
    [Employer Type/Intern Functions].[Intern Functions/Responsibilities
    [Internship Notes].Date,
    [Internship Notes].Specialist,
    [Internship Notes].Notes
FROM 
    (
    [Employer Contact] 
    INNER JOIN [Internship Notes] 
        ON [Employer Contact].[Employer] = [Internship Notes].[Employer]
    ) 
    INNER JOIN [Employer Type/Intern Functions]
        ON [Employer Contact].[Employer] = [Employer Type/Intern Functions].[Employer]
WHERE 
(
    (
        ([Employer Contact].Employer)=[Forms]![Employer Record Search]![cboChooseEmp])
    ) 
OR (
    (([Forms]![Employer Record Search]![cboChooseEmp]) Is Null)
);

Is this an issue in relationships? The criteria? How do I structure my query so that it shows records that have a value when some of the records are null?

Do you mean some of the recrods in other tables are null? If so change your INNER join to LEFT JOIN — Brad, Dec 28 '18 at 15:11
Yes! This fixed it! What is the difference between the INNER and LEFT JOIN? — Rebecca Bannasch, Dec 28 '18 at 15:17
Inner join requires records in both tables to exist. Left join requires only records in first table to exist, and if they exist in second table, they will show up, if they dont, it will return NULL for all those values in that table — Brad, Dec 28 '18 at 15:20

score 1 · Accepted Answer · answered Dec 28 '18 at 15:15

Since the Employer Contact table seems to be your parent table from which the records are being selected, then you should LEFT JOIN this table to the two child tables Employer Type/Intern Functions and Internship Notes such that records from the parent table are always displayed, regardless of whether the child tables contain associated records.

For Example:

SELECT 
    [Employer Contact].Employer, 
    [Employer Type/Intern Functions].Type,  
    [Employer Type/Intern Functions].[Intern Functions/Responsibilities], 
    [Internship Notes].Date, 
    [Internship Notes].Specialist, 
    [Internship Notes].Notes
FROM 
    (
        [Employer Contact] LEFT JOIN [Internship Notes] ON 
        [Employer Contact].[Employer] = [Internship Notes].[Employer]
    ) 
    LEFT JOIN [Employer Type/Intern Functions] ON 
    [Employer Contact].[Employer] = [Employer Type/Intern Functions].[Employer]
WHERE 
    [Employer Contact].Employer = [Forms]![Employer Record Search]![cboChooseEmp] OR 
    [Forms]![Employer Record Search]![cboChooseEmp] IS NULL;

In this way, the query will only return no records if a record is not found in the Employer Contact table.

To understand more about the differences between the various join types, refer to this question.

In access, query only shows results when there are records in EVERY field

1 Answers1