Download
microsoft access n.
Skip this Video
Loading SlideShow in 5 Seconds..
Microsoft Access PowerPoint Presentation
Download Presentation
Microsoft Access

Microsoft Access

0 Views Download Presentation
Download Presentation

Microsoft Access

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