1 / 37

Week 11

Database Design . Week 11. Agenda. Hybrid Review Create Tables Add, Edit Data Create Relationships in MS Access 2010 Queries. Create Tables. Create Tables. For the tables for the sample in the Wk10_TableInstanceChart.xlsx . Table Design View. Exercise: Create table for gender.

olisa
Download Presentation

Week 11

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. Database Design Week 11

  2. Agenda Hybrid Review Create Tables Add, Edit Data Create Relationships in MS Access 2010 Queries

  3. Create Tables

  4. Create Tables For the tables for the sample in the Wk10_TableInstanceChart.xlsx

  5. Table Design View • Exercise: • Create table for gender

  6. Datasheet View • Exercise: • Add data to the gender table

  7. Table Design View • Exercise: • Create Ribbon > Table Design in the Tables grouping • Identify your own PK • Moving the order of the rows

  8. Create Table with Lookups • Exercise: • Add data to the city entity using a look up wizard as the data type for the province

  9. Add Data to an Entity • Based on another entity • Exercise: • Create the students table (studentID, fname, lname and gender fields only)

  10. Edit an Entity • Exercise: • Complete the Students table • Go back and look at your default value (note it was not added to previous records entered)

  11. Edit Data in Datasheet View • Exercise: • Go back and add the age group and city for each of the students previously entered • Datasheet view • Record count • Add a new record • Search for a record • Sort the records

  12. Create relationships

  13. Create Relationships • Database Tools ribbon > relationships grouping • Exercise: • How to Edit a relationship • How to Create a relationship

  14. Want More Information Visit the following site. http://www.functionx.com/access/index.htm

  15. Queries

  16. Northwind Database

  17. Single Table Queries • Select fields to display • Sort by? • Simple Conditions • When you enter text into the criteria cell your text should be enclosed in quotes ("") to distinguish it from other expressions and operators that you may need to add.

  18. Working with Text

  19. Matching Text

  20. OR Statement

  21. IN Statement

  22. NOT Statement

  23. NOT IN Statement

  24. Using Wildcards Wildcard is a special character that can stand for either a single character or a string of text

  25. LIKE “Text*” Statement

  26. LIKE “*Text” Statement

  27. LIKE “[letter-letter]*” Statement

  28. Working with Numbers

  29. Working with Numbers For example, where X represents a number: <X finds values less than X. >X finds vales greater than X >=X finds values greater than or equal to X <>X finds vales not equal to X

  30. Working with dates

  31. Working with Dates • Ensure the criteria cell is type Date/Time • Each date must be enclosed by hash marks (#). • For example: • <#1/1/98# • finds dates earlier than 1 January 1998

  32. Working with Dates • For example: • =#27-Sep-50# • finds dates equal to 27 September 1950

  33. Working with Dates • For example: • Between #5/7/98# And #10/7/98# • finds dates no earlier than 5 July 1998 and no later than 10 July 1998

  34. Finding empty fields

  35. Finding Empty Fields Use the expression Is Null. Conversely, Is Not Null. The expression Null simply means "nothing".

  36. Hybrid / Homework • Read Calculating in Access Queries • Read Calculating Totals in Access • Read Working out Someone’s Age • Watch Multi-Table Queries Part 1 & 2 • Database Assignment Part 2

  37. Quiz Time

More Related