Advanced Query Techniques and Form Design in Microsoft Access

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