Module 2-70-140 PDF - ADVANCE DATABASE CONCEPTS

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...

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 beginning and end of the report. Page Header and Footer for printing information at the top and bottom of each page. Group Header and Footer for printing information when the group starts or the group ends, if groups exist in the report. To add a header/footer pair, right-click in the report design and choose Page Header/Footer or Report Header/ Footer from the shortcut menu. Page and report headers and footers are added as pairs, while In Group only Headers can be added. 98 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS Controls in the Report Header & Footer section are printed only once at the beginning and the end of the report. A common use of a Report Header section is as a cover page or a cover letter, or for presenting information that needs to be communicated only once to the user of the report. The Report Footer section can be used to display the summarized data of whole report, like author of report, date & time, etc. Controls in the Page Header & Footer section are normally printed at the top and bottom of every page. Typically, Page Headers serve as column headers in group/total reports; they can also contain a title for the report. A Group Header section normally displays the name of the group. Group Headers immediately precede Detail sections. A Group Header is added to the report if any grouping is done in the report. Each Header& Footer section in reports can be customized from their traditional look to meet user’s requirement. Different controls can be added to header and footer. The textboxes or labels can be used to display a user-defined message. Various functions and expression can be used to display summary results an d computed data. 2.4.2 Problem Scenario The Database Developer has prepared a report rptCustomers to display all the details of the customers, as shown in Fig. 2.4.1. The Zonal Sales Head requires the report to be customized; each page of the report must contain the Report Title at the top and date & time at bottom. Fig. 2.4.1: Report rptCustomers ADVANCED INFORMATION TECHNOLOGY 99  ADVANCE DATABASE CONCEPTS Solution The Database Developer adds a Page Header & Footer to the report and customizes the Page Header to hold a label with Report Title, and Page Footer to contain the date & Time using Date Time control. Steps to customize Page Header & Footer of the Report 1. Right-click the report rptCustomers under All Access Object -> Reports tab and select Design View from the dropdown to open the report in Design View. The Report Design View should appear, as in Fig. 2.4.2. Fig. 2.4.2: Report rptCustomers Design view 2. Expand the Page Header section. Select a label control from Design ribbon -> Controls tab and draw it on the Page Header section, as indicated in Fig. 2.4.3. Fig. 2.4.3. Add Label to Page Header NOTE: If Report Header does not appear right-click the report and select Page Header /Footer to view them. 100 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS 3. Type the text Customers into the label and format it according to the requirement, as shown in Fig. 2.4.4. Fig. 2.4.4. Add Title to Label 4. In a similar manner, add a Textbox control to the Page Footer of the report from Design ribbon -> Controls tab. The resultant report appears, as shown in Fig. 2.4.5. Fig. 2.4.5: Add Textbox to the Report 5. Delete the label of the Textbox and type =Now() in the textbox. The report appears, as shown in Fig. 2.4.6. ADVANCED INFORMATION TECHNOLOGY 101  ADVANCE DATABASE CONCEPTS Fig. 2.4.6. Add Textbox in Page Footer 6. Right-click the report and deselect Report Header/ Footer to remove the report header and footer. This is optional. The report header can contain some different text and can be displayed. 7. Open the report in Report View format by selecting Report View from Home ribbon -> Views tab to verify the data. The report should appear as in Fig. 2.4.7. Note that each page of the report contains the Report Title and Date/Time. Fig. 2.4.7. Report View 102 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS 2.4.3 Adding Calculated Values Reports in Access can be modified to display the data as per user requirements. In general , reports contain fields from the table, but many times it becomes necessary to add some computed values in reports. These computed values may count, sum, or calculate an average of the numeric values in a group created in the report. Also, these computed values can be calculated using one or more fields in a table , for example, to display the total cost by multiplying quantity and price. The calculated values in reports are added using a text box and specifying an expression. The liberty of displaying computed values helps to display the reports in a user-friendly format. The calculated values in the report can be displayed in two ways: using queries with calculated columns and creating reports based on them, or by computing values in the report itself using Expression and Functions. The calculated values in queries are displayed as fields in reports which use them. 2.4.4 Problem scenario Consider the report rptCustomers discussed in section 3.4.1.The Sales Head wishes the customer name should be displayed as a complete name, not as first name and last name. He requests the Database Developer to implement the change. Solution The Database Developer adds a calculated value that concatenates first name and last name in the report rptCustomers and replaces the First Name and Last Name textboxes. Steps to add Calculated Values 1. Right-click the report rptCustomers under All Access Objects -> Reports tab and select Design View from the dropdown. The report appears in Design View, as shown in Fig. 2.4.8. Fig.2.4.8: report in Design View 2. Select the First Name and Last Name textboxes and delete them. Add a new Textbox control from Design ribbon -> Controls tab. 3. Drag the Textbox in place of First Name and Last Name textboxes. The report should appear, as shown in Fig. 2.4.9. ADVANCED INFORMATION TECHNOLOGY 103  ADVANCE DATABASE CONCEPTS Fig. 2.4.9. Add Textbox control to Report 4. Delete the Textbox Label and type the text = [First Name] + “ “ + [Last Name] in the textbox. The report window appears, as shown in Fig. 2.4.10. Fig. 2.4.10. Write the expression in calculated control 5. Add a label control from Design ribbon -> Controls tab in the Page Header tab along with other headings. The Report should appear, as in Fig. 2.4.11. Fig. 2.4.11. Add Label Control to Page Header 104 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS 6. Type text Name in label and format the label accordingly. The Report should appear , as shown in Fig. 2.4.12. Fig. 2.4.12: Format the label 7. Select Report View from Home ribbon -> Views tab to open the report in Report View. The resultant report should appear, as in Fig. 2.4.13. Fig. 2.4.13. Report view 2.4.5 Sub-Reports Sub-Report is a report that is inserted in another report. A sub-report, a complete report in its own right, is inserted into another report, called the Main Report. Main Report can be either bound or unbound. A bound main report is based on a table or query and its sub-reports contain related information. An unbound main report is not based on a table or query, but can serve as a container for one or more sub-reports. A main report can include as many sub-reports as necessary. The sub-reports can be added to two hierarchy levels. ADVANCED INFORMATION TECHNOLOGY 105  ADVANCE DATABASE CONCEPTS Sub-Reports are usually an extension of data in main report. For example, the main report can contain details about the sales in a year, while the sub-report can show data for sale of each item or charts and graphs summarising and illustrating the numbers in the main report. If a sub report is inserted in a bounded main report, it should contain some field to link to main report. 2.4.6 Problem Scenario Consider the Report rptCustomersdiscussed in section 3.4.1. The Regional Head notices that the database users face a big problem while tracing the orders placed by each customer. He asks the Database Developer to create a user-friendly object which can display both the customer’s details and orders placed by the customer. Solution The Database Developer decides to add a sub report to the report rptCustomers which include details of the orders placed by the customers. For this purpose, the table Sales Order is used and the fields Sale to Customer from Sales Order and Customer Number from Customers are mapped. Steps to add a Sub Report to Main Report 1. Right-click the report rptCustomers and select Design View from the dropdown to open the report in Design View. 2. In the Design View, expand the Details section. Select the sub-report control from Design ribbon -> Controls tab and draw the control in Details section. 3. The SubReport Wizard appears, as shown in Fig. 2.4.14. Fig. 2.4.14. SubReport Wizard 106 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS 4. Select the option Use Existing Tables and Queries and click Next to proceed further, as shown in Fig. 2.4.15. Fig. 2.4.15. Select option to use existing table 5. In the Select Table or Query window, select the table Sales Orders from the list. Select the fields Sales Order Number, Sale to Customer, Sales Date, Ship Date from selected field to available field, as shown in Fig. 2.4.16. Fig. 2.4.16. Select required Table ADVANCED INFORMATION TECHNOLOGY 107  ADVANCE DATABASE CONCEPTS 6. Click Next to advance. The Link Field window appears. Select Customer Number from Forms/Reports field and Sale to Customer from Subforms /Sub reports field. The Sub Report Wizard appears, as shown in Fig. 2.4.17. Fig. 2.4.17. Link fields of main report and sub report 7. Click Next to proceed to Name of Sub report window. Provide a suitable name to your sub report, and click Finish to close the window as indicated in Fig. 2.4.18. Fig. 2.4.18: Provide a name to the sub report. 108 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS 8. Open the resultant report in Report View by selecting Report View from Home ribbon -> Views tab. Final report appears, as shown in Figure 2.4.19. Fig. 2.4.19. Customers and Orders placed by them 2.5 Make Reports more effective Reports are a way of communicating database information. By customizing reports, the information can be presented in the most effective format. A customized report developed by using Access tools can reach a wider audience and enable more users to handle them. Different methods to make reports effective can be : including charts in a report printing data in columns canceling the printing of a blank report creating report snapshot 2.5.1 Including a Chart in a Report Chart is a graphical representation of information used to illustrate quantitative relationships. It is a diagram that depicts a relationship, often functional, between two sets of numbers or between a set of numbers and a set of categories. Microsoft Graph is used to chart data from any of the database tables or data stored within other applications. It creates graphs in a wide variety of styles, such as bar graphs, pie charts, line charts, and others. Because Microsoft Graph is an embedded OLE application, it does not work by itself. As such, it has to be run from within Access. In other words, it is dependent on Access. ADVANCED INFORMATION TECHNOLOGY 109  ADVANCE DATABASE CONCEPTS Different Chart Types Chart Type Purpose Column Chart Used to compare multiple values of categories or differences over a period of time. The horizontal axis depicts categories and the vertical axis depicts values. Bar Chart Used for the same purposes as a Column Chart. However, the horizontal axis of a Bar Chart shows values and the vertical axis shows categories or periods of time. Area Chart Used to emphasize differences in individual values to the total, over a period of time. Line Chart Used to compare trends over a period of time. Pie Chart Used to show the relationship of a part to the whole. It is suitable f or depicting one data series or data at a point in time. Charts can be included in reports to illustrate the information more clearly. Charts enhance the data presented in reports by summarising the information and illustrating it in easily understandable ways. The reader can analyse trends and make comparisons using charting tools. The chart can be linked to a field in the underlying table or query. 2.5.2 Problem Scenario The Sales Head wishes to create a summary report. The report should show the quarterly total sales for each category. The Sales Head requests the Information Analyst to show a diagrammatic representation of the report. Solution The best way to display the summary report is to create charts to represent the data. The charts can be included in a report, and based on a query to show its diagrammatic representation. Steps to Include Chart in Report 1. The Query Sales data represents the summarized data for per quarter sale. The design of the query is displayed in Fig. 2.5.1. 110 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS Fig. 2.5.1. Sales Data Query 2. Select Report Design from Create ribbon -> Reports tab. A blank report opens. 3. Select Chart control from Design ribbon -> Controls tab and draw it on the report. The Chart Wizard appears, as shown in Fig. 2.5.2. Fig. 2.5.2. Chat Wizard ADVANCED INFORMATION TECHNOLOGY 111  ADVANCE DATABASE CONCEPTS 4. Select the Queries option, and select Query: Sales Data from the list, as indicated in Fig. 2.5.3. Fig. 2.5.3. Select Queries Sales Data 5. Click Next to advance. The Select Field window appears. Select all the fields: Category, Qtr, and Sales, as shown in Fig. 2.5.4. Fig. 2.5.4. Select required fields 112 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS 6. Click Next to proceed. In the Choose Chart Type, select the Column Chart and click Next. 7. In Preview Chart window drag field Qtr to Axis, Category to Series and Sales to Data as shown in Fig. 2.5.5. Click Next to advance. Fig. 2.5.5. Drag the fields to chart 8. Specify the title for the chart and select the Display Legend option. Click Finish to close the window. 9. The resultant chart appears, as shown in Fig. 2.5.6. Fig. 2.5.6. The Sales Data Report ADVANCED INFORMATION TECHNOLOGY 113  ADVANCE DATABASE CONCEPTS NOTE: The chart object can be modified by right-clicking and selecting Chart Object -> Edit from the dropdown. Also, the chart object can be embedded with existing data in the report so that the chart changes with each record display. 2.5.3 Printing Data in Columns Reports are a handy tool to represent data. The visibility of the reports makes an impact on how data can be viewed. Some of the reports may involve long lists of just a few fields of data —such as a phone or product list. These may be best arranged in multiple columns for better readability. While working with many databases, it is natural to come across some that consists of long lists of information. As a result, printing such data can mean printing a single column on many pages. The multiple columns for the report can be set using the Report Page Setup property and setting the number of columns to 2 or more, as shown in Fig. 2.5.7. Fig. 2.5.7. Select multiple columns from Page Setup 2.5.4 Cancelling the Printing of a Blank Report If a report contains no records, the detail area of the report will be blank. While printing reports, it is not be advisable to print blank report. Macros can be used to cancel printing of a blank report and thus save time and effort. Depending on the availability of data, certain records may be absent in a report and printing a blank report would be meaningless. 114 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS To cancel previewing or printing of a blank report when the underlying query has been run with no records returned, the On No Data event of the report is used. 2.5.5 Problem scenario The Database Developer has prepared a report CustOrders, which accepts the customer’sfirst name and displays all orders placed by the customer in the current quarter. However, most users while printing the report found that the even when the query returns no results, the report is printed. They requested the Database Developer to resolve the problem. Solution The report event On No Data can be used to cancel the printing of report when no data is returned. Steps for Canceling the Print 1. Open the CustOrders Report in Design View. 2. Press F4 to open the property sheet of report. Make sure that the report is selected in the Selection Type textbox. On the Event tab, select the property On No Data, as indicated in Fig. 2.5.8. Fig. 2.5.8. Property Sheet of Report 3. Select button of On No Data property.Choose Builder window appears. Select Macro Builder and click OK as shown in Fig. 2.5.9. ADVANCED INFORMATION TECHNOLOGY 115  ADVANCE DATABASE CONCEPTS Fig. 2.5.9. Open Macro Builder 4. In the Macro Builder window, select Action MsgBox and specify the value of message Arguments as No Records Found, Title as Customers Orders, as indicated in Fig. 2.5.10. Fig. 2.5.10. Specify MsgBox Action 116 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS 4. Specify the second action as CancelEvent and click the Close button to close the Macro window. The confirmation message appears, as indicated in Fig. 2.5.11. Fig. 2.5.11. Confirm to close the macro 5. Click Yes to save the macro and close the message window. The macro now appears on No Data event. 6. Open the report and verify that the macro is working. 2.6 Summary Forms and Reports are a crucial part of data representation in Access. Access provides various utilities to make Forms and Reports more user-friendly and presentable. Different controls can be added to forms to display the logo of the company, date and time, and pictures. Forms also permit the inclusion calculated values and combo boxes which make it much easier to handle the controls and present data to user. This control helps make data handling easier for the user. Access provides various ActiveX like calendar control to make forms more interactive. Sometimes, it is required to display the information on a form in groups. The tab control available with Access can be used for this purpose. The popular utility for summarizing data of Pivot Table to summarize data is also available in Access. Reports represent static data, but are a useful way of communicating. Reports in Access can be customized to user’s requirements including its header and footers. Another crucial feature available with reports is sub- reports, which enable data linking in several tables. Apart from this, we can add charts and calculated controls to reports to make them more visible. The properties of reports can be used to avoid printing of blank reports. 2.7 Lab Exercises Considering the Apex Inventory Shipment database of Apex Ltd. Provide a solution to the following problem scenarios: 1. The Sales Manager of the company has demanded a form displaying the details of the orders, containing with Items ordered, Shipping Details and Invoice Information. Design the form Order Details as dis played in Figure below. Create tabs to represent the data. ADVANCED INFORMATION TECHNOLOGY 117  ADVANCE DATABASE CONCEPTS On the Order Details Form created in Question1 implement the Question 2 to 7: 2. In the Order Details form, convert the Customer Textbox to Combo Box which contains Customer Number and Company Name. 3. To make the Order Details form more user-friendly, add a calendar control to the form to select the Order Date. 4. The Sales Manager asked the developer to add a logo of the company the Order Details form as the form will be circulated among all divisions. 5. The database developer needs to make the form more presentable and has to add a picture on the background of each tab. Implement the needful. 6. In the Order Details tab of the form, add a calculated value that calculates the Total Price as Qty * Unit Price – Discount as shown in Figure below: 118 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS 7. The Sales Manager of the Company wishes to launch some sales promotional offers. He requested the database developer to create a form which should display the summarized count and cost of Orders place d by each customer on Quarter and month basis. He should also be able to view the same results for a particular category. Create a form containing Summary of Data. The developer of Apex Inventory Shipment database needs to create Item Sales report. The rep ort should be grouped by Category and then Item and should display quantity and price of Item sold. The report is displayed in Figure below: Implement the Questions 8 – 14 based on the report Item Sales. 8. Add a calculated control Total Sales on the report that should display the Total Sales as Price * Quantity. 9. Add a Page Header and Footer on the Report. The header must contain the logo of the company and Footer must contain the current Quarter and Month. 10. Compute the Total Sales for each product in group footer. 11. Include a pie chart in the Category group footer that should display the ratio of sales of each product in that category. The report should look like as in Figure below: ADVANCED INFORMATION TECHNOLOGY 119  ADVANCE DATABASE CONCEPTS 12. The details of the Sales of Item were to be presented in the Monthly meeting as a hardcopy. So the Manager asked the executive to get the report into columnar format so that is more readable. Remove the Page Headers and the chart, and print the report in two columns. 13. For the Annual review of the sales, the Vice President demanded a report displaying the product sales by month. The reports should display the products from a particular category, which is given by the user. The report should represent the data diagrammatically using charts. Create the required Report. 14. The Sales Manager found that there are few categories which are not produced any more by the company but are not discarded in the report. As a result if such category is entered, a blank report gets printed. As a report administrator, cancel the printing of the blank report. Multiple Choice Questions 1. The controls that are not linked to any field of the table or query on the form are known as ________. (a) ActiveX control (b) Unbounded Controls (c) Graphics Control (d) Bound Controls 2. The Vice President of the Company wishes to add the image of the company vision statement as a background of all the forms. Which control can be used for the purpose? (a) Calendar Control 120 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS (b) ActiveX control (c) Image Control (d) Graphics Control 3. Which of the Header in reports can be viewed separately from the Footer? (a) Page Header (b) Group Header (c) Report Header (d) All of the above 4. The Manager requested the developer to create a Inventory form in a manner such that the Product’s description is stored in one group, the cost and supplier information another group. Which is the best control to display the required information? (a) Use the Tab Control (b) Use SubForms (c) Add ActiveX Control (d) None of the above 5. Which property of the form can be used to insert a background image? (a) Caption (b) Background (c) Record Source (d) Picture 6. The Sales Executive while filling the details of the orders placed find it very difficult to type the name of each product every time it is ordered. They demanded that the form should provide them a drop down to select the product to be ordered. How can we implement the required? (a) Using the query in the form (b) Using the Combo Box control (c) Using a SubForm (d) Cannot be done 7. The Calendar control can be selected from _______________ (a) Microsoft Office-> Access Options (b) Design -> Controls (c) Design -> Controls -> ActiveX controls (d) Create -> Forms ADVANCED INFORMATION TECHNOLOGY 121  ADVANCE DATABASE CONCEPTS 8. The Regional Sales Head demanded a summary report indicating the monthly sales done by each employee in each zone. Which kind of form is best to display the required data? (a) Use tabbed browsing (b) Insert an Image in form (c) Insert SubForm (d) Create Pivot Table 9. To print the multi-columnar report, the number of columns can be set through _________ property. (a) Report property sheet (b) Page Setup (c) Report wizard (d) Grouping 10. The persons from the delivery team found it very annoying that even when the reports contained no data, they are printed and they have to search for such reports among all the printed data. They requested the developer to find the solution to this problem. Which property of report can be used to implement the requirement? (a) CancelPrint (b) CancelEvent (c) Create a macro called On No Data (d) Create a macro called On Print 11. To display the data of the Products and the orders placed for each product in the current month. The following feature available in Reports can be used. (a) Report Wizard (b) Nested Reports (c) Grouping (d) SubReports 12. The Regional Head wishes to view the diagrammatic representation of data indicating the sales made by each zone in his region. Which feature can help to implement the requirement? (a) Graphics (b) ActiveX (c) Charts (d) Pivot Table 13. To display the list of employees grouped according to first letter of their name, which type of controls can be used in reports? 122 ADVANCED INFORMATION TECHNOLOGY  DESIGNING FORMS AND REPORTS (a) Use Calculated values in group (b) Add grouping control (c) Add Function control (d) Add ActiveX control 14. Which property of the control is used to bind it to a field of a table or a query? (a) Data (b) Record Source (c) Field (d) None of the above 15. Each tab in a tab control is known as _________. (a) Page (b) Data Tab (c) Control Page (d) Control 16. The Sales Manager requested a summary form which should enable him to choose the category and display the monthly sale of each product in the category. In the created Pivot Table Form, The category field should be placed in which area? (a) Drop Row Fields Here (b) Drop Column Fields Here (c) Drop Totals or Detail Fields Here (d) Drop Filter Field Here 17. A developer created a report displaying the information of customer grouped according to country and state. To add a count of customer in each state the count textbox should be placed in which section of the report? (a) Page Footer (b) Report Footer (c) State Group Footer (d) Country Group Footer 18. The Sub Report in the main report can be inserted to _______________ hierarchy level? (a) 7 (b) 3 (c) 2 (d) 4 ADVANCED INFORMATION TECHNOLOGY 123  CHAPTER 3 BUILDING CRITERIA EXPRESSIONS LEARNING OBJECTIVES ◼ Using operands in Criteria Expressions ◼ Using built-in functions ◼ Working with Expression Builder 3.1 Introduction Expressions in Microsoft Access can be considered similar to formulae in Microsoft Excel. Expressions are a combination of operands, operators, functions, and values that are evaluated according to their order of precedence. Expressions can be used with tables, queries, forms, reports, and macros. In Access, expressions are used to obtain calculated values, provide criteria, and query or supply constraint to table columns. Access also provides a powerful user interactive graphical tool to create expressions known as Expression Builder. In this chapter, we will discuss how to build criteria expressions in Access. We will also look at using various components of an expression for building query criteria. This chapter will also identify various operators available in Access. Next, we will discuss the available built-in functions in Access. We will discuss the different type of functions and their utilisation. Further, this chapter will cover how to use Expression Builder to create expressions using Objects, Functions, Operators, and Identifiers. 3.2 Using Operands in Criteria Expressions Query criteria are the most important part of any query as they permit users to select only the desired records from an existing table. An operand is a value on which a calculation is performed. In other words, an operand is a data value that gets manipulated in the query expression. Operands can be literals, identifiers, or functions. 3.2.1 Literals A literal is value that is not addressed by any name. It can be typed directly into the criteria expression. In Access, literal can be of type number, text, date, or logical value (i.e. True or False). Literals are also referred as constants as their values remain static throughout the evaluation of expression. Examples of literal: “Hello” + “ “ + “Everyone” , Here Hello and Everyone are Text literals [Date] > #1/1/2011#, Here 1/1/2011 (1-Jan-2011) is a Date literal  BUILDING CRITERIA EXPRESSIONS 3.2.2 Identifiers Identifiers are variables. In Access, identifiers represent field name, table name, or control name. Identifiers are a crucial part of expression building as they specify the column to which an expression represents. While creating an expression in Access, identifiers are always represented in square brackets [ ]. Examples of identifiers used in an expression: [Basic Salary] + [Tax]: Where Basic Salary and Tax are identifiers 3.2.3 Functions Functions provide specialised operations to enhance the working of Access. Functions are built-in expressions that take an input, perform necessary calculations on it, and return the output. The input accepted by the function is called arguments; a function may have one or more number of arguments. Access provides us different functions to work with different type of data, such as Text functions, Date and Time functions, Numeric functions, and Mathematical functions. For example, a text function Length takes an input string as an argument and returns the length of the string in number as: Length (“MS Access”) will return 9. 3.2.3.1 Problem Scenario Apex Ltd. is launching a new production unit in “California”, which will also focus on some new products. To promote these products a detailed list of all existing customers from “California” (state code CA) is required. Solution To achieve this, a query displaying Name, Contact Info, and Address of the customers is required. To get only the customers from “California”, the value CA in the criteria for the state field needs to be specified. Here, CA is a literal operand and is typed direct in query criteria. Steps for creating required query 1. Click Create ribbon -> Other -> Query Design to open the Query Design window. 2. Select Customers from Show Table and click Add, as displayed in Fig. 3.2.1. ADVANCED INFORMATION TECHNOLOGY 125  ADVANCE DATABASE CONCEPTS Fig. 3.2.1. Show Table window 3. Select the columns Salutation, First Name, Middle Name, Last Name, Company, Street City, State, Zip/Postal Code Phone, and Email from table Customers and drag them to the columns tab. The Query window appears, as shown in Fig. 3.2.2. Fig. 3.2.2. Select Required Columns 126 ADVANCED INFORMATION TECHNOLOGY  BUILDING CRITERIA EXPRESSIONS Now, we will add criteria to get records from “California”. 4. In the Criteria tab of field State, write the literal “CA”, as indicated in Fig. 3.2.3. Fig. 3.2.3. Specify criteria for State 5. Click Save in the toolbar to save the query. Type the name of query as “Customers from California”. 6. Click the Run sign in Design ribbon -> Results to view the results of the query, as shown in Fig. 3.2.4. NOTE: Literals are usually combined with operators to form complex expressions for query criteria. Fig. 3.2.4. Query Results ADVANCED INFORMATION TECHNOLOGY 127  ADVANCE DATABASE CONCEPTS 3.3 Using Operators in Criteria Expressions Operators make the expression complete. They are special symbols, such as +, -, used with operands to perform calculations. Every operator has a specific meaning and a symbol. Operators help create expressions with the combination of identifiers and values. Every operator is executed according to its priority. For example: In [BasicSalary] + [Tax], “+” is an operator. Different types of operators are: Comparison operators Arithmetic operators Miscellaneous operators Compound criteria and logical operators 3.3.1 Comparison Operators Comparison operators, also known as relational operators, define relation between two identifiers or two values by comparing them. These operators can be used with Numeric or Date data type. Comparison Operators are listed in Table 3.1 OPERATOR NAME EXPLAINATION > Greater Than Num1 > Num2 returns true if Num1 is greater than Num2 < Less Than Num1 < Num2 returns true if Num1 is less than Num2 >= Greater Than Equal Num1 >= Num2 returns true if Num1 is greater than or Equal to Num2 to 500000, it will return customers from California who have credit limit greater than 500000. The AND criteria in a query designer is specified by writing all the criteria in some row of the Criteria tab, as shown in Fig.3.3.1. 132 ADVANCED INFORMATION TECHNOLOGY  BUILDING CRITERIA EXPRESSIONS Fig. 3.3.1. Specifying AND compound criteria 3.3.6.2 OR Criteria The OR compound criteria are used in queries where we need to match either of the criterion specified in query criteria. OR returns result even if any criterion is true. For example, if we add criteria to the query from the Customer table as: [State] = “CA” OR [Credit Limit] > 500000, it will return all customers who are either from California or who have credit limit greater than 500000. The OR criteria in a query designer is specified by writing all the criteria in different rows of the Criteria tab, as shown in Fig. 3.3.2. ADVANCED INFORMATION TECHNOLOGY 133  ADVANCE DATABASE CONCEPTS Fig. 3.3.2: Specifying OR Compound Criteria 134 ADVANCED INFORMATION TECHNOLOGY  BUILDING CRITERIA EXPRESSIONS 3.3.6.3 Problem Scenario This case study focuses on the operators discussed above and how to use them as a Criteria Expression. At the end of a quarter, Country Head of Apex Ltd. wishes to review the following data: A report displaying all sales orders placed in from January to March 2008. All inventory details from the “Car”, “Snowmobile”, and “Boat” categories along with the details of the order placed for them. A list of all the products that were sold with a quantity greater than 5, or the price greater than 1000. A contact list for all Customers whose first name starts with A, B, or C. Solution For creating the query containing the report of Sales Order, the query will be created on the Sales Order table, using “Between” and ”And” operator in query criteria. The inventory details query will contain the Inventory table and Sales Item Description. The query criteria will be based on the IN operator. For obtaining the list of products, query needs to be based on Inventory and Sales Item Description containing compound criteria. To retrieve the contact list of Customers, query will be based on the Customers table and the criteria for the first name will contain the LIKE operator. Steps for creating query containing the report of Sales Order 1. Click Create ribbon -> Queries -> Query Design to open the Query Design window. 2. Select the Sales Order table from the Show Table window and click Add, as shown in Fig. 3.3.3. Fig. 3.3.3. Show Table window ADVANCED INFORMATION TECHNOLOGY 135  ADVANCE DATABASE CONCEPTS Select all the columns from the Sales Order table and drag them to the Columns tab. The Query window appears, as shown in Fig. 3.3.4. Fig. 3.3.4. Select Required Columns 3. In the Criteria section of the Sales Date column, write the criteria BETWEEN 1/1/2008 AND 3/31/2008. Set the Sort order of column as Ascending, as shown in Fig. 3.3.5. NOTE: The Query Builder window formats the date with # sign. Fig. 3.3.5. Specify Query Criteria 136 ADVANCED INFORMATION TECHNOLOGY