Fix Microsoft Access Adding Text Fields Instead of Numeric Scores
Question details
The user needs to add score fields numerically in a Microsoft Access expression instead of joining their values as text.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A table stores subject scores in separate fields, and the user wants to calculate a total for each student.
- Observed behavior
- The expression places field values side by side instead of adding them, which can occur when the fields contain text values.
Open the table in Design View and check the data type of each score field. Review the existing values for nonnumeric text before changing a field type, because incompatible values may be lost during conversion.
Change score fields to Number and add them in a query
Use this approach when each subject is already stored in a separate table field and you need a total for those fields.
In the Navigation Pane, right-click the score table and select Design View. Check whether each score field is set to Short Text or Number.
Open the table in Datasheet View and review score entries for nonnumeric text. Correct those entries before converting the fields; keep a copy of the original table if you need to preserve the existing values.
Return to Design View, select each score field, and change its Data Type to Number. Choose a suitable Field Size, such as Long Integer for whole scores or Double for decimal scores, and save the table.
Select Create > Query Design, add the score table, and enter an expression such as TotalScore: Nz([Math],0) + Nz([Science],0) in a blank Field cell. Replace the sample names with your actual score field names.
Select Run on the Query Design ribbon and check that the TotalScore column shows numeric sums.

Normalize student scores and sum them by student
Use a separate score record for each student and subject when you want a more flexible structure for reporting totals.
Calculate exported student scores in WPS Spreadsheets
For spreadsheet-based score calculations, WPS Office is a free, lightweight alternative with a familiar interface and Excel format support. Export Access data to a supported spreadsheet or CSV file to move it into WPS; WPS does not fix Access database fields or replace Access relationships and queries.

Frequently Asked Questions
Why does Access join my scores instead of adding them?
The score fields may be stored as text. Right-click the table in the Navigation Pane, select Design View, and check each score field's Data Type.
How do I add two subject fields in an Access query?
In Create > Query Design, add the table and enter an expression such as TotalScore: Nz([Math],0) + Nz([Science],0) in a blank Field cell. Use your actual field names and ensure the fields are numeric.
What happens if a score is blank?
A null value can make an addition expression return null. Use Nz([FieldName],0) in the query expression if a missing score should count as zero.
How can I total scores stored in separate rows?
In a query's SQL View, use SELECT StudentID, Sum(Score) AS TotalScore FROM StudentScores GROUP BY StudentID; Replace the table and field names if yours differ.
Can WPS Office repair my Access database field types?
No. Change the field types in Microsoft Access. You can export data from Access to a spreadsheet or CSV file if you want to calculate scores in WPS Spreadsheets.




