1 / 23

Day 19: Access Chapter 4

Day 19: Access Chapter 4. Tazin Afrin Tazin.Afrin@mail.wvu.edu March 20, 2014. Advanced Query. Download the data file Rename : YourLastName_inclass3. Establish table relationships. Create appropriate relationships for the following tables.

erasto
Download Presentation

Day 19: Access Chapter 4

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 19:Access Chapter 4 Tazin AfrinTazin.Afrin@mail.wvu.edu March 20, 2014

  2. Advanced Query • Download the data file • Rename : YourLastName_inclass3

  3. Establish table relationships • Create appropriate relationships for the following tables. • Do not enforce referential integrity and do not enable cascade updates or cascade deletes. • AvgTempDiff and EPTData • HINT: The latitudes and longitudes have been set up to link the two tables together along with the date.

  4. Query A • Create a new query that lists all different fields from both the EPTDataand AvgTempDifftables. • Display the date, latitude, and longitude only once. • HINT:The query returns 48,340 records.

  5. Query B • Create a copy of Query A. • Use the YEAR() function to retrieve the corresponding value from the date. • Display only readings taken in the year 2003. • HINT:The query returns 4,320 records.

  6. Query C • Create a new query. Include all fields from Query A except the latitude and longitude fields. • Display the average by date for all data in the query. • Sort ascending by date. • HINT:The query returns 85 records.

  7. forms • A form is a database object that is used to add data into or edit data in a table. • Reasons to use forms : • You are less likely to edit the wrong record by mistake. • You can create a form that shows data from more than one table simultaneously. • You can create Access forms to match paper forms.

  8. Form Tools • The Form tool is used to create data entry forms for primary tables. • Create –> Forms group ->Form ->Form Design ->Blank Form ->Form Wizard

  9. Create form • Form • Creates a form for the currently selected table • It will also include a subform for any table that has a foreign key that is related to the main table • Form Design • Creates a new, blank form in design view • Design view allows you to add labels and fields and place them how you would like

  10. Create form • Blank Form • Creates a new, blank form in layout view • Layout view allows you to add labels and fields, but it automatically places them based on the style • Layouts • Stacked layout form • displays fields in a vertical column • displays one record at a time. • Tabular layout form • displays records horizontally • data values in rows under the labels.

  11. Create form • Form Wizard • Create a form using any combination of fields from any number of tables • Allows you to select how to group things for subforms • Split Form • combines two views of the same record source - one section is displayed in a stacked layout and the other section is displayed in a tabular layout. • splitter bar divides the form into two halves.

  12. Create form • Multiple Items Form • Displays multiple records in a tabular layout similar to a table’s Datasheet view. • Gives you more customization options than a datasheet

  13. Create form • Datasheet Form • A replica of a table or query’s Datasheet view except that it still retains some of the form properties. • Can add field from multiple tables

  14. Modify form • Add a field • Delete a field • Layout control • Add theme • Change or remove sorting

  15. Form section • The Form Header section displays at the top of each form. • The Detail section displays therecords in the form’s record source. • The Form Footer section displays at the bottom of the form.

  16. Control types • Text box - displays the data • Label control - a literal word or phrase to describe the data. • Bound control - a text box that is connected to a field in a table or query. • Unbound control - not connected to a source of data. • Calculated control - contains an expression that generates a calculated result.

  17. Designate primary keys • TeamRecords: Team • Divisions: DivisionID • Playoffs: Team

  18. Establish table relationships • Create appropriate relationships for the following tables. • Enforce referential integrity and enable cascade updates, but do not enable cascade deletes. • TeamRecordsand Divisions • TeamRecordsand Playoffs.

  19. Create a form with a subform • Open Form Wizard • The main form should display the name of each league and division in the Divisions table. • The subform should display the individual team names, the percent of games won, and whether they participated in a playoff game.

  20. Create a form with a subform • We wish to create a form with subform. • Name the form League Results and subformLeague Results Subform. • Select Datasheet layout and a design style of your choice.

  21. Add records to table using a form • Open the subform. • Click the NEW RECORD button at the bottom of the subform. • Add the following information. • Team: XYZ • Percentage of Wins: 40 • Division Playoffs: Yes • Delete the record from both tables

  22. Next class • Access Chapter 4 • Reports

  23. Thank You Log Off

More Related