Others

How to Create a Microsoft Access Query for Inventory Value Sorted Descending

Olivia Miller Sep 27, 2026 869 views

Question details

The user needs to create an SQL query in Microsoft Access to calculate the total inventory value and sort the results from highest to lowest.

How to Create a Microsoft Access Query for Inventory Value Sorted Descending

Product: Microsoft Access
Device & OS: not provided
Scenario: Calculating and sorting inventory values based on unit price and quantity on hand in a database.
Observed behavior: The user wants a correct SQL statement to achieve this calculation and sorting, specifically avoiding syntax errors like missing spaces before the FROM clause.

Before you start

Ensure you have the correct table name and field names (like UnitPrice and QuantityOnHand) before writing your SQL query.

Solution 1Recommended

Write the SQL Query with Calculation and ORDER BY Clause

Use this standard SQL statement to calculate the inventory value and sort it in descending order.

To calculate the inventory value, you need to multiply the quantity on hand by the unit price. You can assign this calculation an alias and then use that alias to sort the results.

Open SQL View

In Microsoft Access, go to the Create tab, click Query Design, close the Show Table dialog, and select SQL View from the Home or Query Design tab.

Enter the Select statement

Type the following to select your fields and perform the calculation: SELECT ProductID, UnitPrice, QuantityOnHand, QuantityOnHand * UnitPrice AS InventoryValue

Add the FROM clause

On a new line or separated by a space, add your table name: FROM Product_t

Add the ORDER BY clause

Finish the query by sorting the calculated field from highest to lowest: ORDER BY InventoryValue DESC;

Run the query

Click the Run button (red exclamation mark) in the Query Design ribbon to execute the query and view your sorted inventory.

Write the SQL Query with Calculation and ORDER BY Clause

Syntax Warning: Ensure there is a space between the `InventoryValue` alias and the `FROM` clause. A common mistake is typing `AS InventoryValueFROM Product_t`, which will cause a syntax error.

Free Microsoft Office alternative

Manage Your Inventory Data with WPS Spreadsheet

While Microsoft Access is used for complex relational databases, WPS Spreadsheet offers a free, lightweight, and incredibly easy-to-use alternative for managing inventory, calculating values, and sorting data seamlessly without needing to write SQL queries.

1. Download and Install: Download WPS Office Free and launch WPS Spreadsheet.
2. Input Inventory Data: Open your existing Excel inventory file or type your product details, unit prices, and quantities directly into the sheet.
3. Calculate and Sort: Use a simple formula like =B2*C2 to calculate the value, then use the Sort function in the Data tab to order it from Highest to Lowest.

Completely free to use with a familiar, user-friendly interface.Fully compatible with Microsoft Excel formats (.xlsx, .xls) for easy data migration.Built-in formulas and one-click sorting features to organize inventory values instantly.

Frequently Asked Questions

Why am I getting a syntax error near the FROM clause in Access?

This typically happens if a space is missing between your column alias and the FROM keyword. Check your query to ensure it says `AS InventoryValue FROM` and not `AS InventoryValueFROM`.

Can I use the calculated alias in the ORDER BY clause in Access?

Yes, Microsoft Access SQL allows you to use an alias defined in the SELECT statement (like InventoryValue) directly in the ORDER BY clause.

How do I sort results from highest to lowest in SQL?

To sort results from highest to lowest, add the `DESC` (descending) keyword at the end of your `ORDER BY` clause. If you omit it, SQL will sort in ascending order by default.

How can I format the calculated inventory value as currency?

In the Access query design grid, right-click the calculated field, select Properties, and change the Format property to Currency. Alternatively, you can use the Format() function directly in your SQL statement.

Office Quick Solutions