1 / 33

Microsoft Access

Microsoft Access. Creating Queries Expression Exercise- Criteria and Calculated Fields. Using Northwind.mdb. Orders Table. 1. Create Expression. Locate all Ship Names that contain a “q” anywhere in the name field. Criteria. Like "*q*". 2. Create Expression.

msmall
Download Presentation

Microsoft Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Access Creating Queries Expression Exercise- Criteria and Calculated Fields

  2. Using Northwind.mdb Orders Table

  3. 1. Create Expression • Locate all Ship Names that contain a “q” anywhere in the name field PgP MIS 202 Access Overview

  4. Criteria • Like "*q*" PgP MIS 202 Access Overview

  5. 2. Create Expression • Locate all Ship Postal Codes beginning with 050 PgP MIS 202 Access Overview

  6. Criteria • Like “050*” PgP MIS 202 Access Overview

  7. 3. Create Expression • Locate all Freight charges over $100.00, sort results in Descending Order PgP MIS 202 Access Overview

  8. Criteria • >100 PgP MIS 202 Access Overview

  9. 4. Create Expression • Locate all orders Shipped between 9/1/96 and 9/30/96 • Sort Ascending by Shipped Date PgP MIS 202 Access Overview

  10. Criteria • Between #9/1/96# and #9/30/96# PgP MIS 202 Access Overview

  11. 5. Create Expression • Number of days before today an order shipped • Sort Descending PgP MIS 202 Access Overview

  12. Calculated field • DaysSinceShip: Date()-[ShippedDate] PgP MIS 202 Access Overview

  13. Using Northwind.mdb Order Details table

  14. Create Expression • Locate all orders where Quantity is an odd number • Hint: search help (Find tab) for functions that contain the word “remainder” PgP MIS 202 Access Overview

  15. Criteria • [Quantity] mod 2 <> 0 PgP MIS 202 Access Overview

  16. Create Calculated Field • Total discounted amount is greater than $1000.00 PgP MIS 202 Access Overview

  17. Calculated Field • Discounted Amount: UnitPrice * Quantity * Discount • With criteria: > 1000 PgP MIS 202 Access Overview

  18. Using Northwind.mdb Employees table

  19. Create Calculated Field • Determine employee seniority PgP MIS 202 Access Overview

  20. Calculated Field • Seniority: Date()-[hiredate] • Or • Years seniority: DateDiff("yyyy",[hiredate],Date()) PgP MIS 202 Access Overview

  21. Microsoft Access Form and Report Basics Delivering the data as required-tabular, graphical…

  22. Forms • Used to maintain (add/delete/edit) the data in underlying tables • Finding data using Find dialog box • Note available wildcard characters • *, ?, [], !, -,# • Wizards-show typical layouts • AutoFormat, to try different layouts quickly PgP MIS 202 Access Overview

  23. Wildcard Characters PgP MIS 202 Access Overview

  24. Forms and SubForms • Use to show data from related tables • Tables have one to many relationship • Example, View the many orders for one customer • Display lot of data in a small space PgP MIS 202 Access Overview

  25. Forms and SubForms • Main form can have any number of subforms if you place each subform on the main form. • Can nest up to ten levels of subforms. Could have a main form that displays customers, a subform that displays orders, and another subform that displays order details PgP MIS 202 Access Overview

  26. Form/SubForms Example • Open Northwind.mdb, Forms, ‘Customer Orders’ PgP MIS 202 Access Overview

  27. Form/SubForms Example • Form contains Customers table fields: • CompanyName • Country • This form is linked to subForm ‘Customer Orders Subform1’ PgP MIS 202 Access Overview

  28. Form/SubForms Example • Subform1 contains Orders table fields: • OrderID • RequiredDate • This subform is linked to subForm ‘Customer Orders Subform2’ PgP MIS 202 Access Overview

  29. Form/SubForms Example • Subform2 contains OrderDetailsExtended query fields: • ProductName • UnitPrice • Quantity • Discount • Extended Price (a calculated field) • Note-this query is based on the tables ‘Order Details’ and ‘Products’ PgP MIS 202 Access Overview

  30. Form/Subform Relationships PgP MIS 202 Access Overview

  31. Reports • Typically destined for printed output • Can be based on table(s) or query or SQL statement • Report Wizard can do most of the work • For the finishing touches modify in Design View PgP MIS 202 Access Overview

  32. Reports and Forms- Design View • Wizards can do most of the work, but to tailor forms/reports to what you want, you must understand how to modify them in Design View • Examine property settings-similar to Visual Basic forms and controls PgP MIS 202 Access Overview

  33. Report and Form Graphics • Graphics can improve appearance, but • Possible performance penalty due to way Access stores graphic images • Be aware of “Image” object “SizeMode” property settings of clip, stretch, zoom PgP MIS 202 Access Overview

More Related