Fix Missing Status Data in a Microsoft Access Outer Join Query
Question details
The user needs an Access outer join query to show matching volunteer status data while retaining every volunteer.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A query joins All Volunteers to Confirmed Volunteers using the Last 4 and ID fields to retrieve 2018 status values.
- Observed behavior
- Volunteer names appear, but matching status fields are blank because the joined values may differ in data type or formatting.
Open both volunteer tables in Access and identify the Last 4 and ID fields used by the query. Check whether leading zeros are meaningful identifiers before changing either field's data type.
Align the Join Fields and Configure a Left Join
Use this approach when volunteer names appear but status values are blank because the join fields do not match.
In the Navigation Pane, right-click each table and select Design View. Check the Data Type for All Volunteers.[Last 4] and Confirmed Volunteers.[ID], and make them compatible. If leading zeros are significant, use text fields in both tables.
Open each table in Datasheet View and compare several expected matches. Look for extra spaces, different formatting, and leading zeros; a displayed format alone does not change the stored value.
Edit the affected values in Datasheet View or correct the source file and reimport it with appropriate field types. Remove unwanted spaces and preserve leading zeros where they are part of the identifier.
Open the query in Design View, double-click the join line between Last 4 and ID, and select the Join Properties option that includes all records from All Volunteers and only matching records from Confirmed Volunteers.
Click Run on the Query Design tab. Confirm that every volunteer remains visible and that volunteers with matching IDs show their 2018 status values.

Review Criteria That May Exclude Outer-Join Results
Check query criteria if volunteers disappear after the join is configured or if you need to limit the confirmed records being joined.
Review Volunteer Spreadsheets with WPS Office
WPS Office is a free, lightweight alternative for working with office files in a familiar interface. Open compatible spreadsheets to inspect volunteer identifiers and make migration from Microsoft Office easier. It does not fix or replace an Access database query.

Frequently Asked Questions
Why does my Access left join show names but blank status fields?
The join may not find matching IDs because of different data types, spaces, or leading zeros. Open both tables in Datasheet View to compare values, and use Design View to check their field types.
Should volunteer IDs with leading zeros be stored as text?
If leading zeros are part of the identifier, use text fields on both sides of the join. Converting a numeric value to text does not automatically restore zeros that were lost during import.
How do I keep all volunteers in the query results?
In Query Design View, double-click the join line and choose the Join Properties option that includes all records from All Volunteers and only matching records from Confirmed Volunteers.
Can a query criterion remove unmatched volunteers?
Yes. A criterion on a Confirmed Volunteers field can exclude rows with no match. Review the Criteria row in Design View, or filter the confirmed records in a separate saved query before joining.
Does WPS Office repair an Access outer join?
No. Configure and troubleshoot the join in Microsoft Access. WPS Spreadsheets can help you inspect compatible spreadsheet source files, but it does not repair the Access query.




