MS-Access and Data Processing

Designing Forms or Reports can be learned quickly by mastering the usage 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 casual approach and filling up data in 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 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 Primary Key in their respective Table and established One-to-many Relationships to one another. This will ensure the availability of required information when needed from related tables for Reports.


The above lines were only a reminder to 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 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.



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 you require to solve this problem, any idea? We require only the final Profit/Loss value with the Location Name on the Report, like the image below:



The first thought in your mind, I presume may be, how you can subtract the value of one Row from the other. Then you are thinking on 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 you 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 on the examples given here. If you do it differently, but arrived 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 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:




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 with semi-colons. First segment dictates how to display positive values, second segment stands for Negative values, third segment says what to display when the field value is Zero and fourth segment displays zero when the Field/Column contain Null. 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 the 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 normal view and the result will be same as the second image given above.

Three Step Solution

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

1. You can use the first Query under the two steps 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.





How about doing it differently and arrive 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 back-track 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. 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 Same Report
Hiding Report Lines Conditionally-3