Microsoft Access Queries Birgl Kutlu

1
Microsoft AccessQueriesBirgül Kutlu
2
SORTING AND FILTERING

• Sorting and filtering allow you to view records
  in a table in different ways such as
• reordering all of the records in the table
  (sorting by date or alphabetical order)
• view only those records in a table that meet
  certain criteria that you specify (people in
  Istanbul).

3
SORTING

• Follow these steps to execute a simple sort of
  records in a table based on the values of one
  field
• In table view, place the cursor in the column
  that you want to sort by.
• Select RecordsSortSort Ascending or
  RecordsSortSort Descending from the menu bar or
  click the Sort Ascending or Sort Descending
  buttons on the toolbar.
• To sort by more than one column (such as sorting
  by date and then sorting records with the same
  date alphabetically), highlight the columns by
  clicking and dragging the mouse over the field
  labels and select one of the sort methods stated
  above. Priority is given to the leftmost field.

4
FILTER BY SELECTION

• This feature will filter records that contain
  identical data values in a given field such as
  filtering out all of the records that have the
  value in a name field.
• To Filter by Selection, place the cursor in the
  field that you want to filter the other records
  by and click the Filter by Selection button on
  the toolbar or select RecordsFilterFilter By
  Selection from the menu bar.

5
TEXT AFFECTED BY
6
FILTER BY FORM

• This method creates a blank version of the table
  with drop-down menus for each field that each
  contain the values found in the records of that
  field.
• Under the default Look for tab of the Filter by
  Form window, click in the field to enter the
  filter criteria.

7
FILTER BY FORM

• Specify more than one criteria for and
  operation.
• To specify an alternate criteria if records may
  contain one of two specified values, click the Or
  tab at the bottom of the window and select
  another criteria from the drop-down menu.
• More Or tabs will appear after one criteria is
  set to allow you to add more alternate criteria
  for the filter.
• After you have selected all of the criteria you
  want to filter, click the Apply Filter button on
  the toolbar.

8
METHODS

• The following methods can be used to select
  records based on the record selected by that do
  not have exactly the same value. Type these
  formats into the field where the drop-down menu
  appears instead of selecting an absolute value.

9
SAVING A FILTER

• The filtered contents of a table can be saved as
  a query by selecting FileSave As Query from the
  menu bar. Enter a name for the query and click
  OK. The query is now saved within the database.
• To view all records in a table again, click the
  depressed Apply Filter toggle button on the
  toolbar.

10
QUERIES

• Queries select records from one or more tables in
  a database so they can be viewed, analyzed, and
  sorted on a common datasheet.
• The resulting collection of records, called a
  dynaset (short for dynamic subset), is saved as a
  database object and can therefore be easily used
  in the future.

11
QUERIES

• The query will be updated whenever the original
  tables are updated.
• Types of queries are select queries that extract
  data from tables based on specified values, find
  duplicate queries that display records with
  duplicate values for one or more of the specified
  fields, and find unmatched queries display
  records from one table that do not have
  corresponding values in a second table.

12
QUERY WIZARD

• Click the Create query by using wizard icon in
  the database window to have Access step you
  through the process of creating a query.

13
QUERY WIZARD

• From the first window, select fields that will be
  included in the query by first selecting the
  table from the drop-down Tables/Queries menu.
• Select the fields by clicking the gt button to
  move the field from the Available Fields list to
  Selected Fields.
• Click the double arrow button gtgt to move all of
  the fields to Selected Fields.
• Select another table or query to choose from more
  fields and repeat the process of moving them to
  the Selected Fields box.
• Click Next gt when all of the fields have been
  selected.

14
QUERY WIZARD

• On the next window, enter the name for the query
  and click Finish.

15
CREATE QUERY

• From the Queries page on the Database Window,
  click the New button.
• Select Design
• View and
• click OK.

16
CREATE QUERY

• Add fields from the tables to the new query by
  double-clicking the field name in the table boxes
  or selecting the field from the Field and Table
  drop-down menus on the query form. Specify sort
  orders if necessary.

17
CREATE QUERY

• Enter the criteria for the query in the Criteria
  field. The following table provides examples for
  some of the wildcard symbols and arithmetic
  operators that may be used. The Expression
  Builder can also be used to assist in writing
  the expressions.
• After you have selected all of the fields and
  tables, click the Run button on the toolbar.
• Save the query by clicking the Save button.

18
CRITERIA
19
CRITERIA
20
Calculated Fields

• Use Customers, Orders and Order details tables.
• Select CompanyName, PostalCode and Country fields
  from Customers.
• Select OrderID (sort as ascending), OrderDate,
  ShippedDate and Freight fields from Orders.
• Write the following expression to an empty field
• Amount CCur(UnitPriceQuantity(1-Discount)
  
• Click Totals button and select Sum from Totals
  for this field.
• Save as qryOrderAmount.
• CCur Converts Numeric value to Currency

21
Query

• Categories of products purchased by each
  customer We need Customers, Products and
  Categories tables, but because there is
  relationships all tables have to be included.
• Use Customers, Orders, Order details, Products
  and Categories tables.
• Select CompanyName and CategoryName fields.
• Write the following expression to an empty field
• Amount CCur(Order Details.UnitPriceQuantity
  (1-Discount))
• Click Totals button and select Sum from Totals
  for this field.
• Save as qryOrdersByCompanyAndCategory.

22
Inner Join Query

• Name of Customers who have same billing and
  shipping addresses
• Use Customers and Orders tables.
• Select CompanyName, Address and ShippingAddress
  fields.
• Join Address to Shipping Address.
• In order to eliminate duplicates from Properties
  set Unique Values to Yes.
• Save as qrySameAddress.

23
Outer Join Query

• Outer join is used to display all records
  regardless of corresponding records existing in
  joined table.
• By default if an item in primary table has no
  records in many table it is not displayed.
• Left All records with unique primary key are
  displayed when does not exist in many table.
• Right All records in many are displayed
  regardless of whether they exist in primary

24
Outer Join Query

• Display all employees and orders
• Open Employee table.
• Add a new record to that table.
• Use Employee and Orders table for the query.
• Select LastName and OrderID fields.
• Double click on the link.
• Select item 2 which is left join. Item 3 is right
  join.
• Save as qryLeftJoin.
• In order to do Right Join add a record to Order
  table.

25
Find Duplicates Query

• This query will filter out records in a single
  table that contain duplicate values in a field.
• Click the New button on the Queries database
  window, select Find Duplicates Query Wizard from
  the New Query window and click OK.

26
Find Duplicates Query

• Select the table or query (Customers) that the
  find duplicates query will be applied to from the
  list provided and click Next gt.

27
Find Duplicates Query

• Select the fields that may contain duplicate
  values (Region) by highlighting the names in the
  Available fields list and clicking the gt button
  to individually move the fields to the
  Duplicate-value fields list or gtgt to move all of
  the fields. Click Next gt when all fields have
  been selected.

28
Find Duplicates Query

• Select the fields that should appear in the new
  query along with the fields selected on the
  previous screen (CustomerName) and click Next gt.

29
Find Duplicates Query

• Name the new query (qryFindDuplicatesForCustomersI
  nSameRegion) and click Finish.
• Delete a Query To delete a table from the query,
  click the table's title bar and press the Delete
  key on the keyboard.

30
Find Unmatched Query

• Creates a query that finds records in one table
  that have no related records in another table.
• Click the New button on the Queries database
  window, select Find Unmatched Query Wizard from
  the New Query window and click OK. We will find
  customers with no orders.
• Select the table or query (Customers) that find
  unmatched query will be applied to from the list
  provided and click Next gt.

31
Find Unmatched Query

• Select the table or query (Orders) that will have
  no related records with the first table and click
  Next gt.
• Select the fields that should appear in the new
  query (CustomerName, City, region, Country) and
  click Next gt.
• Name the new query (qryCustomersWithoutMatchingOrd
  ers) and click Finish.

32
Crosstab Query

• Display quarterly average prices by products for
  1997
• Crosstab query requires only one table. If more
  than one table is to be used then a query has to
  be formed first.
• Select Order and Order Details tables. Select
  OrderDate, ProductID and UnitPrice fields.
  Specify criteria for the date as
• Between 1.1.1997 and 31.12.1997
• Save as qrySource.

33
Crosstab Query Contd.

• Display quarterly average prices by products for
  1997
• Click New button and select Crosstab query.
  Select from Queries tab qrySource.
• Select productID for the rows, OrderDate for the
  columns, specify quarters, select UnitPrice as
  average.
• Save as qry1997QtrSalesByProducts.

34
Pivot Table

• Display quarterly values of orders by salesperson
  and country for 1997
• Select Employee, Order tables and OrderSubtotal
  query.
• Select LastName, ShipCountry, OrderDate and
  Subtotal fields. Specify criteria for the date
  as
• Between 1.1.1997 and 31.12.1997
• Add a new field to specify quartes as
• 1997 Quarter Format(OrderDate q)
• Hide OrderDate
• Save as qry1997OrdersByCountryPT.

35
Pivot Table Contd.

• Select View menu and Pivot Table View.
• Drag Quarter to column area, Name to row area,
  Orders to Total area.
• Drag Country to row area after Name.
• Right click, select AutoCalc and Sum. This will
  add subtotals to the pivot tabel.
• You can export this to Microsoft Excel by
  clicking Export to Microsoft Excel button.

36
Pivot Chart

• Use qry1997OrdersByCountryPT query.
• Select Pivot Chart View from View menu.
• Using Right Click and Properties, you can change
  the chart title, axis titles, legends and chart
  type.

37
Select Query

• Use Customers table.
• Select ContactName, CompanyName, Address, City,
  Region, PostalCode and Country fields.
• For Country field use USA for criteria.
• Save as qryUSMailList.
• Right click on gray area to access to query
  properties. Change RecordSet type
• Dynaset Query is dynamic, when changes are made,
  table is also affected.
• Snapshot Query is static, no append or edit can
  be done.

38
Make-Table Query

• Use qryUSMailList.
• From Query Menu select MakeTable, when it asks
  for table name give as tblUSMailList.
• When you run the query, this will create a table
  of the query result.
• This is a snapshot.
• When you run it again, it is going to delete the
  old one.

39
Append Query

• To change a select query to an append query,
  choose Append Query from the Query type button's
  list.
• In the Append Query dialog box, you will specify
  to which table you want to append records. The
  table to be appended has to be created and has to
  have the same fields as the query.
• The Append To row will be displayed in the design
  grid indicating the fields that the query will
  append to the table.

40
Append Query

• Copy Employees table and paste as
  EmployeeUSA. Delete all records.
• Use Employee table to create a query.
• Select and Country fields. Set Append To for
  field to EmployeeUSA..
• Set criteria for Country field to USA.
• Run the query. It will append 5 rows to the
  table. If you run the query because of the key it
  is not going to append, you have to delete all
  records again.

41
Delete Query

• A delete query is used to delete records you no
  longer need in your database.
• To create a delete query, first create and run a
  select query in Design View, selecting the items
  you wish to delete. If the select query displays
  only the records you wish to delete, click the
  list arrow for the Query Type button in design
  view and then click Delete Query.
• Run the query to delete the records you
  previously selected.

42
Delete Query
43
Delete Query

• Austrian authorities have declared Ernst Handel
  insolvent and any orders which are not shipped
  has to be deleted.
• Copy Orders table and paste it as OrdersCopy.
• Copy Order Details table and paste it as
  OrderDetailsCopy. Create relationships.
• Use OrdersCopy table to create a query.
• Select , CustomerID and ShippedDate fields.
• Add ERNSH criteria for CustomerID field.

44
Delete Query

• Add Is Null criteria for Shippeddate field.
• Run query.
• Choose Delete query from Query menu.
• Sort and Show rows will be replaced by Delete
  row.
• Click Run.
• Check your files. There should be 2 rows deleted
  from OrdersCopy table and 7 records deleted from
  OrderDetailsCopy.

45
Update Query

• Create a select query with the necessary fields
  and selection criteria and click the Run button
  to preview the results.
• Click the list arrow for the Query Type button
  and select Update Query.
• Type the update values for the fields you need to
  update in their Update To text boxes.
• Click the Run button on the Query Design toolbar.
  Click the Yes button to confirm updating the
  records.

46
Update Query
47
Update Query

• Company decides to give 2 more discount to all
  customers.
• Copy Order Details table and paste it as
  OrderDetailsCopy2.
• Create query from OrderDetailsCopy2 table. Select
  Discount field.
• Choose Update Query from Query menu. Sort and
  Show rows will be replaced by Update To row.
  Change Update to row to Discount 0,02. Click
  Run.