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 185

 DATABASE APPLICATIONS USING MS-ACCESS

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

186 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 Crosstab query?
(a) A PivotTable report can contain sums, counts, and averages, while a Crosstab 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 2010, 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 187

 DATABASE APPLICATIONS USING MS-ACCESS

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

188 ADVANCED INFORMATION TECHNOLOGY

 DATABASE APPLICATIONS USING MS-ACCESS

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

240 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 241

 DATABASE APPLICATIONS USING MS-ACCESS

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?

242 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 SubReport in the main report can be inserted to _______________ hierarchy level?
(a) 7
(b) 3
(c) 2
(d) 4

ADVANCED INFORMATION TECHNOLOGY 243

 DATABASE APPLICATIONS USING MS-ACCESS

Multiple Choice Questions

1. For the Apex Inventory Shipment database, a list of orders placed ten months ago or more from the Sales
Order table needs to be created. What would be appropriate query criteria for the Sales Date field?
(a) DateValue(DateAdd(“yyyy”,-3,[Sales Date]) > Today()
(b) >= 3 Months
(c) Between 3 And 5
(d) DateAdd("m",3,[Sales Date])<Date()
2. Which record will be retrieved if the query criteria is < #1/1/11#?
(a) All values less or more than 1,195
(b) Value less than 95 characters
(c) Records with date before 2011
(d) All of above
3. The query criteria on Shipped Date for deleting all the records from the Sales Order table which were
shipped before April 2011 or were never shipped would be ____________?
(a) ">=#4/1/2011# OR IS NULL
(b) Month() = April
(c) IS NULL
(d) None of the above
4. For the criteria BETWEEN 1/1/2011 and 12/31/2011, which rows will be displayed as result?
(a) Display records between the dates 1/2/2011 and 1/1/2012
(b) Display records between the dates 1/1/2011 and 12/31/2011
(c) Display records whose dates equaled 1/1/2011 or 12/31/2011
(d) All of the above
5. In the criteria expression Total Amount Paid: [Tax] + [Freight] + [Other], which value is an Identifier.
(a) Total Amount Paid
(b) [Tax]
(c) +
(d) All of the above
6. A report is needed to be prepared, checking all the orders that are pending to be shipped within one week.
The criteria expression in Shipped Date can be?

276 ADVANCED INFORMATION TECHNOLOGY

 BUILDING CRITERIA EXPRESSIONS

(a) > Today()

(b) < DateAdd(“d”,7,”[Shipped Date])
(c) > Date() + 7
(d) = Now()
7. The tab in Expression Builder in which we write expressions is known as _____________.
(a) Expression Elements
(b) Operator Button
(c) Expression Box
(d) Expression Builder window
8. The criterion for the Category column in the Inventory table is IN (“Truck”, “Boat”, “Car”). This is equivalent
to:
(a) [Category] Like “Truck”,”Boat”,”Car”
(b) [Category] = “Truck”,”Boat”,”Car”
(c) [Category] = “Truck” AND [Category] = ”Boat” AND [Category] = ”Car”
(d) [Category] = “Truck” OR [Category] = ”Boat” OR [Category] = ”Car”
9. Among +, <>, MOD, AND, <= operator which operator has the highest priority.
(a) +
(b) MOD
(c) AND
(d) <>
10. The criteria expression LIKE?a*.
(a) Will match all text starting from A
(b) Will match all text ending from A
(c) Will match all text having second character as A
(d) Will match all text starting with a digit
11. Which criteria would be used to find the records where the mode of payment is not known from Payments
table?
(a) [How Paid] IS NULL
(b) [How Paid] = “”
(c) NOT IN [How Paid]
(d) None of the above

ADVANCED INFORMATION TECHNOLOGY 277

 DATABASE APPLICATIONS USING MS-ACCESS

12. The criteria [Sales Date] > #1/1/2011# XOR [Payment Terms] = “Cheque” will return records only when …
(a) [Sales Date] is greater than 1-Jan-2011 And [Payment Terms] is “Cheque”
(b) Either [Sales Date] is greater than 1-Jan-2011 Or [Payment Terms] is “Cheque”, but not both
(c) [Sales Date] is greater than 1-Jan-2011 Or [Payment Terms] is “Cheque”
(d) All of the above
13. Consider the query on the Inventory table:

This query will return:

(a) All records from Inventory table
(b) All records from Inventory table having Quantity in Stock greater than 20 AND Quantity on Order >
25.
(c) All records from Inventory table except having Quantity in Stock less than 20 and Quantity on Order
> 25.
(d) All records from Inventory table having Quantity in Stock greater than 20 OR Quantity on Order > 25.
14. The Mid(“Apex Limited”,5,4) function will return
(a) Apex
(b) Ted
(c) Limi
(d) Apex Limited
15. The SGN() function is a ____________ type function:
278 ADVANCED INFORMATION TECHNOLOGY
 BUILDING CRITERIA EXPRESSIONS

(a) Math
(b) Financial
(c) Text
(d) Date & Time
16. The expression to combine first three characters of Customer First Name and last four characters of Sales
Order Number placed by customer will be:
(a) [First Name] + [Sales Order Number]
(b) [First Name] & [Sales Order Number]
(c) Left([First Name],3) + Right([Sales Order Number],4)
(d) Left([First Name]) + Right([Sales Order Number])
17. The expression 3\4 will return
(a) 0.75
(b) ¾
(c) 0
(d) None of the above
18. The Instr(4, “XXpXXpXXPXXP" , “P”) function will return
(a) 3
(b) 6
(c) 9
(d) None
19. Which of the following is not a Date Time function?
(a) Today()
(b) Date()
(c) Now()
(d) MonthName()
20. A query with compound criteria, where both the criteria are written on the same row of two different fields.
This will be creating_____________ compound criteria?
(a) OR compound criteria
(b) AND compound criteria
(c) XOR compound criteria
(d) EQV compound criteria

ADVANCED INFORMATION TECHNOLOGY 279