Others

Exclude Calculated Fields from an Access Make-Table Query

Phi Hung VoPhi Hung Vo Nov 20, 2026 869 views

Question details

The user needs to create an Access table from selected source fields without copying calculated fields.

Product
Microsoft Access
Device & OS
not provided
Scenario
A Make-Table query uses SELECT * to copy fields from a source table that includes calculated columns.
Observed behavior
Access reports that a calculated column cannot be saved without a valid expression.
Before you start

Identify the source fields the destination table needs, including any IDs used for joins. Check whether the destination table already exists: a Make-Table query can replace an existing table, while an Append query adds records to it.

Solution 1Recommended

Select only the required fields in the Make-Table query

Replace SELECT * with an explicit field list to leave calculated source fields out of the new table.

Access SQL does not provide a SELECT * syntax that excludes specific fields. List each field you want to copy, and calculate derived values in a separate query when needed.

1
Open the query in SQL View

In Access, right-click the Make-Table query in the Navigation Pane and choose SQL View.

2
Replace the wildcard

Replace SELECT * with the required source fields. For example, use: SELECT Game_ID, Roster_ID, Player_ID INTO Game_Stats_Copy FROM Game_Stats; Add any required statistics to the field list and use your actual table and field names.

3
Check any criteria

If the query filters for an exact Player_ID, use = rather than Like. For a numeric ID, an example is WHERE Player_ID = 22; for a text ID, use WHERE Player_ID = "22".

4
Run the query

On the Query Design tab, click Run and review the destination table to confirm it contains the selected fields and records.

Check the destination name: A Make-Table query creates a new table and can replace an existing table with the same name. Use an Append query if you need to add records to an existing table instead.
Free Microsoft Office alternative

Work with Office documents and spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for documents and spreadsheets, with a familiar interface and Microsoft Office format compatibility to help you migrate existing files. It does not fix Access queries or replace Access database functionality.

Review and analyze tabular data in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and Word DOCX filesUse a familiar interface for everyday Office tasksGet started with a free, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can I exclude a few fields while keeping SELECT * in Access?

Access does not support excluding named fields from SELECT *. In SQL View, replace the wildcard with a list of the fields you want in the destination table.

Do calculated values have to be stored in the new table?

No. You can omit the calculated fields from the Make-Table query and create expressions in a separate query when you need the values.

Should I use Like or = to filter a specific Player_ID?

Use = for an exact match. In SQL View, use a number without quotes for a numeric field, such as Player_ID = 22, or quotes for a text field, such as Player_ID = "22".

How do I add records without recreating the destination table?

Create an Append query in Query Design, select the existing destination table, and map the required source fields in the Append To row before clicking Run.