What is a Calculated Query? If you have two or more value fields in your query you can calculate a third field based on the other two fields.
For example if you wanted to find out the value of a customer who had bought goods. It certainly is not practical to go in and manually calculate each line.
You would have the unit price and quantity, but would like to find out the total value.
So how can we do this?
I am going to use the sample database that comes with Access for this tutorial. Load up the Northwind database. Usually the file will be nwind.mdb
Open the query designer and select the following tables
Customers Orders Order Details
Access will link them in the query designer.
Double click or drag the followi
ng fields to the query grid
Customers table - CompanyName
Orders table - OrderDate
Order Details table - UnitPrice, Quantity
In the query designer go to a blank column and on the field line type in the following:
TotalValue: UnitPrice * Quantity
Notice how Access adds the square brackets around each field name. This is ok.
Now run the query.
Access has now totaled the column called 'TotalValue' a result of multiplying the quantity by the unit price.
If you have hundreds or thousands of records this is a great time saver. Imagine having to manually go in and calculate each line?
I hope this has given you an insight into what can be done with MS Access calculated fields.
Paul Barnett, an Access developer from London, UK has been developing software for 15 years. His products and training are used all over the world by individuals and companies including IBM and MCI Worldcom. He believes in the power of simplicity and that the system should only do what the user asks for. It should not include features that were never asked for.
Article Source: www.activehowto.com
| Share this article with others. Bookmark it at these sites: |
         
         
      |
READ NEWEST ARTICLES HERE
Posted 2009-02-09 00:09:49 By Paul Barnett
Views: 268
Submit Article
?
