Calculating Fields in Access

This document is designed to aid those students in the first Access tutorial in which the GPA column was overlooked. It may also be of use to those having problems with calculated fields in Access. Let us begin with the following MS Access table. (This table should look familiar, it is a slightly modified version of the "Books" table in the Bkstore Database used in the lab).

So, we have all the information you’d expect. ISBN, Title, Author, Year of publication, Price and Publisher are all there. The odd field is Publisher Rebate. Clearly a publisher rebate is money you’d be refunded by the publisher if you bought the book. What would be of interest to us as "book store owners" would be the final price to the customer (so we could advertise that price and put "after Publisher Rebate" in the fine print). So, we wish to subtract the "Publisher Rebate" field from the "List Price" field for each rebate, but how do we do that?

We start by constructing a query that would give us the fields we feel are appropriate. Let’s say the ISBN, Title, List Price and Publisher Rebate and a calculated field called "Final Price". Let’s gather the necessary fields we have (here we see the query in design mode).

So, we have ISBN, Title, List Price and Rebate, now we have to calculate the final price field. Before we continue, close the query and give it a name, I’ve picked "Final Price" (closing and saving the query now will save you the trouble of hunting down the fields under Table, Books and then selecting the right fields in the expression builder). Re-open the query, go to design mode and right click where the red dot is in the above picture. A menu will appear. Select the "build" option, it is easy to pick out, and it’s the one with the little magic wand next to it. The expression builder will appear. You should see the following (without the red circles and arrows of course):

Now double click "List Price" then the subtraction symbol (it’s in the circle), then double click "Publisher Rebate". The expression builder will now appear like this:

If you clicked the tables icon, then the books icon, and then selected the list and rebate fields, the equation would read: [Books]![List Price] - [Books]![Publisher Rebate], but this is for the most part the same equation. Click ok, then switch over to the data sheet view. The query will produce the following:

As we can see, the column labeled "Expr1" contains the difference of List Price and Publisher Rebate. Back in design mode we have:

If we were to edit this equation to read "Final Price: [List Price]-[Publisher Rebate]", the column would be labeled "Final Price". In fact, we could replace "Expr1" with whatever title we would like to appear at the top of this column (remember to leave the ":" in place). Now in the assignment we are asked for something called a "GPA" (Grade Point Average). GPA is equal to Quality Points divided by Credits. With this knowledge and the above information you should be able to complete your assignment. Widen the column as necessary so that the entire calculation is visible in design mode, then take a screen shot of the query.