Advanced Information Technology (AICITSS) Module 2 PDF

ADVANCED INFORMATION TECHNOLOGY (AICITSS) COURSE MATERIAL MODULE – II Board of Studies The Institute of Chartered Accountants of India, New Delhi The objective of this background material is to provide uniform reference material to the students undergoing Advanced Information Technology under AICITSS. All attempts have been made to make the discussion simple and comprehensive. Students may note that the material has been prepared with an objective to help them in acquiring requisite knowledge and skills in the subject and gain hands on experience. This is also expected to serve as a source of reference book in their future education and training. In case students have any suggestions to make for further improvement of the material contained herein, they may write to Board of Studies, ICAI Bhawan, A-29, Sector 62, Noida. Queries can alsobe sent to : [email protected]. All care has been taken to provide the material in a manner useful to the students. However the material has not been specifically discussed by the Council of the Institute or any of its Committees and the views expressed herein may not be taken to necessarily represent the views of the Council or any of its Committees. All rights reserved.No part of this publication may be reproduced, stored in a retrieval system or transmitted, in any forms or by any means, electronic, mechanical, photocopying, recording or otherwise, without prior permission, in writing, from the Institute. ©The Institute of Chartered Accountants of India Revised Edition : February, 2021 ISBN : 978-81-8441-716-6 Price : ` 800/- (For All Modules) Published by The Publication Department on behalf of The Institute of Chartered Accountants of India, ICAI Bhawan, Post Box No. 7100, Indraprastha Marg, New Delhi- 110 002, India. CONTENTS UNIT-3 : ADVANCE DATABASE CONCEPTS Chapter - 1 Advanced SQL Queries 3-68 Chapter - 2 Designing Forms and Reports 69-123 Chapter - 3 Building Criteria Expressions 124-159 Chapter - 4 Macros and Switchboards 160-213 UNIT-4 : ADVANCED ERP CONCEPTS Chapter - 1 Simplifying Advanced Transactions in Tally 217-285 Chapter - 2 TSS Features and Capabilities 286-304 Chapter - 3 E-Filing 305-388 Practical Lab Exercises and Case Studies 389-410 UNIT-3 ADVANCE DATABASE CONCEPTS CHAPTER 1 ADVANCED SQL QUERIES LEARNING OBJECTIVES ◼ Writing Advanced Queries ❑ Creating Sub-queries ❑ Creating Unmatched and Duplicate Queries ❑ Grouping and Summarizing Records using Criteria ❑ Summarizing Data using Crosstab Query ❑ Creating a Pivot Table and a Pivot Chart ◼ Joining Tables in Queries ◼ Calculated Fields 1.1 Introduction Queries are an essential part of database. They are used to extract required data from one or more tables and present the result in a datasheet or on a Form or Report. They can be considered as questions asked to a table in a database. Access provides a graphical tool known as Query Designer to create queries. Queries are not only used to retrieve data from tables, but can also be used to insert, update or append data in tables, to filter data, to perform calculations with data, to summarize data and to automate data management tasks. This chapter focuses on creating different types of queries and their different usage. The first part of the chapter talks about Sub-queries, in which the criterion for a query is query itself, i.e. writing a que ry within query. The next part of the chapter talks about working with Query Wizard and learning how to find unmatched data between two tables and to find duplicate rows within a table. We will also learn to create a summari sed result out of queries using the crosstab queries. Crosstab queries can be taken as a tabular format of Pivot tables. This chapter also introduces the multiple views of the query and how to get query results in the form of Pivot Tables or Pivot Charts. In addition to the above queries, we will also learn to create a query on multiple tables, specify different join types to retrieve the desired result. We can create joins between the tables using relationship window provided by Access, or we can create joins at the query design window. Access also has many functions and operators which can be used to create expressions. This chapter discusses how to use these functions to create a new column from an existing column. The chapter also highlights some of the advanced queries available in Access and illustrates their significance using case studies. ADVANCE DATABASE CONCEPTS 1.2 Writing Advanced Queries Access has the capability to handle far advanced queries than the simple criteria based queries. The queries in Access not only allow users to change the existing data, but also to generate summary reports. This section discusses few advanced queries handled by Access, such as Sub-queries, Crosstab Queries, Creating Unmatched and Duplicate Queries, and also creating PivotTable and PivotChart through queries. 1.2.1 Creating Sub-queries A sub-query is a query nested inside another query. We can use Sub-queries within Select query, Action query or within other Sub-queries. Sub-queries in Select statement can be the part of Where clause or having clause in Group By query. For Action queries, we can use Sub-queries to change the records which match some values in other tables. Fig. 1.2.1 shows an example of a sub-query in a Select statement. SELECT * FROM Products WHERE ProductID IN (SELECT ProductID FROM OrderDetails WHERE Discount >=.25); Fig. 1.2.1. Subquery Example This query retrieves all the columns from table Products based on the criteria that the discount on the products in the OrderDetails table must be greater than or equal to 25%. Notice that to present the criteria of discount for Products, we have used a sub-query in the WHERE clause. Points to be noted while writing a sub-query: The sub-query must always be written as an SQL statement. The sub-query is always written in brackets ( ). If we are using the same table for the main query and sub-query, we need to provide aliases (alternate names) to the tables. If a sub-query is returning more than one row, the IN, ANY, ALL or EXISTS clause should be used in the WHERE statement. 1.2.1.1 Problem Scenario Rohit is an Accountant in Apex Ltd. At the closing of the quarter, he has to check the Invoices for which the payments are made in Quarter-I of year 2008. Solution For the purpose of solving the above requirement, a query is created to retrieve all records from Invoices table for which the Payment Date in Payments table lies in Quarter-I i.e. between 1/1/2008 and 4/30/2008. Since we do not require any details from the Payments table, we use the table in a sub-query. 4 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Steps for retrieving records from table INVOICES 1. Open the Show Table dialog box, by clicking on Create -> Queries -> Query Design. 2. Select the Invoice table from Show Table window and click Add, as shown in Fig. 1.2.2. Fig. 1.2.2: Show Table window 3. Click Close. 4. The Query Designer Window appears, as shown in Fig. 1.2.3. Fig. 1.2.3: Query Designer Window ADVANCED INFORMATION TECHNOLOGY 5 ADVANCE DATABASE CONCEPTS 5. Select the fields Invoice Number, Sales Order Number, Type of Invoice, Invoice Date, Source of Order and Payment Terms from table Invoice and drag them to the Columns tab as shown in Fig. 1.2.4. Fig. 1.2.4: Drag the required columns 6. Click the Save button at Quick Access Toolbar to save the query. Type the name of query as Invoices Paid Q1 as shown in Fig. 1.2.5. Fig. 1.2.5: Save the Query 6 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Now we create a SQL Statement for sub-query. The SQL Statement for the sub-query can be written directly in the criteria clause with the Select syntax or we may create another query using Query Designer and then copy the generated SQL. For the current scenario, we use the latter method. Steps for writing a query to retrieve Invoice Number from Payments where Paid Date is in Quarter-I of year 2008 1. Open the Show Table dialog box, by clicking on Create -> Queries -> Query Design. 2. Select the Payments table from Show Table window and click Add, as shown in Fig. 1.2.6. Fig. 1.2.6: Show Table window 3. Click Close. 4. Double-click the columns Invoice Number and Date Paid so that they appear in the Columns tab in Query Design as shown in Fig. 1.2.7. ADVANCED INFORMATION TECHNOLOGY 7 ADVANCE DATABASE CONCEPTS Fig. 1.2.7: Select Required columns 5. In the Criteria section of the Date Paid Column, write the criteria BETWEEN #1/1/2008# AND #4/30/2008# and clear the Show checkbox. The Query Design window should appear, as shown in Fig. 1.2.8. Fig. 1.2.8: Specify the criteria 8 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 6. Click the Run icon in Design -> Results to view that the proper result is coming. 7. Click on SQL View from Design -> Results -> View drop-down to view the SQL statement of the query. The SQL statement appears for the query, as shown in Fig. 1.2.9. Fig. 1.2.9: Query SQL View 8. Copy the SQL statement and switch to the Query window of Invoices Paid Q1. Steps to add a sub-query to Invoices Paid Q1 query 9. In the Criteria tab of Invoice Number column, type IN() and paste the copied query within the braces. The Query window appears, as shown in Fig. 1.2.10. Fig. 1.2.10: Write subquery in Criteria ADVANCED INFORMATION TECHNOLOGY 9 ADVANCE DATABASE CONCEPTS 10. Click the Run icon in Design -> Results to view that the proper result is coming. In a similar manner as demonstrated by an example above, Sub-queries can be used as a SQL statement for criteria of Action queries, Group By queries etc. NOTE: We can also use Sub-queries as an expression to create a new column as a query result. 1.2.2 Creating Unmatched and Duplicate Queries Query Wizard available in Access can be used to create different types of queries. Along with several different queries, it also provides queries to find duplicate records in a table on the basis of one or more fields in a table. In a similar manner, we can create Unmatched Query to compare two tables and find the records that do not have matching values in given columns. 1.2.2.1 Unmatched Queries Find Unmatched Records Query will examine the data found in two different tables/queries and compare the records based on a common field. It will return the records from the first table which do not have matching values in second table. The easiest way to create Unmatched Query is by using the Find Unmatched Query Wizard. After the wizard builds query, we can modify the query's design to add or remove fields, or to modify joins as required. Unmatched Query Wizard in Access asks for the names of the two tables to compare, and common field name between the tables. The wizard then confirms the fields that we wish to retrieve from the first tab le as a query result. Finally, the wizard prompts to enter the query name and creates the query. This type of query can help find records that have no corresponding records in other tables. For example, we may be looking for products that have not been sold in any order or may be for customers who have not placed any orders. 1.2.2.2 Problem Scenario Ankur Mathur, Sales Head of Apex Ltd. wishes to reduce the production of few products. To analy se which products should not be produced further, he needs to find out the products which have not been ordered so far. Solution For the purpose of finding the unmatched products in Sales Item Description, we use Unmatched Query Wizard and find out products from Inventory table which do not have a matching Item Number in Sales Item Description table. Steps for creating Unmatched Query using Query Wizard: 1. Open the Query Wizard by selecting Query Wizard from Create -> Queries, as indicated in Fig. 1.2.11. Fig. 1.2.11: Open Query Wizard 10 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 2. The New Query window appears, as shown in Fig. 1.2.12. Fig. 1.2.12: New Query Dialog Box 3. Select Find Unmatched Query Wizard from the New Query window and click OK, as shown in Fig. 1.2.13. Fig. 1.2.13: Select Find Unmatched Query Wizard 4. The Find Unmatched Query Wizard appears. Select the table Inventory and click Next, as shown in Fig. 1.2.14. ADVANCED INFORMATION TECHNOLOGY 11 ADVANCE DATABASE CONCEPTS Fig. 1.2.14: Select Table Inventory 5. Select the table Sales Item Description from second page of the wizard as we need to compare Inventory table to Sales Item Description table, as shown in Fig. 1.2.15. Click Next. Fig. 1.2.15: Select Table Sales Item Description 6. Now, we need to mark the common field in both the tables to be compared. For this example, select the Item Number in both Inventory and Sales Item Description table and click button, as 12 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES shown in Fig. 1.2.16. Click Next to move to next page. Fig. 1.2.16: Match the common fields between two tables 7. On the next page in wizard, select the columns that should be the part of query result and click Next. In this page, select Item Number, Class, Category, Sub Category, Description, Manufacturer, Model and Cost fields as shown in Fig. 1.2.17. Click Next to move to next page. Fig. 1.2.17: Select the Fields NOTE: Use button to move a selected field from Available fields to Selected fields, ADVANCED INFORMATION TECHNOLOGY 13 ADVANCE DATABASE CONCEPTS button to move all fields from Available fields to Selected fields, button to move selected field from Selected fields to Available fields, button to move all fields from Selected fields to Available fields. 8. Name the Query as “Products without Orders” in the final window that appears and click Finish as shown in Fig. 1.2.18. Fig. 1.2.18: Name the query 9. The result of the query appears as in datasheet form, indicating the products that do not have an order associated with them, as shown in Fig. 1.2.19. Fig. 1.2.19: Query Result 14 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 1.2.2.3 Duplicate Queries The Duplicate Queries option creates a query that reports which records in a table are duplicated by matching one or more fields in the table. The Query Wizard first confirms which fields have to be used to check for duplication and then prompts to enter some other fields that may be a part of query result. Finally, Access accepts a name for the query and displays the results as a datasheet. This type of query is useful when we have no unique indexes or primary key in the table, or the data for the table is imported from a source where we do not have mechanism to check duplicate values. 1.2.2.4 Problem Scenario Varun Gupta, a Chartered Accountant in Apex Ltd., is required to audit the invoices and the payments. While tracking the payments he noticed that multiple invoices have been created for a single sales order. To sort out the things, he wishes to check all the sales order having duplicate invoices. Solution Create a Find Duplicate Query for table Invoices based on the field Sales Order Number so that it displays all the duplicate invoices created for a single sales order. Steps for Finding Duplicate records in the table Invoices 1. Open the Query Wizard by selecting Query Wizard from Create -> Queries. 2. Select Find Duplicates Query Wizard from the New Query dialog box and click OK, as shown in Fig. 1.2.20. Fig. 1.2.20: Select Find Duplicates Query Wizard 3. The Find Duplicates Query Wizard appears. Select the table Invoice and click Next as shown in Fig. 1.2.21. ADVANCED INFORMATION TECHNOLOGY 15 ADVANCE DATABASE CONCEPTS Fig. 1.2.21: Select Table Invoice 4. Select the column on the basis of which the duplicate records need to be matched and click Next. In this scenario, select Sales Order Number as shown in Fig. 1.2.22. Click Next. Fig. 1.2.22: Select the column for Duplicate values 16 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 5. Select the columns that should be the part of the query result and click Next. In this dialog box, select Invoice Number, Type of Invoice, Invoice Date, Source of Order and Payments Terms, as shown in Fig. 1.2.23. Click Next. Fig. 1.2.23: Select the columns for Query Result 6. Name the Query as “Duplicate Invoices” in the final window that appears and click Finish, as shown in Fig. 1.2.24. Fig. 1.2.24: Name the Query ADVANCED INFORMATION TECHNOLOGY 17 ADVANCE DATABASE CONCEPTS 7. The result of the query appears in Datasheet form with all the Sales Order having multiple invoices, as shown in Fig. 1.2.25. NOTE: We can create Find Duplicate Query by matching records on multiple fields also. This query can also be used to find duplicate records in a table by matching records on all the fields (considering that we can match only 10 fields at a time). Fig. 1.2.25: Query Result 1.2.3 Grouping and summarising Records using Criteria Access provides queries that can be used for obtaining the aggregated results instead of individual records. These queries may be helpful in retrieving count of records, sum, average, and maximum or minimum of the values in a column. These queries are known as Group By queries or Totals Query. Such queries can return the aggregated results from the entire table or the records of the table filtered by a certain criteria. 1.2.3.1 Grouping and summarising Records At times, it is required to retrieve information in tables based on a group of one or more fields. For example, total number of contacts from a particular area or a sum of all the payments received in a month. Access provides a method to obtain the desired result using queries without the need of any complex programming. It calculates the totals using several aggregate functions. Access performs grouping of the records by using Totals option available in the Query Tools tab, as shown in Fig. 1.2.26. 18 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Fig. 1.2.26: Totals option in Design tab The Totals option adds a new row in the Query Designer window which enables the application of summary functions to columns as indicated in Fig. 1.2.27. Fig. 1.2.27: Total Row added A list of Aggregate Functions that can be used with Totals Query is given in Table 1.2.1. FUNCTION NAME EXPLANATION Sum() Returns the sum of numeric data for a column or set of values in a column Count() Counts the set of values that satisfy the given criteria Avg() Returns average of numeric data for a column or set of values in a column Max() Returns a maximum value from a set of values Min() Returns a minimum value from a set of values Var() Returns the variance of values in a column or set of values Stdev() Returns a standard deviation for a set of data values First() Returns the first value from a list of text values Last() Returns the last value from a list of text values Table 1.2.1: Aggregate Functions 1.2.3.2 Problem Scenario The Country Head in Apex Ltd. wishes to compute the sales volume of its products. He also would like to see the number of orders placed for each product, so that they can focus on products with greater sales volume. ADVANCED INFORMATION TECHNOLOGY 19 ADVANCE DATABASE CONCEPTS Solution: Create a Total query that calculates the count of orders placed for each product and arrange them in descending order of the counts so as to find the products which are sold more. Steps for creating a total query 1. Open the Query Design window by selecting Query Design from Create -> Queries, as indicated in Fig. 1.2.28. Fig. 1.2.28: Opening the Query Design window 2. Select the Inventory and Sales Item Description tables from Show Table dialog box and click Add, as shown in Fig. 1.2.29. NOTE: Multiple tables can be selected in the Show Table window by pressing CTRL key and then selecting the tables. Fig. 1.2.29: Show Table window 3. Click Close. We can see the two tables with a 1:∞ relationship. This relationship has been created while creating the database. 20 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 4. Click the Totals button in the Show/Hide group on the Design tab, as shown as Fig. 1.2.30. Fig. 1.2.30: Click on Totals button 5. Select the fields Category and Item Number from Inventory table and drag them to the Columns tab. Similarly, select fields Sales Order Number from Sales Item Description table and drag it to Columns tab. The Query Window appears as shown in Fig. 1.2.31. Fig. 1.2.31: Select the required columns ADVANCED INFORMATION TECHNOLOGY 21 ADVANCE DATABASE CONCEPTS 6. In the column Sales Order Number, change the Group By function to Count, as shown in Fig. 1.2.32. Notice that we have set Group By on columns Category and Item Number, so that the query first groups all the items according to their category and then all the items in same category on the basis of Item Number. The Count function with Sales Order Number indicates that we wish to compute the count of total Sales Orders for a group created. Fig. 1.2.32: Set the Totals Function 7. Click Run in Design -> Results to view the results. The result of the query appears, as shown in Fig. 1.2.33. 22 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Fig. 1.2.33: Query Result NOTE: The Query result just shows all the products arranged in group of Category and Item Number and their volume sold. To get the products which are sold more, the column CountOfSales Order Number has to be arranged in order that it appears from most sold to least sold. 8. Reopen the query in Design View by clicking Design View from Home -> Views. 9. In the column Sales Order Number, set the sort order as descending, as shown in Fig. 1.2.34. ADVANCED INFORMATION TECHNOLOGY 23 ADVANCE DATABASE CONCEPTS Fig. 1.2.34: Set the sort order 10. Click Run in Design -> Results to view the result. The result appears as shown in Fig. 1.2.35. 24 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Fig. 1.2.35: Query Results sorted according to sales of Product 11. Click the Save button on Quick Access Toolbar to save the query. Type the name of query as Product Sales Volume. NOTE: Queries are required to be saved for future use if the same result is desired again; it saves the effort and time of recreating the query. 1.2.3.3 Applying Criteria to Records A criterion limits the records that are displayed as result, on the basis of values in a field. A criterion in Total queries can be created against Group By, Aggregate Total, Non-Aggregate Total fields. For Group By and Aggregate Total, criteria can be mentioned using Criteria tab of Query Design window. For the Non-Aggregate Total field, the criteria can only be specified using the Where clause instead of Group By or Aggregate function in Total tab of Query Design window. Using any one, any two, or all three of these criteria, the scope of Total query can be limited to finite criteria. 1.2.3.4 Problem Scenario Considering the problem of retrieving Sales volume of Products as discussed in Section 2.2.3.1, Country Head wants to have a look at only the products with at least an order count of three. The management authority also thought of maintaining the products which are cheaper and have more sales volume. Solution Update the Total query Product Sales Volume created above and apply criteria “>=3” on the Sales Order Number count to ensure that only the products with at least an order count of three are retrieved as a query result. Also, add criteria “=3” in the Criteria tab as shown in Fig. 1.2.36. Fig. 1.2.36: Insert Criteria for Sales Order Number 3. Click Run in Design -> Results to view the result. The result of the query appears as shown in Fig. 1.2.37. 26 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES. Fig. 1.2.37: Query Result for Products having at least 3 Sales Orders Now, include only the products which cost less than 50.00 in the query result. Since Cost is a non-aggregate field, the criteria for the Cost is included in the Where clause of Total tab. 4. Double-click the field Cost in the Inventory table so as to include it into the Field tab of Query Design window. 5. Change the Group By function of column Cost to Where clause in Total tab and write “ Queries 2. Select Crosstab Query Wizard from the New Query dialog box and click OK as in Fig. 1.2.40. Fig. 1.2.40: Select Crosstab Query Wizard ADVANCED INFORMATION TECHNOLOGY 29 ADVANCE DATABASE CONCEPTS 3. The Crosstab Query Wizard appears. Select the table Sales Order and click Next as shown in Fig. 1.2.41. Fig. 1.2.41: Select Table Sales Order 4. The next page in Query Wizard confirms the column to be taken as Row Heading. In this window, select the column Sales Date as shown in Fig. 1.2.42 and click Next. Fig. 1.2.42: Select column Sales Date for Row Heading 30 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 5. Select the column to be taken as Column Heading in the next window of the Query Wizard that appears. In this window, select the column Sold to Customer as shown in Fig. 1.2.43 and click Next. Fig. 1.2.43: Select column Sold to Customer for Column Heading 6. Next page in the wizard prompts to specify the values that should appear on the intersection of rows and columns. Select the column Sales Order Number from the Fields tab and Count from the Functions tab in this page, as shown in Fig. 1.2.44. Click Next. NOTE: This page has a checkbox Yes, include row sums; which if checked, allows the inclusion of the grand total of values in the row as a column in the query result. ADVANCED INFORMATION TECHNOLOGY 31 ADVANCE DATABASE CONCEPTS Fig. 1.2.44: Select Sales Order Number Count as values 7. Write the name for the query as “Customer-Month wise Sales” in the final window that appears and click Finish, as shown in Fig. 1.2.45. We can also choose the default query name that the wizard displays. Fig. 1.2.45: Name the query 32 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 8. The result of the query appears in Datasheet form, as shown in Fig. 1.2.46. Fig. 1.2.46: Query Result NOTE: The query result is not as desired as it displays the values on the basis of the Sales Date and not on Sales Month. To view these results on the basis of Sales Month, we are required to make changes in the query design and change the column Sales Date to the Month (Sales Date) and also provide a criterion that the months must be in last financial year. Steps to group results on Sales Order Month 1. Reopen the query in design view by clicking the Design View from Home -> Views. 2. Click on field Sales Date and write the expression Sales Month: Month ([Sales Date]) in Field tab as shown in Fig. 1.2.47. NOTE: We can use the function Month Name() to get names of month instead of numbers in query results. ADVANCED INFORMATION TECHNOLOGY 33 ADVANCE DATABASE CONCEPTS Fig. 1.2.47: Change the expression of field Sales Date Steps to provide the criteria for last financial year 1. Double-click the column Sales Date from table Sales Order such that it appears in Field tab. Change the value of Total tab to Where and add the expression Between 4/1/2011 AND 3/31/2012 to the Criteria tab as shown in Fig. 1.2.48. Fig. 1.2.48: Provide a criteria to Sales Date column 34 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES NOTE: The Access query window has changed the criteria expression suitable to match column values. 2. Click Run in Design -> Results to view the modified result as shown in Fig.1.2.49. Fig. 1.2.49: Query Result NOTE: The result of the query appears as a 3-dimensional table with Months on rows, Customer No. on columns and the Count of orders as values. Crosstab Queries are capable of handling much more complex calculations. These queries can be based on another query using multiple tables or may use different level of grouping by having more than one row heading. Also, Crosstab queries can be created by Query Design by changing the query type to Crosstab. 1.2.5 Creating a PivotTable and PivotChart The powerful tool of MS-Excel PivotTable and PivotChart is also available in Access to summarize data. Interactive Pivot Tables and Pivot Charts enable the manipulation of summary data, and therefore can save ample time to create multiple queries and reports to achieve the same results. 1.2.5.1 PivotTable A PivotTable is a view in Access Queries that allows summarising and examining data in a datasheet form. It is used to group values as rows and columns with a calculated value at the intersection of each row and column. A PivotTable can be considered as a modified form of Crosstab queries discussed in the above section. A PivotTable is created by dragging fields to the appropriate area on the design screen. Data can also be broken down to different levels of detail, such as showing earnings by year, quarter, or month. The PivotTable view in a query can be obtained by selecting the PivotTable View from the Views drop-down in Home tab as indicated in Fig. 1.2.50. ADVANCED INFORMATION TECHNOLOGY 35 ADVANCE DATABASE CONCEPTS Fig. 1.2.50: PivotTable View The PivotTable is shown in Fig. 1.2.51. Fig. 1.2.51: PivotTable A PivotTable has four areas for dropping fields whose values are summarized. The description of these areas is shown in Table 2.2.2. 36 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Drop Area Description Filter Area Is used for the fields that are used as a filter for PivotTable The fields selected to be displayed as column headings are included in this Column Area area The fields selected to be displayed as row headings are dropped in this Row Area area The field values to be used for calculations or summarization are dragged Totals Or Detail Area and dropped in this area, and the value is displayed at the intersection of a row and column Table 1.2.2: Drop Area in Pivot Table 1.2.5.2 Problem Scenario The Head of Sales Department in Apex Ltd. has demanded a Sales Summary report for review. The following are the desired requirements: the sales of the products can be viewed in terms of Year, Quarters and Months; the report should enable the user to view the products filtered by Category. Solution To create this Summary report, first a query is created that displays the Products and their Category, Sales amount of each product, and also the date on which they were sold. Then to create summary sheet, we create a PivotTable view of the query having: Category field as a filter Product field on row Year, Quarter and Month on columns Total Sales (which is computed as Quantity sold into Product cost) displayed as values Steps for creating the query to obtain Sales Data 1. Open the Query Design window, by clicking Create -> Queries -> Query Design. 2. Select the Inventory, Sales Item Description and Sales Order tables from Show Table window and click Add, as shown in Fig. 1.2.52. ADVANCED INFORMATION TECHNOLOGY 37 ADVANCE DATABASE CONCEPTS Fig. 1.2.52: Show Table window 3. Select the columns Category and Item Number from Inventory Table and drag them to the columns tab. Similarly, select column Sales Date from table Sales Order and drag it to the columns tab. The query window appears as in Fig. 1.2.53. Fig. 1.2.53: Select the required columns 38 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 4. Now to compute the Total Sales of the Product, multiply Quantity * Price and create a new column. Write the expression Total Sales: [Quantity]*[Price] in the Field tab of the next column as displayed in Fig. 1.2.54. Fig. 1.2.54: Compute Total Sales 5. Click Run in Design –> Results to view the query result. The result appears as shown in Fig. 1.2.55. Fig. 1.2.55: Query Result ADVANCED INFORMATION TECHNOLOGY 39 ADVANCE DATABASE CONCEPTS Now, we have achieved the desired result from the query. However, the Summary Report through PivotTable is required to be created. Steps for Creating PivotTable from the query 1. Open the query in PivotTable View by clicking PivotTable View from Home -> Views, as displayed in Fig. 1.2.56. Fig. 1.2.56: Select PivotTable View 2. The PivotTable view appears with the field list on right side as displayed in Fig. 1.2.57. Fig. 1.2.57: PivotTable View 40 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES NOTE: If the PivotTable Field List does not appear, obtain the list by clicking Field List from Show/Hide group on the Design tab as indicated in Fig. 1.2.58. Fig. 1.2.58: Field List Button 3. Select the Category field from PivotTable Field List and drag it to the Drop Filter Fields Here area. 4. Select the Item Number field from PivotTable Field List and drag it to the Drop Row Fields Here area. 5. Select the Year by expanding the Sales Date By Month field and drag it to the Drop Column Fields Here area. Similarly, select and drag Quarter and Month to the Drop Column Fields Here area. 6. Select and drag the Total Sales field to the Drop Totals or Detail Fields Here area. The PivotTable window should appear as in Fig. 1.2.59. Fig. 1.2.59: Drag Fields in PivotTable Area 7. Click Save at Quick Access Toolbar to save the query. Type the name of the query as “Sales Summary Report”. If we wish to see the Quarterly sales of all the Products from Personal Watercraft category, the PivotTable query is obtained as follows: ADVANCED INFORMATION TECHNOLOGY 41 ADVANCE DATABASE CONCEPTS 8. Open the drop-down in Category Filter Area, clear all checkboxes and check the Personal Watercraft and click OK as in Fig. 1.2.60. Fig. 1.2.60: Select Personal Watercraft from Category Filter 9. Click on the negative sign on Quarter tab so that the months disappear. Also, click on the plus sign with Totals to make the Grand Total appear. Make sure that all the plus signs in Item Number Row are clicked so as to make the Total Sales value appear. The final query should appear as shown in Fig. 1.2.61. 42 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Fig. 1.2.61: Quarterly sales of all the Products from Personal Watercraft category 1.2.5.3 PivotChart A PivotChart is a tool used for graphical analysis of data. In simple terms, PivotChart helps visualize a PivotTable, Query or a Form. It can display summarized data in different chart formats and enables data analysis. Data can be presented by using different chart formats as required, and unwanted items can be hidden from being viewed. The PivotChart view in a query can be obtained by selecting the PivotChart View from Home -> Views, as indicated in Fig. 1.2.62. ADVANCED INFORMATION TECHNOLOGY 43 ADVANCE DATABASE CONCEPTS Fig. 1.2.62: PivotChart View A sample PivotChart indicating sales of a Category of Products in each Quarter in a year is shown in Fig. 1.2.63. Fig. 1.2.63: PivotChart View 44 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Description of the various headings marked in PivotChart above is given in Table 2.2.3. Headings Description X-Axis The horizontal axis in PivotChart Y-Axis The vertical axis in PivotChart Legend A table displaying the color code used for each data series in PivotChart Filter Field A field on basis of which filter can be applied on PivotChart. Data Field Field values to be shown along the Y-axis. Category Field Field values to be shown along the X-axis. Series Field Field values that will form the legend of the graph. Table 1.2.3: Various fields in PivotChart 1.2.5.4 Problem Scenario Considering the Summary Sales Report discussed in Section 1.2.5.1, a graphical representation of the same data is required. Solution To represent the data graphically, we need to create a PivotChart. For this purpose, we first create a query to display Summary Report and then create a PivotChart based on that query. In the Pivot Chart, the fields will be placed as: Category as Filter Field Years & Quarters field on X-Axis Total Sales on Y-Axis Item Number as Series Field Steps to create a query of Summary Report Follow the steps discussed in Section 1.2.5.1 to create a query Sales Summary Report and create another query Graph of Sales Summary Report with columns as Category, Item Number, Sales Date and Total Sales (Quantity * Price). Steps to create a copy of Summary Report 1. Double-click the Graph of Sales Summary Report query to open it in the Datasheet view. 2. Open the PivotChart view of the query by selecting PivotChart View from Home -> Views drop-down list. The PivotChart view appears as in Fig. 1.2.64. ADVANCED INFORMATION TECHNOLOGY 45 ADVANCE DATABASE CONCEPTS Fig. 1.2.64: PivotChart View 3. If the Chart Field List is not there, select the Field List option from the Show/Hide group on the Design tab. 4. Select the Category field from Chart Field List and drag it to the Drop Filter Fields Here area. 5. Select Years by expanding the Sales Date By Month field and drag it to the Drop Category Fields Here area. Similarly, select and drag Quarters to the Drop Category Fields Here area. 6. Select the Item Number field and drag it to the Drop Series Fields Here area. 7. Select and drag the Total Sales field to the Drop Data Fields Here area. 8. To display the legend, select Legend from the Show/Hide group on the Design tab, as indicated in Fig. 1.2.65. Fig. 1.2.65: Select Legend option 46 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 9. The PivotChart window should appear, as shown in Fig. 1.2.66. Fig. 1.2.66: Drag and Drop Fields in PivotChart Area NOTE: The Total Sales of few products is much lesser as compared to other products that they are hardly visible on data bar. To solve this problem, the axis has to be changed to Logarithmic axis so as to make data bars more visible. Steps for changing the axis to logarithmic axis 1. Right-click on any value in Y-axis so the entire scale is selected and select Properties from the menu, as shown in Fig. 1.2.67. ADVANCED INFORMATION TECHNOLOGY 47 ADVANCE DATABASE CONCEPTS Fig. 1.2.67: Select properties for Y-Axis 2. In the Properties window that appears, go to the Scale tab and check Logarithmic Scale checkbox as displayed in Fig. 1.2.68. Fig. 1.2.68: Scale Properties 48 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 3. Close the Properties window. In order to make graph more explanatory, we will provide a name to X-Axis and Y-Axis of the graph. Steps for naming Axis in PivotChart 1. Right-click the Axis Title on X-Axis and select Properties from the menu, as shown in Fig. 1.2.69. Fig. 1.2.69: Open X-Axis Properties 2. In the Properties window, go to the Format tab and change the Caption property to Sales Quarter as in Fig. 1.2.70. ADVANCED INFORMATION TECHNOLOGY 49 ADVANCE DATABASE CONCEPTS Fig. 1.2.70: Change caption of X-Axis 3. In a similar manner, change the caption of Y-Axis to Total Sales. The PivotChart appears as in Fig. 1.2.71. Fig. 1.2.71: Pivot Chart with Axis Captions 50 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Now if we wish to see the chart of quarterly sales of all the products from Truck category for the year 2011, the PivotChart will be obtained as follows: 4. Open the drop-down in the Category Filter area, clear all checkboxes check the Truck checkbox and click OK as in Fig. 1.2.72. Fig. 1.2.72: Select Truck from Category 5. In a similar manner, select 2011 from Years drop-down in horizontal axis. The resultant chart appears as in Fig. 1.2.73 indicating the sale of different items of Truck category, which is maximum in Quarter2 and minimum in Quarter4. ADVANCED INFORMATION TECHNOLOGY 51 ADVANCE DATABASE CONCEPTS Fig. 1.2.73: Quarter-wise Truck Sales NOTE: Different PivotCharts can be made to represent different kinds of data. Both PivotChart and PivotTable can also be made directly on tables rather than making a query first. 1.3 Joining Tables in Queries A Join is a temporary relationship that is created between two tables in a que ry or the relationship window using a common field in both the tables having same data type and usually the same name. Joins created in a query are temporary and are meant for the current query only. Joining tables in a query is required to view data from two or more tables. For example, to retrieve products ordered by each customer, tables Customers, Sales Order and Inventory are required to be joined to get the desired output. When two or more tables are added to the Query Design window, Access creates Joins between them based on the relationships that have been defined in the relationship window. Joins establish the criteria that the data must match to be included in the query operations. If the tables are not joined, the query result will match each record of one table to every record in another table, resulting in spurious rows. Different types of joins are available to get a different set of records as query result. There are three types of joins available in Access: inner join, left outer join, and right outer join. Inner Join: Returns only those rows from both tables that match on the joining field. Left Outer Join: The query returns all of the rows from left table, and also those rows from the right table that share a common value on both sides of the join. Since some of the rows in left table of a left outer join will not have corresponding rows in right table, some of the fields returned as a query result will be empty when the rows do not correspond. 52 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Right Outer Join: It is just the opposite of left outer join. The query using right outer join returns all the rows from right table, and also those rows from the left table that share a common value on both sides of the join. 1.3.1 Problem Scenario The Operations Regional Head of Apex Ltd. is required to produce a report giving the details of the payments received. For this purpose, two reports are required to be prepared: First report indicating the Invoices for which payments have been made, including the invoice and payments detail. Second report displaying a list of all the sales order, their invoices and details of payments, including those invoices for which payments have not been received. Solution To get the desired result, we create two queries: First query to fulfill the requirement using inner join between tables Invoice and Payments. Second query to achieve the second requirement which includes Sales Order, Invoice and Payments tables with an left outer join between Invoice and Payments tables. Steps to create the first query - to obtain invoices for which payments have been made 1. Open the Query Design window, by clicking Create -> Queries -> Query Design. 2. Select the Invoice and Payments tables from Show Table window and click Add. Click Close. 3. To join the two tables, click the Invoice Number from Invoice table and drag it to Invoice Number in Payments table. The query window appears, as shown in Fig. 1.3.1. NOTE: If the tables are already related through relationship window, the joining line automatically appears between the tables. ADVANCED INFORMATION TECHNOLOGY 53 ADVANCE DATABASE CONCEPTS Fig. 1.3.1: Create Relationship between tables 4. Select the columns Invoice Number, Sales Order Number, Type of Invoice, Invoice Date and Source of Order from the table Invoice and drag them to the columns tab. Similarly, select columns Date Paid, How Paid, Amount Paid and Amount Alloc from Payments table and drag them to the columns tab. The query window appears as shown in Fig. 1.3.2. Fig. 1.3.2: Add required columns 54 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 5. Click the Save button on Quick Access Toolbar to save the query. Type the name of the query as Payment Made. 6. Click the Run icon in Design -> Results to view the query result displaying invoices with their payment details as shown in Fig. 1.3.3. Fig. 1.3.3: Query Result Steps to create the second query - to display list of all the sales order, their invoices and payments details, including those invoices for which payments have not been received 1. Open the Query Design window, by clicking Create -> Queries -> Query Design. 2. Select the Sales Order, Invoice and Payments table from Show Table window and click Add. Click Close. 3. To join the tables, click the Sales Order Number from Sales Order table and drag it to the Sales Order Number in Invoice table. Similarly, join Invoice and Payments table on Invoice Number field. The query window appears as shown in Fig. 1.3.4. ADVANCED INFORMATION TECHNOLOGY 55 ADVANCE DATABASE CONCEPTS Fig. 1.3.4: Join Tables 4. Select the fields Sales Order Number, Sold to Customer and Sales Date from Sales Order table and drag them to the Columns tab. Similarly, select fields Invoice Number, Type of Invoice, Invoice Date, Source of Order and Payment Terms from Invoice table and columns Date Paid, How Paid, Amount Paid and Amount Alloc from Payments table. The query window appears as in Fig. 1.3.5. 56 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Fig. 1.3.5: Select required columns 5. Click Run in Design -> Results to view the query result as shown in Fig. 1.3.6. Fig. 1.3.6: Sales Order with Payments ADVANCED INFORMATION TECHNOLOGY 57 ADVANCE DATABASE CONCEPTS Notice that these query results are showing only the records for which payments have been received. To get the records for which the payment are not yet received, the join between Invoice and Payments table has to be converted to left outer join. Steps to create left outer join between Invoice and Payments tables 6. Switch to the query design view by clicking Design View in Home -> Views. 7. Right-click the joining line between Invoice and Payments tables and select Join Properties from the menu, or double-click the joining line. The Join Properties window appears, as shown in Fig. 1.3.7. Fig. 1.3.7: Join Properties dialog box 8. Select the option 2: Include ALL records from ‘Invoice’ and only those records from ‘Payments’ where the joined fields are equal., and click OK. The Join Properties window appears as displayed in Fig. 1.3.8. Fig. 1.3.8: Set the Join Properties to Left Outer Join 58 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 9. The Join line changes to indicate that it is a left outer join. Notice an arrow pointing towards the Payments table as indicated in Fig. 1.3.9. Fig. 1.3.9: Left Outer Join between Invoice and Payments tables 10. Click Save on Quick Access Toolbar to save the query. Type the name of the query as Sales Order Details. 11. Click Run in Design -> Results to view the query result shown in Fig. 1.3.10. Notice that the Date Paid, How Paid, Amount Paid and Amount Alloc fields from table Payments are blank for few records, indicating the invoices for which payments have not been received. ADVANCED INFORMATION TECHNOLOGY 59 ADVANCE DATABASE CONCEPTS Fig. 1.3.10: Query Result A more complex join can be created among any number of tables and modified accordingly to get the desired results. 1.4 Calculated Fields The Access query’s result is not restricted to the fields in tables only, but can display many other computed columns known as Calculated Fields. The Calculated fields can be compared to cells containing functions or formulas in Excel Worksheet. In Access, these cells can be considered as the columns of the table. Normalization forbids tables to have columns whose values can be computed using the existing fields. Calculations in a query are recomputed each time the query is run. As such, data is always current. The results of the calculations are not stored in a table. A calculated field performs some type of arithmetic calculations on one or more fields in a table to come up with a completely new field. For example, if a table has an Order Total field and a Tax Rate field, Access can calculate these two fields to find out the Sales Tax for each order as [Order Total] * [Tax Rate]. The calculated fields create new fields in a record by combining the values of other fields in the record. Calculated fields can store numeric, date, or text fields for each record using expressions and functions. 1.4.1 Problem Scenario Ramit, an executive in Apex Ltd. is required to produce a report displaying all the sales orders which contains the field Total Amount Paid as a sum of Tax, Freight and Other Charges. Solution As a solution to the above problem, a query has to be created on table Sales Order with a calculated column Total Amount Paid computed as a sum of Tax, Freight and Other Charges. 60 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Steps for creating query with Calculated Columns 1. Open the Query Design window, by clicking Create -> Queries -> Query Design. 2. Select the Sales Order table from Show Table window and click Add. Click Close. 3. Select the columns Sales Order Number, Sold to Customer, Sales Date, Payment Terms and Shipped via from Sales Order table and drag them to the columns tab as displayed in Fig. 1.4.1. Fig. 1.4.1: Select Required Columns 4. To create the calculated column, write the expression Total Amount Paid: [Tax] + [Freight] + [Other] in the Field tab of the next column as displayed in Fig. 1.4.2. ADVANCED INFORMATION TECHNOLOGY 61 ADVANCE DATABASE CONCEPTS Fig. 1.4.2: Calculated Column - Total Amount Paid 5. Click Run in Design -> Results to view the query result. The result is displayed in Fig. 1.4.3. Note the calculated column Total Amount Paid in query result. 62 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES Fig. 1.4.3: Query Result 6. Save the query as Sales Order Report and close the query window. 1.5 Summary Queries are the heart of every database application. Queries are responsible for converting diffuse data contained in tables into information that users can actually use. Without queries, we would have to write a complex code for every data extraction and transformation. This chapter talks about more complex part of queries. Sub-queries, as the name indicates is a query within query and helps us to retrieve data from multiple tables, and can also be used to replace Joins. The Query Wizard can be used to create queries like Find Unmatched and Find Duplicate. Apart from this, queries can generate summary reports using Group By and Crosstab Queries. Queries can be very interactive in terms of PivotTables and PivotCharts which can be obtained by just changing the query view. Queries can be based on multiple tables using different types of joins. Joins can be Inner Join or Outer Joins. Queries provide much more liberty, such as adding newly calculated columns to query result or restrict number of rows returned by specifying the criteria. Calculated columns enable us to implement normalization by omitting unnecessary columns in the table and introducing them as calculated columns later. ADVANCED INFORMATION TECHNOLOGY 63 ADVANCE DATABASE CONCEPTS 1.6 Lab Exercises 1.2.6 Case Study For the database Apex Inventory Shipment of Apex Ltd., consider the scenario and provide their solutions. 1. The company is launching a new sales campaign for its existing customers. For this , the marketing department needs the list of customers with the highest credit limit. Create a query using sub-query to retrieve this list. 2. A sales person made a mistake and skipped entering the item description for an order made by the customer. Using query, retrieve the sales order information for which the item description has not been entered. 3. For the above exercise, retrieve the information about the customer who has placed the order so that the items can be reconfirmed. 4. The company launched its new office in India and asked the sales executive to interact with various people and enter the details of possible customers in a table named Customers_India. The table has the following structure: Field Name Data type Customer Number Text Salutation Text First Name Text Last Name Text Company Text Phone Text Email Text Street Text City Text State Text ZIP Code Text In the absence of any constraints on the table, the sales team ended up inserting duplicate records of a single customer. Write a query to find these duplicate records. (Consider the Customer Number as unique for each customer.) 5. The Finance Head who keeps track of their inventory shipped, requires the report displaying the quantity of items shipped every month of year 2011. Create a Totals query displaying the required data. 6. The company is issuing a discount policy to its customers. For this purpose, the Marketing Regional Manager needs a detailed report of the customers. The report should contain Customers Name s on rows and Month Name as columns, and count of orders placed by each customer on the intersection cell s of rows and columns. 64 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES 7. Create a user interactive report using PivotTable to display payments made by customers. The user should be able to filter the customers according to their states and should be able to drill the payments in terms of years, quarters, months and days. 8. Considering the scenario given in the above exercise, create a graphical view using PivotChart indicating the total payments received quarter-wise from all the customers from a particular city. User should be able to filter the city on the basis of their country. 9. Create a query containing mailing address list for all the customers with the following field list: 1 Salutation FirstName Last Name 2 Company 3 Street, City 4 State 5 Country 6 Postal Code 10. Display the list of all customers from New York (State Code – NY), with details of items they have ordered and Total Amount to be paid by them. 1.2.7 Multiple Choice Questions 1. For the database Apex Inventory Shipment of the Apex Ltd., we wish to retrieve records for customers who have not placed any orders yet. What type of query can be used? (a) Duplicate Query (b) Crosstab Query (c) Find Unmatched Query (d) Group By query 2. Considering the database Apex Inventory Shipment of the Apex Ltd., how can we display sales grouped by country, state, and customer, all at the same time? (a) Use the Sort Descending command (b) Use the PivotTable View command (c) Use the Find command on specified groups (d) All of the above 3. For the database Apex Inventory Shipment, the number of items for each category is required to be computed, which query type is required to be used? (a) Select Query (b) Group By query (c) Crosstab Query (d) Duplicate Query ADVANCED INFORMATION TECHNOLOGY 65 ADVANCE DATABASE CONCEPTS 4. Considering the scenario in question above, which function should be used with Item Number in query? (a) Count (b) Compute (c) Sum (d) Calculate 5. In the database Apex Inventory Shipment, to see total amount received from Payments table, it should be dragged into which area of the PivotTable? (a) Drop Column Fields Here (b) Drop Row Fields Here (c) Drop Totals or Detail Fields Here (d) None of the above 6. Considering the database Apex Inventory Shipment, we wish to get all the sales orders with cash payments. What needs to be done to achieve this? (a) Create a sub-query with Sales Order as Payment type (b) Create a select query on Sales Order table and write Cash as criteria for Payment Terms (c) Create a Group-By query on Payment Terms (d) None of the above 7. From the database Apex Inventory Shipment, we wish to retrieve a report displaying details of all the invoices including the payments details (if already made) of the invoices. What type of Join should be used in Query window to achieve the desired result? (a) Left outer join (b) Right outer join (c) Default join (d) No joins will be used 8. How can we add a table to the Query Design window? (a) Select Create -> Add Table (b) Select Database Tools -> Add Table (c) Select Design -> Show Table (d) Select the table from the Navigation Pane 9. If we are creating a Crosstab query, the table we are querying must contain what? (a) Lots of confusing information (b) More than 100 records 66 ADVANCED INFORMATION TECHNOLOGY ADVANCED SQL QUERIES (c) At least one field (d) At least three fields 10. _______ type of query summarises information in a grid, organized by regions and months. (a) An update query (b) A parameter query (c) An action query (d) A Crosstab query 11. When we double click a query object, we open (a) The object in design view (b) The object in print preview (c) The result of the query (d) The underlying table on which the query is based 12. What is the primary difference between a PivotTable report and a Cross tab query? (a) A PivotTable report can contain sums, counts, and averages, while a Cross tab query cannot (b) We cannot create a PivotTable from a Crosstab query (c) A Crosstab query lets us group similar items, while a PivotTable query does not (d) None of the above 13. In Access, the best types of queries to use for data analysis are: (a) Select queries (b) Parameter queries (c) Action queries (d) All of the above 14. Which view allows adding tables to the query? (a) Datasheet view (b) PivotTable view (c) PivotChart view (d) Design view 15. Which type of join in multi-table query permits to view all the records from one table and matching from another? (a) Inner Join (b) Outer Join (c) Equi Join (d) Non-Equi Join ADVANCED INFORMATION TECHNOLOGY 67 ADVANCE DATABASE CONCEPTS 16. For the database Apex Inventory Shipment, we need to present a graphical view of the sales volume of products. User should have the liberty to view all the products, or products from a particular category. For this purpose, a PivotChart is created. Which field should be placed in area Drop Series Fields Here? (a) Category (b) Total Sales (c) Month (d) Item Number 17. For the above question, the area Drop Filter Fields Here should contain _________ field. (a) Category (b) Item Number (c) Years (d) Month 18. To view the results in Datasheet view of the query created, do the following _____________. (a) Press F5 key (b) Click Run on Design tab (c) Press CTRL + R (d) All of the above 19. The option in Design ribbon permits us to create what type of queries? (a) Crosstab Queries (b) Action Queries (c) Parameter Queries (d) Group By Queries 20. For the database Apex Inventory Shipment, if we wish to delete all the invoices for which the payments were made in last quarter of year 2011, what should be done? (a) Create a select query and delete records manually (b) Create a sub-query with action query (c) Create simple action query (d) Cannot be done using queries 68 ADVANCED INFORMATION TECHNOLOGY CHAPTER 2 DESIGNING FORMS AND REPORTS LEARNING OBJECTIVES ◼ Advanced Form Design ❑ Adding Unbound Controls ❑ Adding Graphics to Form ❑ Adding Calculated Values ❑ Adding Combo Boxes ◼ Make effective use of forms ❑ Displaying a calendar control on a form ❑ Organising information with tab pages ❑ Displaying a summary of data in a form ◼ Advanced Reports ❑ Creating customised headers and footers ❑ Adding calculated values ❑ Sub-reports ◼ Make Reports more effective ❑ Including a chart in a report ❑ Printing data in columns ❑ Cancelling the printing of a blank report 2.1 Introduction Forms help to display, add, modify, and delete data. Different features available in Access enable to create forms such that it becomes easier for the users to handle data. Access provides various methods to make forms handier and simpler. Different controls can be added to a form to enhance their working. Access also provides ActiveX controls which are Microsoft control meant for different purposes. A form can be divided into pages to increase the readability, or can include a summary section to display grouped data. Reports can be considered as the static version of forms. Reports are the best way to present dat a to higher authority and communicate the information to the people. They can be customized using header and footers, calculated values, and sub-reports to represent linked data. Access also provides the facility to create charts in ADVANCE DATABASE CONCEPTS reports which represents data diagrammatically. Since reports are used for business communication, it must be available for everyone. 2.2 Advanced Form Design Forms are an important medium of representing data in Access. Forms are not only used for entering of data, but also to view the data in a user friendly manner. Access provides various controls and utility to make the forms more presentable. For example, forms can contain unbounded controls, graphics, calculated values, and combo boxes. 2.2.1 Adding Unbound Controls An Access Form can contain multiple controls, each having its own significance. These controls can be selected from Create tab in Forms ribbon. In general, all the controls are bounded to one field of the table or query the form is based on. However, there are few controls which retain the entered value, but are not linked with any table fields. These controls can be used for text label display, for controls such as lines and rectangles, or for holding unbound OLE objects (such as bitmap pictures or logo) that are not stored in a table, but in the form itself. Unbound controls are also known as variables or memory variables. 2.2.2 Problem Scenario The database Apex Inventory Shipment has a form frmOrders as shown in Fig. 2.2.1, based on table Sales Order which keeps track of all the orders placed. The Sales Manager wishes to add a current date on the form, so that it becomes easy for the user to keep track of dates. Fig 2.2.1. frmOrders form Solution The Database Developer adds an unbounded control, a label to the form and sets its property to store the current date. 70 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS Steps for adding an unbounded control 1. Right-click the form frmOrders under All Access Objects -> Forms tab. Select Design View from the dropdown to open the table in Design View, as shown in Fig. 2.2.2. Fig 2.2.2. Open the form frmOrders in Design view 2. Select the Date & Time control from Design ribbon ->Header/Footer tab. The Date and Time window appears, as shown in Fig. 2.2.3 select the date & time format from this window. ADVANCED INFORMATION TECHNOLOGY 71 ADVANCE DATABASE CONCEPTS Fig. 2.2.3: Date and Time window 3. Click OK to close the window and draw the control on the Form Header portion on the form. The form should appear, as shown in Fig. 2.2.4. Fig. 2.2.4: Date and Time unbounded control added to a form NOTE: In a same way, other controls like shapes, logo, etc. can be added to form. The properties of these controls can be set to meet user requirements. 2.2.3 Adding Graphics to Form Attractive forms are always a valuable addition. Access makes it easy to add a graphic to the background of a form, such as a “watermark” which appear on expensive bond paper. The picture can contain a company logo, text, or any other graphic element. The picture is specified by the form’s picture property and can be embedded 72 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS in the form or linked to an external file. If the picture is linked, the graphic displayed on the form changes any time the external file is edited. The graphic can be inserted into the form in the following ways: 1. Import a graphic file (clip art, gif, jpeg, bmp, etc.) directly into the form. 2. Attach a link to the graphic that opens a website or other database object. 3. Associate the graphic with an existing macro in the database. 2.2.4 Problem Scenario Consider the frmOrders discussed in section 2.2.1. The Database Developer wants to make it more presentable and thought of adding a picture to the background of the form. Solution The picture can be set as form background using the image control. Select the picture to be added, draw the image control and adjust its properties to make image as a background. Steps for adding graphics 1. Right-click the form frmOrders under All Access Objects -> Forms tab. Select Design View from the dropdown to open the table in Design View. 2. Select the insert image button from Design ribbon -> Controls tab and drag it to cover the Details section of the form. 3. The Insert Picture window appears. Browse for the picture to be inserted and click OK to close the window. The Insert Picture window is displayed in Fig. 2.2.5. ADVANCED INFORMATION TECHNOLOGY 73 ADVANCE DATABASE CONCEPTS Fig. 2.2.5. Insert Picture window 4. The form after the picture is inserted appears, as shown in Fig. 2.2.6. 74 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS Fig. 2.2.6: Image inserted in form frmOrders 5. The picture is required to move to the back of controls. Right-click the picture and select Position -> Send to Back from the dropdown, as indicated in Fig. 2.2.7. Fig. 2.2.7. Send the picture to back of controls 6. The form appears, as shown in Fig. 2.2.8. ADVANCED INFORMATION TECHNOLOGY 75 ADVANCE DATABASE CONCEPTS Fig. 2.2.8. Form with Graphics 7. Select the picture and press F4 to view its properties. In the property sheet, move to format and set the Size Mode property of picture to stretch, as shown in Fig 3.2.9. Fig. 2.2.9. Set the image property 8. The final form appears, as in Fig. 2.2.10. 76 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS Fig. 2.2.10. frmOrders with Graphics inserted NOTE: While adding a graphic to the form, a link to that graphic can also be created. The link ensures that the changes made to original file are also reflected in form. 2.2.5 Adding Calculated Values Access provides many features to give forms enhanced behavior and a modern look. One of the major requirements in Accessform that it can be display computed results. These results may be bounded to some field in the table or may be unbounded. For example, we may need to display the complete name of the customer instead of displaying the first name and last name separately. Calculated controls can use any of the existing function or user build function available in Access. Calculated values can also be expressions computed with a combination of other fields and operators. 2.2.6 Problem Scenario Consider the form frmOrders discussed in section 3.2.1. The Manager wishes to add another date in form, which may indicate when the order will reach customers. This date can be computed as 15 days after the date of shipment. Solution A textbox is added to the form.This textbox contains calculated value, which is computed as 15 days + date of shipment. Steps to add calculated values to form 1. Right-click the form frmOrders under All Access Objects -> Forms tab. Select Design View from the dropdown to open the table in Design View. 2. Select the textbox control from Design ribbon -> Controls tab and draw it on the details section of the form. The form should appear, as in Fig. 2.2.11. ADVANCED INFORMATION TECHNOLOGY 77 ADVANCE DATABASE CONCEPTS Fig. 2.2.11. Insert Textbox in Form 3. Double-click the label and type Delivery Date instead of Text25. Similarly, double-click the textbox and type =DateAdd("d",15,[Ship Date]). The form should appear, as shown in Fig. 2.2.12. Fig. 2.2.12. Specify values for Textbox control 78 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS NOTE: The formatting of the new added textbox can be copied from above cells. 4. Select Form View from Home ribbon -> Views tab to open the form in Form View. Confirm the textbox displays the calculated value. 2.2.7 Adding Combo Boxes Combo boxes in Access provide a way of selecting a value from a list. This is quicker than remembering which value to type and ensures that the entered value is valid. A combo box is a compact method of presenting a list of choices and allows user to enter a value that is not in the list. The values in a combo box are displayed by clicking the arrow at the end. In combo box values can be selected by clicking it or by typing the first few characters of the value into the text box area of the combo box. If the Auto Expand property is set to Yes, the default setting, Accessautomatically fills in the rest of the value. Combo box consists of rows of data with one or more columns, which can appear with or without headings. One of the columns contains the values to be stored in the field (bound control) or use for other purposes (unbound control); the other columns contain explanatory information. 2.2.8 Problem Scenario Consider the form frmOrders created in section 3.2.1.Theexecutives are making many mistakes while typing the reference of the customer to which orders are sold. The Sales Manager asked the Database Developer to find the solution to this problem. Solution The Database Developer thought that it will be a good way to add a combo box for the customers. Users will have an option to choose the value from the list or type a new value if required, thereby decreasing the typing mistakes. Steps to add a combo box in Form 1. Right-click the form and select Design View from the dropdown to open the form in Design View. The form appears, as shown in Fig. 2.2.13. ADVANCED INFORMATION TECHNOLOGY 79 ADVANCE DATABASE CONCEPTS Fig. 2.2.13: frmOrders in Design View 2. Delete the Sale to Customer textbox. 3. Select the Combo Box control from Design ribbon -> Controls tab and draw it in the place of Sale to Customer textbox. The Combo Box wizard appears, as shown in Fig. 2.2.14. Fig. 2.2.14. Combo Box wizard 80 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS 4. Select the first option i.e. I want the combo box to look up the values in a table or query and click Next. 5. In the Select Table or Query window, select the table option from View tab and choose table Customer from the list, as indicated in Fig. 2.2.15. Click Next to continue. Fig. 2.2.15. Select the required table 6. In the Select Field window, select field Customer Number from Available field and click on sign to put it into selected fields. Similarly, add First Name and Last Name to selected field. The window appears, as shown in Fig. 2.2.16. Alternatively, the user can double-click the fields to add them to selected field. Fig. 2.2.16. Add field to Combo Box ADVANCED INFORMATION TECHNOLOGY 81 ADVANCE DATABASE CONCEPTS NOTE: Even if the multiple fields are selected to be displayed in Combo Box, the Primary Key will be stored in table or form. 7. Click Next to continue. In the Sort Order window select Customer Number as 1. This is indicated in Fig. 2.2.17. Fig. 2.2.17. Select Customer Number for Sort Order 8. In the Hide Key Column window, uncheck the Hide Key Column checkbox, as shown in Fig. 2.2.18. Click Next to proceed. Fig. 2.2.18. Uncheck Hide Key Column Option 82 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS NOTE: Hide Key Column hides the Primary Key to be displayed as a value in combo box, though internally only Primary key is stored. 9. The next window confirms a unique value to be stored in table. Select Customer Number and click Next, as indicated in Fig. 2.2.19. Fig. 2.2.19. Select Field to be stored in Table 10. In the next window that appears, select the option Store the value in this field and select the value Sale to Customer from the dropdown, as indicated in Fig. 2.2.20. NOTE: The first option Remember the value for later use is used in case of unbound controls. Fig. 2.2.20. Select field to store the value ADVANCED INFORMATION TECHNOLOGY 83 ADVANCE DATABASE CONCEPTS 11. Click Next to make the Name window appear. Type the name cmbCustomers for the Combo Box and click Finish to close the wizard, as indicated in Fig. 2.2.21. Fig. 2.2.21: Type Name for Combo Box 12. The Sale to Customer combo box appears on the form, as displayed in Fig. 2.2.22. Fig. 2.2.22. Combo Box added in form 13. Set the formatting of the label and combo box to make it more presentable. Open the form in Form View to verify the added combo box. The form should appear, as shown in Fig. 2.2.23. 84 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS Fig. 2.2.23. The Combo Box List appears 2.3 Make effective use of Forms Most databases provide forms for data entry and for viewing data. Access provides few techniques that help enhancing the usability of forms, and in turn, the productivity of the users of database. Forms are used by most of the users on a regular basis, and hence it is very crucial to make the effective use of forms. Adding more capabilities to forms helps to save user time and work, and also present data in a more organi sed way. Some of the extended functionality of the form can be: Adding calendar control on a form Organising information with tab pages Displaying a summary with tab pages 2.3.1 Displaying a calendar control on a Form To make forms more presentable and user friendly, different types of controls can be added to forms. These controls can be as simple as controls that are available in the design ribbon -> controls tab, or can be a third party ActiveX control. ActiveX controls are usually graphical objects that do not operate as standalone solutions, and they run only in the Windows environment. Calendar control is one of the most popular ActiveX controls. While working with dates, it is always helpful to have a calendar nearby. If a form contains a date field, it is always good to add a calendar control which displays dates graphically and make the form more user-friendly. The calendar control provides properties that enable to set and retrieve dates in/from a table. 2.3.2 Problem Scenario Database Designer of Apex Ltd. has designed the frmOrders form to be used for Sales Orders table, as shown in Fig. 2.3.1 The Supervisor of the Customer Service Group informs the designer that the people taking orders ADVANCED INFORMATION TECHNOLOGY 85 ADVANCE DATABASE CONCEPTS often need to refer to a calendar to answer customer questions, such as when they will receive a shipment. A calendar is necessary so that the customer service employees can take weekends and holidays into account when they make an estimate as to when orders will be shipped. Fig. 2.3.1. frmOrders Form Solution A Calendar control is added to all the date fields to make the form convenient for the users. The Database Developer adds a calendar control for Sales Date and Ship Date for users to pick up date graphically. Steps to add a Calendar Control 1. Right-click the frmOrders and select Design View from the dropdown to open the form in Design View, as shown in Fig. 2.3.2. 86 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS Fig. 2.3.2. Open form frmOrders in Design View 2. From Design ribbon -> Controls tab, click the Insert ActiveX Control Command. 3. The Insert ActiveX control window appears. SelectCalendar Control 11.0 from the list and click OK, as shown in Fig. 2.3.3. ADVANCED INFORMATION TECHNOLOGY 87 ADVANCE DATABASE CONCEPTS Fig. 2.3.3. Select Calendar control 4. The Calendar control is placed in the upper-left corner of the form. Drag it to the required position. The frmOrders after inserting the Calendar control appears, as displayed in Fig. 2.3.4. 88 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS Fig. 2.3.4: Calendar control inserted on the form 5. Select the Calendar control and press F4 to open the Property Sheet for the Calendar control 6. Move to the data tab in property sheet. Click the arrow next to the Control Source property, and choose Sales Date from the list, as shown in Fig. 2.3.5. Fig. 2.3.5. Set the Control Source property ADVANCED INFORMATION TECHNOLOGY 89 ADVANCE DATABASE CONCEPTS NOTE: The Calendar control has many properties that you can set to create a custom appearance. 7. To set other properties of Calendar control, click on button in the Custom property in Other tab. The Calendar properties appear, as shown in Fig. 2.3.6. Fig. 2.3.6. Calendar Properties 2.3.3 Organising information with Tab Pages A Tab control is an Accesscontrol that allows user to create multiple pages in one form. Each page is separated by its own tab and becomes active when the user selects a tab. Tab controls are useful for presenting grouped information that can be assembled by category. A tab control has pages, each with a tab of its own. Each tab page can contain all types of controls, such as text boxes, combo boxes, images, and even command buttons. 2.3.4 Problem Scenario The Sales Manager asks the Information Analyst to store the information of the customer which could be displayed in a friendly manner, such that the customer’s personal details and company details can be viewed separately. Solution The Information Analyst advises the developer to divide the information into separate tab in a form. One tab in the form should contain customer contact detail, while the other can contain customer’s company details, and third tab can contain customer’s terms with the company. 90 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS Steps to add tab in a form 1. On the Create ribbon -> Forms tab, click Blank Form to create a new form, as shown in Fig. 2.3.7. Fig. 2.3.7: Open a blank Form 2. From the Field List window, open the plus sign with Customers table and drag the fields Customer Number, First Name, Middle Name and Last Name to the form. The form appears, as shown in Fig. 2.3.8. Fig. 2.3.8. Add Fields to a blank form NOTE: If the field list window does not appear, click on Add Existing Field from Design ribbon -> Tools tab. 3. Click Home ribbon -> Views tab -> View and select Design View from the dropdown to open the form in Design View. The form appears in Design View, as shown in Fig. 2.3.9. Fig. 2.3.9. Form in Design View ADVANCED INFORMATION TECHNOLOGY 91 ADVANCE DATABASE CONCEPTS 4. Extend the size of the form as required. Click on tab control in Design ribbon -> Controls tab and draw it into the form. The form appears, as shown in Fig. 2.3.10. Fig. 2.3.10. Add tab control to a form 5. Double-click the tab Page28. Property sheet appears. Type the value Personal Details in Name property, as shown in Fig. 2.3.11. Fig. 2.3.11. Change the name of first tab 6. In a similar manner, change the name of the second tab to Company Details. Right-click the Tab control and select Insert Page from the dropdown to add a new tab and change its name to Other Details. The form should appear, as in Fig. 2.3.12. 92 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS Fig. 2.3.12. Add three tabs to Form 7. Move to the Personal Details tab, and select the field Street, City, Country, Zip/Postal Code, Phone, Other Phone, and Email from the Field List window. The resultant form is displayed in Fig. 2.3.13. Fig. 2.3.13: Add required field to personal Details tab ADVANCED INFORMATION TECHNOLOGY 93 ADVANCE DATABASE CONCEPTS 8. In a similar manner, add the fields CompanyName, Company Details and Job Title to the Company Details tab and also the fields Credit Limit, Payment Terms, Comments, and Document submitted to the Other Details tab. 9. A tabbed form is prepared. Now the user can navigate between different tabs to view the information required. 10. Save the form as frmCutomers. 2.3.5 Displaying a Summary of Data in a Form Presenting a summary of data can be very useful to users who access database. PivotCharts and PivotTables are created in forms to display a summary of data. Pivot Table is used to summarize and analyzes data in a form. The idea of Pivot Table is to let users slice and dice the data in any way required at a given moment in time. Pivot Table represents the data in spreadsheet form, while Pivot Chart represents the same data in a graphical form. Both Pivot Chart and Pivot Table are different views of a form. Pivot Table represents the data in tabular form, in which one or more vales are represented in rows, another value in columns, and a summarized value at the intersection of row and column. Forms that lend themselves to be displayed in PivotTable or PivotChart view provide many ways for users to manipulate data. An example of such a form is one that contains information about country, city, salesperson, sales, and date of sale. Such form can be used to determine sales by city and salesperson for each month, o r sales in each country for each salesperson during the year. 2.3.6 Problem Scenario The Sales Manager wishes to see the summarized data of sales. He requests the Database Developer to create a form in such a manner, that it should display the summarized data in terms of cost of Item Sold per year, per country. For example, he needs a report displaying summary of the items sold country wise and state wise in year 2008 Quarter-2. Solution The Database Developer first creates a query based on table Sales Order, Customers, Sales Item Description, and Inventory and fetches the required data from it. Once query is created, then a form using pivot table option is created over the query. Steps for creating a query 1. Open the Query Design window by clicking Create ribbon ->Queries tab -> Query Design. 2. Select the tables Customers, Sales Order, Sales Item Description and Inventory table from the Show Table window. Click OK to close the window. The Query Design window appears, as shown in Fig. 2.3.14. 94 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS Fig. 2.3.14. Query Design window 3. Select Country and State from the Customers table and drag them to the Add Columns tab. Similarly add the column Sales Date from Sales Order table and Item Number from Inventory table. Also add a calculated column as Total Sale: [Quantity] * [Price]. The resultant query window is displayed in Fig. 2.3.15. Fig. 2.3.15. The query Design window 4. Save the query as SummarizedData and run to confirm the results. Steps for creating a Pivot Table Form 1. Select the query SummarizedData under All Access Objects -> Queries tab. From the Create ribbon -> Forms tab ->, click the dropdown arrow near More Forms and select Pivot Table from the list, as indicated in Fig. 2.3.16. ADVANCED INFORMATION TECHNOLOGY 95 ADVANCE DATABASE CONCEPTS Fig. 2.3.16. Select the Pivot Table option 2. The Pivot Table window appears along with the field list, as shown in Fig. 2.3.17. Fig. 2.3.17. The Pivot Table window NOTE: If the Field List window does not appear, click Design ribbon -> Show/Hide tab and Field List. 96 ADVANCED INFORMATION TECHNOLOGY DESIGNING FORMS AND REPORTS 3. Open the plus sign of the field Item Number and drag the field to Drop Row Fields Here. Open the plus sign of Country and State drag Country and then State to Drop Column Fields Here. Similarly, open the plus sign for Total Sale and drag the column Total Sale to Drop Totals or Detail Fields Here, and open the plus sign of Sales Date By Month and drag the fields Years and Quarters to Drop Filter Field Here. The resultant Pivot Table window is shown in Fig. 2.3.18. Fig. 2.3.18. Pivot Table window 4. Click the dropdown arrow of Sales Date by Month field. Deselect the Select All option and open the plus sign of year 2008, and select Qtr2 from the year 2008, as shown in Figure 2.3.19. ADVANCED INFORMATION TECHNOLOGY 97 ADVANCE DATABASE CONCEPTS Fig. 2.3.19. Select the required year 5. The Pivot Table window displays the sales made for each item country and state wise in Quarter2 of year 2008. NOTE: Pivot Table gives you a flexibility of selecting the desired data and view the data according to your requirements. 2.4 Advanced Reports Reports are one of the best ways to represent data. Reports can be made more advanced and more user friendly to represent the data in a more organized form. The Reports can include customized Headers and Footers such as to display the company Logo or the department. Also the calculated controls can be added to report to display computed values. 2.4.1 Creating Customised Headers and Footers Reports can include pairs of header and footer sections: Report Header and Footer for printing information at the begi

Advanced Information Technology (AICITSS) Module 2 PDF

Document Details

Tags

Related

Summary

Full Transcript