MS-Access and Data Processing

Introduction

Designing Forms or Reports in Microsoft Access can be learned quickly by mastering the Design Tools available, even without delving deeply into programming. However, data processing is a different challenge altogether — it demands flexibility and varies from project to project, making it impossible to standardize.

The data table design is crucial and must be carefully planned to ensure easy information retrieval and to prevent data duplication. Establishing proper relationships between tables is equally important to join related information together.

If these principles are ignored — if you design casually and enter data as you might in Microsoft Excel — you’ll likely face serious difficulties when you try to prepare reports later.

A good example of proper database design and relationships can be found in the sample database:

C:\Program Files\Microsoft Office11\Samples\Northwind.mdb

Open the Northwind sample database and select Relationships from the Tools menu to view the structure of the various tables and how they are organized and related to one another. Use this as a reference and guide when planning your own projects.

Each field name shown in bold represents a Primary Key in its respective table. These keys establish one-to-many relationships between tables, ensuring that all the required information can be accessed efficiently from related tables when generating reports.

The above notes are a reminder for your future projects. To illustrate the importance of proper table design, consider the example image of a poorly structured table shown below. In that design, Location Names and Description values are directly entered in the same table — a mistake that leads to data duplication and maintenance difficulties.

Instead, each of these — Location and Description — should be maintained in separate lookup tables, each with its own unique code. You can then use Combo Boxes in the Transactions table structure to insert these codes into the appropriate fields, ensuring data consistency and eliminating redundancy, as demonstrated in the corrected design below.

Right-click to open Large Image in New Tab/Window.

Approaching the Data Processing Task.

In this exercise, we will explore and learn the data processing steps required to derive meaningful information from the table shown above.

The second field, Type, contains transaction category codes — R for Revenue and E for Expenses. These codes were intentionally included in the table design to allow us to group transactions by category and separately tabulate the Revenue and Expense values. The Description field lists the actual Account Heads under which each transaction is recorded.

Our task is to prepare a Location-wise Profit and Loss (P&L) Statement. To achieve this, we need to sum all Revenue values and subtract the total of all Expense values for each location. The final result should display the Profit or Loss amount alongside the corresponding Location Name, as shown in the sample report image below.

Now, here’s a question to consider:
How many queries or steps do you think are needed to produce this report?

Surprisingly, the entire process can be completed in just a few well-designed steps — ultimately yielding the final Profit/Loss summary by Location.

Right-click to open Large Image in New Tab/Window.

The first thought that comes to one's mind is: how can you subtract the value of one row from another? If that’s what you’re thinking, you’re already on the right track.

Now, let’s talk about the number of steps. If you say it takes four steps, I won’t consider that the best approach — but if it gets the correct result, that’s fine. After all, the end result is what really matters to the User.

If you can solve it in three steps, I’ll be curious to see your method. If you can do it in two steps, then you clearly have a solid grasp of MS-Access techniques. And if you can do it in one step, then I know you’re someone who understands MS-Access.

If you’re truly interested in this challenge, here’s my suggestion:
Stop reading here, and try it out on your own database. Create the Transactions Table with the structure and sample data given above. Work through the problem, and then compare your solution with the examples provided.

And if you solve it differently but still arrive at the correct result, please share your approach — it could give everyone a fresh perspective!

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.

The Two-Step 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:

   

   Right-click to open Large Image in New Tab/Window.

   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 the 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 semicolons. 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. You don't need to 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 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, which 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 second Query result is given below.

   

   Right-click to open Large Image in New Tab/Window.

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 the Type Column with Description.

Right-click to open Large Image in New Tab/Window.

Doing It Differently

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

Right-click to open Large Image in New Tab/Window.

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 the Method2_2 Query in Normal View and check the output carefully.

   As demonstrated in the examples above, there are often multiple ways to approach a problem in MS-Access and still arrive at the same result.

   If your solution requires several steps to reach the final Report output, it’s a good practice to create a Flow Chart of the process steps. This way, if you later notice an issue with the Report, you can follow the flowchart to backtrack and identify the source of 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 will you 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
• Unsecured Database and Users-Log
• MS-Access Report and Page Total
• Detail and Summary from the Same Report
• Hiding Report Lines Conditionally-3