1 / 31

Introduction to Access

Introduction to Access. BUS 782. Access Objects. Tables Open Design New Wizard Queries Forms Reports Pages. Creating a Table. Create table in design view Field name Field data type Create table by using wizard Create a primary key Enter data. Demo. Queries Forms.

eparkhill
Download Presentation

Introduction to 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. Introduction to Access BUS 782

  2. Access Objects • Tables • Open • Design • New • Wizard • Queries • Forms • Reports • Pages

  3. Creating a Table • Create table in design view • Field name • Field data type • Create table by using wizard • Create a primary key • Enter data

  4. Demo • Queries • Forms

  5. Querying Database ISYS 363

  6. Basic Query Language Operations • Selection • Projection • Join • Aggregates: Max, Min, Sum, Avg, Count • Totals and SubTotals • GroupBy • Calculated fields

  7. Selection • Selection operation retrieves records that satisfy user’s criteria.

  8. Projection • Projection operation defines a vertical subset of a table and retrieves only the specified fields.

  9. Natural Join • The two tables must have common attributes: • Key and foreign key. • Combines two tables to form a new table where records of the two tables are combined if the common attributes have the same value.

  10. Join Example • Faculty File: • FID Fname • F1 Chao • F2 Smith • Student File: • SID Sname FID • S1 Peter F1 • S2 Paul F2 • S3 Smith F1 • Faculty Join Student = • Note: What if FID is named AdvisorID?

  11. Join Example • StudentCourse File: • SID CID • S1 ISYS263 • S1 Acct101 • S3 ISYS363 • S2 ISYS263 • S2 Fin350 • S2 Acct101 • Student File: • SID Sname FID • S1 Peter F1 • S2 Paul F2 • S3 Smith F1 • Course File: • CID Cname Units • ISYS263 IS IntroPeter 3 • ISYS363 MIS IntroPaul 3 • Acct101 accounting 3 • Fin350 Finance Intro 3 (Student Join StudentCourse) Join Course

  12. Aggregate Functions • Max, Min, Sum, Count, Avg • View/Totals • Ex. Student: SID,Sname, GPA, Sex, Major • How many students in this University? • What is the overall average GPA?

  13. Aggregates by Group • How many students in each major? • Compare male students and female students average GPA.

  14. Examples • Customer: CID, Cname, City, Rating • Orders: OID, Odate, SalesPerson, CID • Queries: • Find customers live in San Francisco. • Produce a customer report that shows CID, Cname, and Rating. • Number of customers in each city • City, NumbeOfCustomers • Produce a report that shows the number of orders for each customer: • CID, Cname, TotalNumberOfOrders

  15. Examples • University Database: • Student, StudentCouse, Course • Minimum enrollment example. • Order Processing Database: • Customer, Orders, OrderDetail, Product • MIS report • Total amount for each order • Criteria applied to subtotal

  16. Calculated Fields • Rename a field: • NewName:OldName • Define a calculated field: • Tax:salary*.15 • Age:Year(Now()) – Year(DOB) • Amount:qty*price • IIF function • Calculated field based on subtotal: • University tuition rules: • If total units < 5 then tuition= 600 • Otherwise tuition =600 + 200 for each additional unit

  17. Criteria • >, >=, <, <=, =, <> • Range: BETWEEN 1/1/03 AND 12/31/03 • Wildcard: • ? – match any one character • “K?NG” • * - Match any number of characters • “C*”

  18. Complex Condition • University admission rules: Applicants will be admitted if meet one of the following rules: • 1. Income >= 100,000 • 2. GPA > 2.5 AND SAT > 900 • An applicant’s Income is 150,000, GPA is 2.9 and SAT is 800. Admitted? • Income >= 100,000 OR GPA > 2.5 AND SAT >900 • How to evaluate this complex condition?

  19. Scholarship: Business students with GPA at least 3.2 and major in Accounting or CIS qualified to apply: • 1. GPA >= 3.2 • 2. Major in Accounting OR CIS • Is a CIS student with GPA = 2.0 qualified? • GPA >= 3.2 AND Major = “Acct” OR Major = “CIS” • Is this complex condition correct?

  20. Complex condition • Rating = “A” OR Rating = “B” • Cname = “Chao” OR Cname = “Smith” • Rating = “A” OR Rating = “B” AND City=“SF” • (Rating = “A” OR Rating = “B”) AND City=“SF”

  21. Sorting • One field sorting • Two fields sorting

  22. Other Queries • Update query • Delete query • Parameter query • CrossTab query

  23. Forms • Form wizard • Main/Sub Form • Based on two tables with 1:M • Based on a query created from two tables with 1:M • Pivot Table form • AutoCal

  24. Access Tools for Import/Export • File/Get External Data • File/Export

  25. Excel’s Database Tools • Data • Sort • Filter • Subtotals • Pivot table/Pivot chart • OLAP: On Line Analytical Process • DrillDown, RollUp, ReAggregation • Import external data • Import data • New web query • New database query

  26. Data/Import External Data • New Database Query • Select <New Data Source> and click OK.

  27. OLAP Cube • Data/Import External Data/New database query: • Define query • In the last step, choose Create OLAP Cube

  28. OLAP Wizard • Source of data to be summarized • Analysis dimensions

  29. Web Query • Data/Import External Data/New web query • Click table on the web page • Use the Save Query button to save web query • Web query can be refreshed. Copy/Paste can not.

  30. Import Data • Data/Import External Data/Import Data

  31. Data Consolidation • Help, F1

More Related