MS-Access and Data Processing

Introduction

Designing Forms or Reports can be learned quickly by mastering the use of Design Tools available in MS-Access, keeping aside the programming aspects. But, data processing is something that demands diversity in each Project and cannot be standardized. The data Table design is very important and these must be carefully planned and created for easier retrieval of information as well as to avoid duplication. Proper relationships must be established between Tables to join related information together.

Ignoring the importance of these considerations, designing with a casual approach, and filling up data with them like you do in Microsoft Excel will land you in trouble when you attempt to prepare reports out of them.

You can see a good example of database design in the C:\Program Files\Microsoft Office11\Samples\Northwind.mdb sample Database.

Open this sample database and select Relationships from Tools Menu to view the structure of various Tables and how they are organized and related to one another. Use it as a reference point and guide when you plan for a new Project.

Each Field Name in bold is defined as the Primary Key in their respective Table and established One-to-many Relationships with one another. This will ensure the availability of required information when needed from related tables for Reports.

The above lines were only a reminder of your future projects. You can see an example image of a bad Table design below. The Location names and Description values should have been in the Tables of their own with appropriate Codes. Two Combo Boxes can be created in the Transactions Table Structure to insert those Codes into the fields to avoid duplication of information as shown below.

Approaching the Data Processing Task.

But here, we are going to concentrate on learning the data processing steps using the above Table.

The second data field Type contains transaction type Codes. R stands for Revenue and E for Expenses. These category Codes are introduced in the Table keeping in mind that we must be able to Group the transactions on Category and Tabulate the Revenue and Expenses Values separately. The Description field shows the actual Account Heads under which each transaction is recorded.

We have been asked to prepare a Location-wise Profit/Loss Statement. Subtracting the Total of all Expenses from the Total of all Revenue figures will give us the required result. How many Queries or steps do you require to solve this problem, any idea? We require only the final Profit/Loss value with the Location Name in the Report, like the image below:

The first thought in your mind, I presume maybe, is how you can subtract the value of one Row from the other. Then you are thinking in the right direction.

If you say in four steps I will not accept that as a good approach, but if you can solve the problem and come out with the result then that is OK with me. After all, the correct end result is all that matters as far as the User is concerned.

If you say in three steps I will be happy to see how you do it. If you say in two steps, then I know you have some grip on things around here. If you say in one step, then I know you are somebody with MS-Access.

If you are really interested in taking up this simple challenge then stop reading further down from here and start trying out this in a database of your own. Come back with your own solution and compare it to the examples given here. If you do it differently, but arrive at the same result, then share that idea with me too.

Create the Transactions Table with the Structure and sample data given above.

One Step solution

1. Copy the following SQL String into the SQL Editing Window of a new Query and save it with a name you prefer.

   SELECT Transactions.Location,
    Sum(IIf([type]="E",-[Amount],[Amount])) AS Profit
   FROM Transactions
   GROUP BY Transactions.Location;
   

2. Open the Query in Normal View and you will see the result of the Query as shown in the second Image given above.

Two Steps Solution.

1. Create a Query with the following SQL String and name the Query as Query_Step1.

   SELECT Transactions.*,
    IIf([Type]="E",-[Amount],[Amount]) AS Amt
   FROM Transactions;
   

   The Query output will look like the image given below:

   

   Tip: The Query Amt Column is formatted to display Negative Values in Color and in brackets. Open the Query in Design View. Highlight the Column and click on the Properties Toolbar Button or select Properties from View Menu to display the Property Sheet of the Column. Type 0.00;[Red](0.00);0.00;0.00 into the Format Property and save the Query. If you open the Query now the output will appear in color.

   The Format Property Values are expressed in four segments separated by semi-colons. The first segment dictates how to display positive values, the second segment stands for Negative values, the third segment says what to display when the field value is Zero and the fourth segment displays zero when the Field/Column contains Null. The third and fourth segments can be set with a literal string like 0.00;[Red](0.00); "Zero"; "Null" to display these values rather than 0.00. You can set the Field Format Property values on the Table Structure, on Forms, or on Reports. It is not necessary that you should use all four segments of the Format Property Values all the time.

2. Create another Query, with the following SQL String, using Query_Step1 as Source Data, and save the Query with the name PL_Report:

   SELECT Query_Step1.Location,
    Sum(Query_Step1.Amt) AS Amount
   FROM Query_Step1
   GROUP BY Query_Step1.Location;
   

3. Open the PL_Report Query in the normal view, and the result will be the same as the second image given above.

Three-Step Solution

If you need more clarity on how the results are being formed in the final report then try this method.

1. You can use the first Query under the two-step solution as the first step here.
2. Use the following SQL String, that uses the first step Query's output as source data, and create the second step Query with the name Query_Step2:

   SELECT Query_Step1.Location,
    Query_Step1.Type,
    Sum(Query_Step1.Amt) AS Amt
   FROM Query_Step1
   GROUP BY Query_Step1.Location, Query_Step1.Type
   ORDER BY Query_Step1.Location, Sum(Query_Step1.Amt) DESC;
   

   The output of the second Query is given below.

   
3. Create a third Query for the final result, with the SQL String given below, using the second step Query (Query_Step2) as Input:

SELECT Query_Step2.Location,
 "Profit/Loss" AS Description,
 Sum(Query_Step2.Amt) AS Amt
FROM Query_Step2
GROUP BY Query_Step2.Location;

The output of the above Query is given below with a three Column result replacing Type Column with Description.

Doing It Differently

How about doing it differently and arriving at the following Result with Queries in two Steps?

1. Create the first Query Method2_1 with the following SQL String:
   

   TRANSFORM Sum(Transactions.Amount) AS SumOfAmount
   SELECT Transactions.Location
   FROM Transactions
   GROUP BY Transactions.Location
   PIVOT IIf([Type]="R","Revenue","Expenses");
   

2. Create the Report Query Method2_2 with the following SQL String that uses Method2_1 as Source:

   SELECT Method2_1.Location,
    Method2_1.Revenue,
    Method2_1.Expenses,
    [Revenue]-[Expenses] AS [Profit/Loss]
   FROM Method2_1;
   

3. Open Method2_2 Query in Normal View and check the output.
   As you have seen in the above examples you can approach a problem in MS-Access differently and arrive at the same result. If you have to create several steps to get the final Report output, then it is a good idea to create a Flow Chart of the Process Steps. Later on, if you find something is not right with the Report you can always follow this path and backtrack to find the Error.

A sample Flow Chart of the Three-Step Solution is given below:

If the Transactions Table has Year and Month Fields too, and both locations have January and February 2009 data in them, then how you will create the Report Month-wise?

Try it out on your own and check it out with my examples next week. A sample image of the output is given below for reference.

• Microsoft Excel Power in Access
• Un-secured Database and Users-Log
• MS-Access Report and Page Total
• Detail and Summary from the Same Report
• Hiding Report Lines Conditionally-3