Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

Share:

8 comments:

  1. Hi, I had a few minutes to kill so I gave this test a go. I did this in two steps but used a different method to you. Here is what I did. Firstly, my table is called tbl_test but the field names are the same.
    Ok my first query looks as follows (named as qry_test_part1):
    TRANSFORM Sum(tbl_test.Amount) AS SumOfAmount
    SELECT tbl_test.Location
    FROM tbl_test
    GROUP BY tbl_test.Location
    PIVOT tbl_test.Type;

    Then my second is thus:
    SELECT qry_test_part1.Location, [R]-[E] AS Profit
    FROM qry_test_part1;


    Thanks!

    ReplyDelete
  2. Nice try, but I think you have missed something from my Question. I will repeat those two Paragraphs of my Question given above the sample output image, to clear my point:

    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.


    Please Check the image that I have given as output above and compare it with your Query result.

    Regards,

    ReplyDelete
  3. Yes, I accept your example as a solution with a difference to the earlier challenge that I have given at the beginning part of this Article.

    Regards,

    ReplyDelete
  4. Hi,

    Sorry, I missed that question...

    Using qry_test_part1 I would create two more queries, the first named qry_test_january, SQL below:

    TRANSFORM Sum([R]-[E]) AS Profit
    SELECT qry_test_part1.Location, qry_test_part1.E, qry_test_part1.R
    FROM qry_test_part1
    WHERE (((qry_test_part1.Month)="January"))
    GROUP BY qry_test_part1.Location, qry_test_part1.E, qry_test_part1.R
    PIVOT qry_test_part1.Month;

    The second would be qry_test_february which is essentially the same as qry_test_january, except the month filtered is now february:

    TRANSFORM Sum([R]-[E]) AS Profit
    SELECT qry_test_part1.Location, qry_test_part1.E, qry_test_part1.R
    FROM qry_test_part1
    WHERE (((qry_test_part1.Month)="February"))
    GROUP BY qry_test_part1.Location, qry_test_part1.E, qry_test_part1.R
    PIVOT qry_test_part1.Month;

    Finally, we just need to combine the two and give the results some meaningful names. I have linked them on location on an equal join since both queries have the same locations in each:

    SELECT qry_test_february.Location, qry_test_january.E AS [January Expenses], qry_test_january.R AS [January Revenue], qry_test_january.January AS [January P / L], qry_test_february.E AS [February Expenses], qry_test_february.R AS [February Revenue], qry_test_february.February AS [February P/L]
    FROM qry_test_february INNER JOIN qry_test_january ON qry_test_february.Location = qry_test_january.Location;

    ReplyDelete
  5. I am glad that you have shown the courage and tried to approach the problem to solve it.

    We need little re-thinking here. If we follow this pattern of creating Queries for each month and linking them together one by one we will be creating Queries through out the Year after introduction of new Data into the Table each month. But, you are in the right direction, little bit of planning is required.

    I will give you a clue. We need only two Queries to solve this problem.

    Regards,

    ReplyDelete
  6. Well I was just duplicating your example. Of course if I were doing this month on month, then the location would be transposed...therefore..

    Using qry_test_part1 again..create two queries. First named qry_month_new_york:

    TRANSFORM Sum([R]-[E]) AS [P/L]
    SELECT qry_test_part1.Month, qry_test_part1.E, qry_test_part1.R
    FROM qry_test_part1
    WHERE ((([Location] & " P/L")="New York P/L"))
    GROUP BY qry_test_part1.Month, qry_test_part1.E, qry_test_part1.R
    PIVOT [Location] & " P/L";

    Second named qry_month_ohio:

    TRANSFORM Sum([R]-[E]) AS [P/L]
    SELECT qry_test_part1.Month, qry_test_part1.E, qry_test_part1.R
    FROM qry_test_part1
    WHERE ((([Location] & " P/L")="Ohio P/L"))
    GROUP BY qry_test_part1.Month, qry_test_part1.E, qry_test_part1.R
    PIVOT [Location] & " P/L";

    Finally, link them together in qry_month_location:

    SELECT qry_month_new_york.Month, qry_month_new_york.E AS [New York Expenses], qry_month_new_york.R AS [New York Revenue], qry_month_new_york.[New York P/L], qry_month_ohio.E AS [Ohio Expenses], qry_month_ohio.R AS [Ohio Revenue], qry_month_ohio.[Ohio P/L]
    FROM qry_month_new_york INNER JOIN qry_month_ohio ON qry_month_new_york.Month = qry_month_ohio.Month;


    This way you can add as many months as you want, though if a new location were brought in, you'd need to create a separate query for each new location.

    ReplyDelete
  7. Even though we have used only two locations in the example; there can be several locations in the Report. Creating Queries for each location is very complicated. It is not that hard to create this Report. With the right approach we can do this with few steps.

    I am getting ready with the next Article on this. I will come out with my solution to this problem tomorrow late evening. Visit the site tomorrow late at night (Oman Time, +4 GMT)or day after tomorrow. You can subscribe to the RSS Feed by entering your E-Mail Address in the right-side panel above. You will receive the Article as soon as it is published Online.

    Regards,

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code