Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

User-Defined Data Type-3

Last week we have learned how to define a User -Defined Data Type and it’s usage in programs.  If you have landed on this page, you may go t...

Labels

Blog Archive

Recent Posts