1 / 18

Day 16: Access Chapter 2

Day 16: Access Chapter 2. Tazin Afrin Tazin.Afrin@mail.wvu.edu October 10 , 2013. objectives. Single table query Multi-table query. Single table queries. Which customer currently has a balance over $5000? Create a query

harsha
Télécharger la présentation

Day 16: Access Chapter 2

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. Day 16:Access Chapter 2 Tazin AfrinTazin.Afrin@mail.wvu.edu October 10, 2013

  2. objectives • Single table query • Multi-table query

  3. Single table queries • Which customer currently has a balance over $5000? • Create a query • A query enables you to ask questions about the data stored in a database. • Need reference of table • For previous question refer Account table

  4. Query design view • Query Design view enables you to create queries; the Design view is divided into two parts – • the top portion displays the tables • the bottom portion (known as the query design grid) displays the fields and the criteria.

  5. Single table query • Create in two ways – • Simple query wizard: • provides dialog boxes to guide you through the query design process. • helpful for users who are not experienced. • Query design tool: • For more advanced users • Provides the most flexibility

  6. Query datasheet • Display the results of the query. • Query’s datasheet looks and acts like a table’s datasheet • Usually a subset of the records from the table. • Only shows the records that match the criteria. • Allows you to enter a new record, modify an existing record, or delete a record. • Any changes made in Datasheet view are reflected in the underlying table. • Caution: Changes Made to Query Results Overwrite Table Data

  7. Single – table select query • A select query displays only the records that match the criteria entered in Design view. • Create -> Queries -> Query Design • Show Table Dialog Box appears • Select table • Add query

  8. Use query design view • Consists of two parts: • The top portion contains tables with their respective field names. • The bottom portion (known as the query design grid) contains columns and rows. • Field row • Table row • Sort row • Show row • Criteria row

  9. wildcard • A wildcard is a special character that can represent one or more characters in the criterion of a query. • * mark • Represents one or more characters • If name start with Smsearch by Sm* • S*ndwill return Sand, Stand, or StoryLand. • ? mark • Stand for a single character • H?ll will return Hull, Hill, or Hall etc.

  10. Comparison operators • A comparison operator can be used in the criteria of a query. • Such as – • equal (=) • not equal (<>), • greater than (>) • less than (<) • greater than or equal to (>=) • and less than or equal to (<=) • Query for salary >5000

  11. Null • Null is the term Access uses todescribe a blank field. • Example: • Is Null • For an Employee field in the Customers table when the customer has not been assigned a sales representative. • Is Not Null • For the ShipDate field; a value inserted indicates the order was shipped to the customer.

  12. Establish Logical criteria • The AND logical operator returns only records that meet all criteria. • The OR logical operator returns records meeting any of the specified criteria. • The NOT logical operator returns all records except the specified criteria.

  13. Query operation • Copy a query • Run a query

  14. Multi-table query • A multi-table querycontains two or more tables. • Enables you to take advantage of the relationships that have been set in your database. • Related tables should already be established when you create a multi-table query. • Related tables are tables that are joined in a relationship using a common field.

  15. More operations • Add additional tables • Get answers using query • Modify multi-table query • Add and delete fields

  16. Common mistake • Use tables in query who are not joined. • Results in more rows than usual • For example, If we create a query on Customer table and Branch table but forget to join them. • Suppose Customers table contains 11 records, and the Branch table contains 5 records. • Since Access does not know how to interpret the unrelated tables, the results will show 55 records –every possible combination of customer and branch (11*5). • When add new table, create relationships. • Temporary join line in query will not create an actual join line between tables.

  17. Next class • Access Chapter 3 • Order of operations. • Create a calculated field. • Create expressions. • Use built-in functions. • Perform date arithmetic.

  18. Thank You Log Off

More Related