Home

Tutorial 5
1. Modify table designs using lookup fields, input masks, and data validation rules
2. Use a subdatasheet and identify object dependicies.
3. Use like, In, And not, and Or operators in qaueries
4. Create a parameter query
5. Use query wizards to create a crosstab query, a find duplicates query, and find unmatched query
6. Create a top values query.

Important Points.
1. Rules of Data Normalization 5th rule - Isolate Semantically Related Multiple Relationships. There may be practical constraints on information that justify separating logically related many to many relationships.
2. A Lookup Wizard field lets the user select a value from a list of possible values.
3. For tables which have a one to many relationship, you can display records from the related table as a subdatasheet in the primary table's datasheet.
4. To display the position subdatasheet for a specific record, click the expand indicator + in the row of the record.
5. Literal display characters are the special characters that automatically appear in specific positions of a field value.
6. An input mask is a predefined format used to enter and display data in a field.
7. The Input Mask Wizard tool guides you in creating a predefined format for a field.
7a. Clicking the Property Update Options button gives you the abiltiy to Update Input Mase everywhere Phone is used.
7b. Property Propagation updates field properties in objects automatically when you modify a table field property.
7c. Object Dependencies task pane allows up to identify the dependencies that any object we might modify will effect the rest of the database.
8. The Validation Rule property value Specifies the valid values that users can enter in a field.
9. The Validation Text property will be displayed in a dialog box if the user enters a invalid value.
9a. Table validation rule compares one field value in a table record to anohter field value in the same record.
10. A pattern match select records with a value for the select field that matches the patter of the simple condition value.
11. The Like comparison operator selects records by matching field values to a specific pattern the includes the wildcard characters.
12. A list of values match selects records whose value for the selected field matches one of two or more simple condition values.
13. The In comparison operator lets you define a condition with two or more values.
14. A nonmatching value selects records whose value for the select field does not match the simple condition value.
The Not logical operator negates a criterion.
15. A parameter query prompts you for information when the query runs.
16. A crosstab query performs aggragate function calculations. 
16a. Use the crosstab query wizard for the quickest way to create a cross tab query.

1. Use as many lookup fields as you can to avoid data input error.

Tutorial 6

1. Design and create a custom form
2. View and print datatbase documentation
3. Add, select, move, resize delete, and rename controls
4. Add form headers and footers
5. Add a picture to a form
6. Create a multipage form with tab controls
7. Change a control to another control type
8. Add combo boxes to a form
9. Add a subform to a form
9a. Add calculated controls to a form and a subform
10. change the tab order in a form
11. filter data in a form. 

Important points.

1. To create a custom form you can modify an existing form or design and create a form from scratch.
1a. The record source property sepcifies the table or query tht porvides the fields for the form
2. A bound control is linked, or bound, to a field in the underlying table or query.
3. An unbound control is not linked to a field in the underlying table or query.
4. An unbound control that displays text is called a label.
5. A calculated control display a value calculated using data from one or more fields.
6. The toolbox is a specialized toolbar containing buttons that represent the tools you use to place controls on a form or report.
7. To create a bound control, you use the Field List button.
8. In the Detail section of the form is where the fields, labels and values are placed.
9. The grids are the dots in the detail section that help you place objects on the pallet.
10. The Rulers at the top and left edge define the horizontal and vertical dimensions of the form.
11. The form header and form footer sections let you add titles, instructions, command buttons, and other information to the top and bottom of your form.
12. Use the Image tool from the toolbox to place a picture in the form.
13. A multi page form can be created by inserting a page break control in the form or by using a tab control.
14. To place the tab control on the from click the Tab Control from the toolbox.
15. Click the Subform/Subreport tool on the toolbox to add a subform to the tab control.
16. The Control Wizard ask a series of questions and then creates the control.
17. Filter by selection finds records that match a particular field value or a portion of a field value.
18. Filter by Form and Filter for Input find records that match multiple selection criteria.
19. Advanced Filter/Sort lets you specify multiple selection criteria and specify a sort order for selected records.
20. You can save a filter as a query.
21. A Form's Record Source property specifies the table or query that provides the fields for the form. 
22. The Documneter is used to create detailed documentaion of all or selected objects in a database.
23. To print the Releationship window, click file, print Relationships.
24. To run Documenter - Tools, Analyze, documenter.
25. A Combo Box contain a list and a text box.
26. To remove a lookup feature on a field.
27. Open the table in design view, click on the field name that is a lookup, go to the field properties and click on the look up tab, click on the right side of the display control property, click on text box. 
28. To change the caption property value for a label.
29. Right click the label, go to the all propery tab in the propertios window, go to the caption value and change it.
30. To add a form header and footer go to view and select form header and footer.
31.  To add a picture, click the image tool on the toolbox, place it on the form where you want it, then select the picture to be inserted.
32. To change the background color of a form, go to the fill/back color button on the formatting toolbar and select the section that you want to apply the background color.  
33. Changing a control to another type.  Click on the control, click format, click change to, click other type of control.
34. Adding a calculated control to a form,  get the text box from the toolbox, type the name of the control into the name txt box, and then in the control source text box type the formula.
35. Tab order set the focus on the control.
 

Tutorial 7
 

1. Customize an existing report
2. Hide duplicate values in a report
3. Add calculated controls to a report
4. Design and create a custom report
5. Assign a conditional value to a calculated field
6. Sort and group data in a report
7. Add, move, resize, and align controls in a report
8. Modify control properties
9. Add a subreport to a main report
10. Add lines to a report
11. Define conditional formatting rules
12. Use domain aggregate functions
13. Add the date, page numbers, and title to a report
14. Create and modify mailing labels
15. Sort and group data in a report
16. Add a summary subreport to main report. 
 

Important Points.
1. A Report is formatted output.
2. Page Header - appears at the top of each page and is often used to place the labels for the fields in the detail section.
3. Page Footer - appears at the bottom of each page of the report and is used for placing the page number.
4. Report Header - appears once at the beginning of the report and is used for report title, logo, date, time, etc.
5. Report Footer - appears once at the end of the report and is used to summarize the data for the entire report by creating calculated fields with the text box from the toolbar.
6. Detail - this is used to place the most often displayed data in the report. Often the fields are placed in the detail section. Calculated fields can also be created here but only apply to the current record.
7. Group Header - this is an optional section which is used to differentiate between groups of data. Ten group headers can be created. For example: If you were organizing data by galaxy, star system, planet, continent, nation state, state, county, city, street, and persons name you would create ten group headers for this.
8. Group Footer - this is an optional section which is used to summarize the data for each group using the text box from the toolbox to create calculated fields. In the above example for group header we could determine the number of humans in the galaxy, star system, planet, continent, nation state, state, country, city, street, and last name.

You will notice that the report footer appears before the page footer. This is always the case for the last page of the report.
9. Most reports that you create will be based on queries.
10. The Iif( ) function allow you to assign one value to a calculated field if the condition is true and another value if the condition is false. Iif([age]<65,"Get back to work","Time to retire").
11. If you specify a sort key a grouping field, you can include a group header and a group footer.
12. You can specify up to ten grouped fields.
13. In the group footer you can write calculated fields to summarize, average, etc. the data.
14. Each label has a Caption property that controls the text that appears in the label.
15, The Align command provides different options for aligning controls.
16. The Can Shrink property, when set to Yes, reduces the height of a control that contains no data to eliminate blank lines in the printed report.
17. The Border Style property specifies a control's border type.
18. The Visible property is used to show or hide a control.
19. The Line tool on the toolbox lets you add a line to a report.
20. The Hide Duplicates property is used to hide a control on a report when the control's value is the same as that of the preceding record.
21. Sum Function - =sum([field name]) is placed inside a text box to calculate totals.
22. Conditional formatting lets you change the format of a report or form control based on the control's value.
23. Domain Aggregate functions provide statistical information about a set of records or domain.

24. The Date function is a type of calculate control that prints the current date on a report. =Date()
25. The Format Painter lets you copy the format of a control to other controls in the report.
26. An orphaned header section appears by itself at the bottom of a page.
27. The Keep Together property prints a group header on a page only if there is enough room on the page to print the first detail record for the group.
28. The Label Wizard provides templates for hundreds of standard label formats.
29. A Multiple-column report prints the same collection of data fields in two or more sets across the page.
30. The down, then across layout is also called newspaper-style columns or snaking columns.
 

Additional Functions:
http://www.earlwylie.com/datefunctionsinquery.htm

Tutorial 8

Export an Access table to an HTML document
1. View an HTML document using a Web browser
2. Use a Wizard to create a data access page for an Access table
3. Update a data access page using a Web browser
4. Sort and filter data access page records
5. Create a custom data access page
6. Create and use a PivotTable on a data access page
7. Create and use a PivotChart on a data access page
8. Import an XML file as an Access table
9. Export an Access table as an XML file
10. Export an Access query as an Excel worksheet
11. Add a hyperlink filed to an Access table
12. Create hyperlinks to Office documents

Important Points.
1. HTTP stands for hypertext transfer protocol.
2. HTML stands for hypertext markup language.
3. ISP stands for Internet Service Provider. An ISP is like an onramp to the freeway.  The ISP gives you access to the internet.
4. The Uniform Resource Locator(URL) identifies the address where the Web page is stored. Internet web pages begin with http://  URL.
5. A static web page shows the state of the database object at the time the page was created.
6. A dynamic Web page is updated automatically each time the page is viewed and therefore reflects the current state of the database at that time.
7. A interactive Web page is one in which the user can add records, delete records, and edit records. In access this is called a data access page.
7. A tag <> is the basic unit of the HTML document.
8. Connection string is a string of text that specifies the disk locton and the dtabase name used to connect a page to an access database.
9. Absolute Path is the location of the database. c:\access\filename.
10. A theme is a collection of formats tht determines the appearance of the controls, sections, body and text in a data access page.
11. A PivotTable is an interactive table that lets you analyze data dynamically using a we browser.
12. PivotTable toolbar lets you perform actions such as sorting and filtering when viewing and usin a pivottable.
13. The detail area, consist of the detail field and detail values.
14. The row area consist of a row field and row field items provides row groupings.
15. The column area consist of a column field and column field items provides column groupings.
16. The filter area consist of a filter field and filter field items lets you restrict which data appears.
17. When using a piviot table we are using office pivottable component is is one of the office web components.    
18. The PivotChart toolbar let you perform actions such as sorting and filtering.
19. The plot area provides a background for the data marker and gridlines.
20. A data marker is a bar, dot, segment, or other symbol that represents a single data value.
21. Data field identifies what which values the data markers represent and identifies each value displayed a a value axis label.
22. Category field identifies each value that displayed as a category axis label
23. Series field identifies the data markers subdivisions.
24. legend provides a list of the series field values and how hese values are indicated on the data markers.
25. filter field lets you restrict which data appears on the chart.
26. XML (Extensible markup language) is a programming language tht is similar in format to html and can exchange data between programs.
27.



Best Practice: In this chapter we will be using a template. You have probably noticed that in the right hand bottom of the screen when you open up Access 2002 the option new from template file. Under that are two hyper links. 1. General Template. 2. Template in MicroSoft.com. Templates found in the General Template are databases already set up that address specific database programming needs. Such as in inventory data base, personnel database, payroll, etc. Templates found in the Templates from MicroSoft.com contain additional database templates. The direct hyperlink the web based templates is http://officeupdate.microsoft.com/templategallery/. As you continue your work as a database developer do not reinvent the wheel. Look at the Access2002 templates that are available to use. Many of them can be used directly without any modification for projects that you may need to do. Other templates will only need slight modification to be useful to you. As a developer you will find that reusing database structures (templates) that you have built before leads to profit.
 

Important Points.
1. You should by this time have your tables and relationships built for your class project.
Best Practice: