Others

How to Show All Records from One Table in an Access Query

Aamir Naveed Akram Sep 30, 2026 868 views

Question details

The user needs to display every record from a primary table and only the matching records from a related second table in a Microsoft Access query.

How to Show All Records from One Table in an Access Query

Product: Microsoft Access
Device & OS: not provided
Scenario: Designing a relational database query where a primary table's records must be fully displayed, regardless of whether there are matching records in a joined table.
Observed behavior: By default, Access creates an INNER JOIN between linked tables, which only returns records that exist in both tables, omitting primary records that lack corresponding entries.

Before you start

Ensure both tables are already added to your Access database and share a common field (such as an ID number) that can be used to link them together in the Query Design view.

Solution 1Recommended

Change Join Properties to Create a LEFT JOIN

The most straightforward way to show all records from your primary table is to modify the join properties directly within the Access Query Design view.

By modifying the visual join line between two tables, Access automatically rewrites the underlying SQL code to convert the standard INNER JOIN into a LEFT JOIN. This ensures that no data from your primary table is hidden.

Open Query Design

Open your Access database, navigate to the Create tab on the ribbon, and click on Query Design.

Add Tables

Add the two tables you want to query to the design canvas. If a relationship isn't already established, drag the common field from Table 1 to Table 2 to create a join line.

Access Join Properties

Double-click the physical join line connecting the two tables to open the Join Properties dialog box.

Select the Correct Option

Select the option that states 'Include ALL records from [Table 1] and only those records from [Table 2] where the joined fields are equal.' Click OK to apply the changes.

Run the Query

Click the Run button (the red exclamation mark) in the Design tab to execute the query and view the results.

Change Join Properties to Create a LEFT JOIN

Filtering Warning: If you apply a filter (criteria) to the second table, it may inadvertently hide unmatched records from the first table. To prevent this, apply the filter in a subquery or within the join condition instead of the main query criteria.

Solution 2

Write a LEFT JOIN manually in SQL View

For advanced users familiar with SQL, you can directly edit the query's SQL statement to apply a LEFT OUTER JOIN.

Free Microsoft Office alternative

Looking for a Lightweight and Free Office Suite?

While WPS Office does not include a dedicated relational database management application like Microsoft Access, it offers robust alternatives for Word, Excel, and PowerPoint. If you primarily manage data in spreadsheets, WPS Spreadsheet can easily handle complex data lookups, pivot tables, and large-scale data analysis without the need for complex SQL queries.

1. Download and Install: Visit the official WPS website to download and install WPS Office for free.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet and open your exported database CSV or Excel files.
3. Merge Your Data: Use built-in data functions like VLOOKUP or XLOOKUP to replicate LEFT JOIN behavior and merge your datasets effortlessly.

Highly compatible with Microsoft Office formats, ensuring seamless viewing and editing of .xlsx, .docx, and .pptx files.Perform database-like data merging and lookups effortlessly using advanced functions like VLOOKUP and XLOOKUP.Enjoy a lightweight, fast-loading office suite that runs smoothly on Windows, Mac, Linux, and mobile devices.

Frequently Asked Questions

Why does my Access query only show matching records by default?

By default, Access creates an INNER JOIN when you link two tables. An INNER JOIN strictly requires matching values in both tables to display a record, filtering out any unmatched data from your query results.

What is the exact difference between an INNER JOIN and a LEFT JOIN?

An INNER JOIN returns only the records that have matching values in both tables. A LEFT JOIN returns all records from the left (primary) table, and only the matched records from the right (secondary) table, leaving blank fields (nulls) for unmatched rows.

How do I filter the second table without hiding records from the first table in Access?

If you place criteria directly on the second table in a LEFT JOIN, Access treats it like an INNER JOIN and hides unmatched records. To prevent this, apply the criteria to the second table within a separate subquery before joining it to your main table.

Can I show all records from both tables simultaneously in Access?

Yes, this is known as a FULL OUTER JOIN. However, Microsoft Access does not support FULL OUTER JOIN directly in its SQL syntax. You must create a LEFT JOIN query, a RIGHT JOIN query, and then combine their results using a UNION query.

Office Quick Solutions