SQL Primer: Using Calculated Fields


This Recordset includes a calculated field (TotalOrder), the values of which are the result of the expression (Quantity*Order_Details.UnitPrice)
OrderID ProductID ProductName Quantity UnitPrice TotalOrder
1 78 North Face Sunspot 2 390 780
8 78 North Face Sunspot 2 390 780
4 79 Polar Star 2 310 620
1 79 Polar Star 1 310 310
2 79 Polar Star 1 310 310
6 79 Polar Star 1 310 310
10 79 Polar Star 1 310 310
12 79 Polar Star 2 310 620
2 80 Big Sur 1 200 200
7 80 Big Sur 2 200 400
8 80 Big Sur 1 200 200
3 82 Everglades 1 98 98
5 82 Everglades 1 98 98
7 82 Everglades 1 98 98
9 82 Everglades 1 98 98
11 82 Everglades 1 98 98
14 82 Everglades 2 98 196
2 82 Everglades 2 98 196
8 83 Rockies 1 78 78
1 84 Sierras 5 87 435
2 84 Sierras 1 87 87
3 84 Sierras 3 87 261
13 84 Sierras 1 87 87

The SQL Source of the Recordset is as follows:

SELECT Order_Details2.OrderID, Order_Details2.ProductID, Products2.ProductName, Order_Details2.Quantity, Order_Details2.UnitPrice, (Quantity*Order_Details2.UnitPrice) AS TotalOrder FROM Products2 INNER JOIN Order_Details2 ON Products2.ProductID = Order_Details2.ProductID

This example also uses INNER JOIN to relate the Order_Details2 and Products2 tables ON the ProductID field, and uses the ProductName values from the Products2 table.

Go Top   |   Return to DB101 ADO Samples