How to Show Only Selected Fields in a Microsoft Access Query
Question details
The user wants to display only specific fields in a Microsoft Access query, but the query is returning all fields from the table.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Running a database query built with the Access Query Wizard or Design Grid.
- Observed behavior
- The query results display every field from the table instead of just the selected fields due to a hidden wildcard character in the SQL statement.
Ensure you have the correct Access database open and switch your query to Design View to verify which fields you originally intended to display.
Remove the Wildcard (*) in SQL View
Use the SQL View to find and delete unintended wildcards that cause Access to return all table columns.
Often, the Access Query Wizard automatically includes a wildcard character (*) in the underlying SQL code, particularly if you add a field strictly for sorting. This wildcard forces the query to return every field in the table, overriding your design grid selections.
Right-click the problematic query in the Access Navigation Pane and select 'SQL View' from the context menu. Alternatively, open the query, go to the Home tab, click the View drop-down arrow, and choose 'SQL View'.
Look at the SQL statement for an asterisk (*) immediately following the SELECT keyword, such as 'SELECT *, FieldName FROM TableName'.
Delete the asterisk (*) and the comma following it, leaving only your specific field names (e.g., 'SELECT FieldName FROM TableName').
Click the 'Run' button (the red exclamation mark) on the Query Design tab to execute the query and verify that only your selected fields are now displayed.
Manage Your Exported Data Easily with WPS Office
While WPS Office does not include a database management tool like Microsoft Access, it offers a powerful Spreadsheet application perfect for filtering, analyzing, and formatting exported Access data. Experience a highly compatible, lightweight, and free Office suite.
- 1. Export Access Data: Export your query results from Microsoft Access as an Excel Workbook (.xlsx) or CSV file.
- 2. Open with WPS Spreadsheet: Launch WPS Office and open your exported data file to instantly view the selected fields.
- 3. Filter and Analyze: Use the Data tab in WPS Spreadsheet to apply filters, sort columns, or create PivotTables effortlessly.
Frequently Asked Questions
Why does my Access query show all fields when I only added one to the design grid?
This typically happens when the Query Wizard automatically inserts a wildcard (*) into the SQL code. This wildcard instructs Access to return all columns in the table, overriding the specific field you selected in the design grid.
How do I hide a field in Access query results without deleting it?
In the query Design View, simply uncheck the 'Show' box beneath the field you want to hide. The field can still be used for sorting or defining criteria without appearing in the final results.
Can I prevent the Query Wizard from adding wildcards automatically?
The Query Wizard's behavior is automated based on how you build the query. The best prevention is to build queries directly in Design View or manually verify the SQL View immediately after using the wizard.
