Home

Access 2007-Manual - Human Resources

1. sssse 65 Do EE 170 lee e FEN 54 Integrate Programs Exporting Data ees 158 Link To An External Table 1445 24 20 eed 168 Lo k p Field eegener 60 Wan linge Ee 154 Make Table TEEN 103 Many To Many Relationships eene 78 178 INDEX Modifying The Report Property Sheet 143 Msword Mail Merge With Access Data 150 RID ISC EVES Eeer 1 One To Many Relationships eene 76 One To One Relationships eeeeeeeeeeenreeenenene 75 Ons Ee CN EE 166 Previewing amp Printing Formatting The Datasheet 26 PUTA YG T 50 Print A Rep rt suse Dau peli alee ae maid deiude 148 Printing amp Previewing Formatting The Datasheet 26 Property Edel e E 143 Queries Delete 1 5 Deeg Eden Bes 106 Queries Make Table 103 Queries Update 5i etae n Ae roa o USE tt RECHNEN EA Tr SERERE 101 Queries In Design View 2 ee i pose sete ed deni 100 Querie S Ras EHE eo IRR ER RO 88 Query Wizard Find Duplicates see 90 Query Wizard Find Unmatched ee 91 Query Wizard Simple Boa eG Oa Ie I RIEN RE NEGRAS 89 Relationships EE 32 74 Replace Or Edit Data sodes aoreet taiaroa eieae 18 Report Pining eo de IS NI dra POR FUE a 148 Report Ke EE 133 179 INDEX Reports Report OG ooi ee ettet edite lude 135 REPOT e o o HE qs aH ie 132 Select Queries
2. 12 Displaying Records In A Subdatasheet AAA 14 Finding And Editing Records eese 16 Replace Or Edit Dabo tie od o eet toutes te dunes 18 Sorting Records EE 20 Applying And Removing Plterg eee 23 Filtering By Selection ee INN Nees 24 Filter By FoM T deet eege 25 Previewing amp Printing Formatting The Datasheet 27 Tables Taking Control With Design View esses 31 Relationships uoa eee dee ed aaa 33 Deleting Objects In ACCBSS eo o pa eredi leiten ae 35 Designing The Database re a Res 37 Create A New Database gesteet heet 44 Create KO 46 Define Fields TEEN 49 Primary Key SRM 53 TABLE OF CONTENTS Field Prope EE 55 Input EE 57 Default E E 59 Validation Rule door diae cao tacite CA REESS 61 Lookup Eieldds oco itt iode md up uode dua d 63 Import Data From An External Source 67 Importing From An Excel Spreadsheet sse 68 Importing Data From An Access Database sss 73 Creating Relationslpsa ia e deele 77 Creating One To One Relationships A 78 Creating One To Many Relaponsbps ees 79 Creating Many To Many Relattonsbtpes esses 8l Cascade Options 32 5 nts Ror eege 87 Queries ote NND EI HEU toit 9 Query Wizard Simple ies tt aba eere UH Sx EH ERE ERU 92 Query Wizard Find Dupltcates sene 93 Query Wizard Find Unmatched eee 94 Select Queries In Design View eese
3. wedon tne d tlie Label Bu ISBN fey sissies n mm eni control Click the Label control and then press the Delete key um TIERE AE Sumi Total Pribe 000000 SSIS LASERS Been MESE Sum Total Sei EE SE FE Report Footer 149 EXERCISE ADD A CUSTOM FIELD TO A REPORT 7 To make the report readable draw a dividing line between the detail Qty and the aggregate calculated for Qty On the Design tab in the Controls group click the Line control Draw a line above the Sum Qty text box in the T Footer section 8 To thicken the line click the Line Thickness control in the Controls group Select the thickness style you want 9 Click the Report View or Print Preview button on the Status bar in the lower right corner of the screen to examine the report Go back to Design View to make any modifications CLName Zehper T To102 ISBN Oty Price Total Price 15587 2 514 95 29 90 i Transaction Total 29 90 T To107 ISBN Oty Price Total Price 59652 2 34 99 534 99 7614c 1 12 95 12 95 78973 2 34 99 334 99 S Transaction Total 82 93 Customer Total 5112 83 Total Purchases 365 48 Page 1 of1 10 Save the report 150 EXERCISE APPLY AN AUTOFORMAT TO A REPORT The AutoFormat feature for reports allows you to apply a predefined design style to an existing report If you want to change the style for the Books4U Customer Sales Repor
4. Tabular Stacked Remove Control layouts can be switched from tabular to stacked or from stacked to tabular Select all of the controls of the layout On the Arrange tab in the Control Layout group click the layout type you want Removing a control from a control layout allows you to place it anywhere on the form without affecting the positioning of any other controls Once you select the control s you want to remove from the layout click Remove in the Control Layout group on the Arrange tab 133 EXERCISE REMOVE CONTROLS FROM A CONTROL LAYOUT On the Employee subform the employee information is contained within a stacked control layout We cannot move one control without moving the entire group To enhance the appearance of the form modify the position of the employee data on the form 1 Click the Layout View button on the Status bar in the lower right corner of the screen 2 Select the three text box controls of employee data While holding down the shift key click the EZ ELName and Title text box controls Notice that the label controls are also selected 3 Onthe Arrange tab in the Control Layout group click Remove or you can right click on any of the selected controls point to Layout and click Remove The orange layout selectors appear for each control 4 Click on any blank area of the form to deselect the controls 5 Alllabel and text box controls can move independently Reposition the controls o
5. Query Structure Query Types Steps to Create Select Queries in Design View For more flexibility in retrieving data use the Query Design feature Creating a query in Design view gives you added control in creating the query structure Data can be retrieved from multiple sources as with the Wizard Data can be further restricted in Design view by specifying conditions for a field select customers in one city examine transactions on a specific day or obtain a list of books from a particular publisher Results can also be ordered The types of queries created with the Wizard are Select queries These queries display selected fields from one or more tables In addition to Select queries Action queries can be created in Design view Action queries make changes to data or the data structure in the database Four types of Action queries are available in Access update append make table and delete More about Action queries in the next section On the Create tab in the Other group click the Query Design button The Query grid and Show Table windows display From the Show Table window select the desired table s Click the Add button for each table Click Close From the table s field list double click the required fields to move them down into the query grid Select the fields in the order you want them to appear in the datasheet Enter Criteria to restrict the data retrieved Use the Sort feature to order the data Clic
6. 6 Click the pull down arrow to the right of Contacts Navigation and then Object Type click Object Type at the drop down list to display all database objects Tables and Related Views Modified Date 7 You can now open any object in Design view or Layout view to make S E e Filter By Group changes as appropriate for your application Contacts Supporting Objects Show All 172 APPENDIX C LINK TO AN EXTERNAL TABLE Linking allows you to connect to a table in another database without importing it Importing the table makes a copy that becomes part of your database Any changes to the original table are not reflected in your copy Linking allows you to modify and view the latest data in both the source and destination databases without creating and maintaining two copies of the same data Although you can edit and update data in linked tables you are prevented from making changes to the structure of the tables in the destination database You cannot add delete or modify the data type of a source field You cannot link to queries forms reports macros or modules We will link to the Products table in the Northwind database 1 On the External Data tab in the Import group click Access 2 In the File name text box type the name of the source database or click Browse to display the File Open dialog box Navigate to the Northwind database in the Access 07 Class folder Click Open 3 Click Link to the da
7. CREATE MAILING LABELS Label Reports Access 2007 gives you several different options for creating labels containing data stored in your Access tables Labels are created as a report that is formatted to fit the desired label Mailing labels are the most common use of labels but any Access data can be printed in a label format for a variety of purposes With mailing labels the report retrieves the address data from the table or query containing the addresses Printing the report gives you a single label of each address from the underlying record source Label Wizard The Label Wizard is the simplest method used for creating labels The wizard walks you through a series of questions to determine the label format and the fields to display You can customize the label size format font font size and font color Steps to Create qu DE 1 Mailing Labels Click the object in the Navigation pane that contains the fields to be printed on the label On the Create tab in the Reports group click the Labels button The Label Wizard is launched Choose label type font size and color Select the fields that will be displayed on the label Select a field to sort the labels Name the label report Click Finish 159 EXERCISE CREATE MAILING LABELS 1 Click the Customers table object in the Navigation pane 2 Onthe Create tab in the Reports group click the Labels button Access launches the Label W
8. Object Procedure Multiple Objects You can import multiple objects in a single import operation New Objects Each import operation creates a new object in the destination database You cannot overwrite an existing object or append records to an existing table by using an import operation Skipping fields amp records You cannot skip specific records or fields when importing data from a table or query You can import the table definition without any data Relationships Relationships between source tables can be imported Table Definition Table definitions without the data can be imported Table structure Record Source objects When importing a query form or report you must also manually import the source tables used to create these objects Lookup Fields Lookup items defined in the field properties window will automatically be imported with the table If the lookup items were created from a related table that table must also be imported Subforms Subreports Subforms and subreports are not automatically imported Each object and their underlying sources must all be imported Queries A query can be imported as either a table or a query Underlying tables must also be imported for queries as queries 73 EXERCISE IMPORTING FROM AN ACCESS DATABASE In this exercise we will import a table from an Access 2007 database accdb and a table from an Access 2003 database mdb
9. Set P as the primary key and name the new table Publishers Open the Publishers table Check that the data type formats are both text format 72 IMPORTING DATA FROM AN ACCESS DATABASE With or Without Importing a table from another Access database offers the option Data of importing with or without data definition only If you import the definition of a table the table is created as in Design View Field names and data types are easily modified Importing table relationships is also optional There may be restrictions to the modifications on tables with data and or established relationships included Appending If your goal is to add records from another database to an existing Records table consider importing the records to a new table and then Pre creating an append query We will discuss queries later in the manual You cannot append records to an existing table from another database during an import operation Access will not overwrite the existing table If the same table name is used in an import operation Access will created another version of the table for example importing an Employees table to an already existing Employees table will result in Employees1 vious If the source database is an mdb or accdb file you can import Versions tables queries forms reports macros and modules Only tables can be imported from mde or accde files Tips When Importing Objects from Another Access Database
10. To see how a selected mask works use the Try It box To change the Input Mask list dick the Edit List button EEN ag Input Mask Data Look General Lookup r UT Field Size 255 Social Security Number Format Zip Code Q r Input Mask Extension Caption Password Default Value Validation Rule Long Time Validation Text Tylt __ _ a to be entered in this field Required No Allow Zero Length Yes Indexed No emt ss ees Unicode Compression Yes G o J LD IME Mode No Control IME Sentence Mode None Smart Tags vj 3 Place the cursor in the Input Mask field Click on the Build button LJ that appears at the end of the box The Input Mask Wizard window opens 4 Select Phone Number Click the Try It field Be sure to place the cursor at the beginning of the input box 5 Type 6025559999 Note that the dashes are inserted for you However the dashes are not actually saved in the database 6 Click the Finish button The Input Mask Wizard window closes 7 Save the Customers table 58 DEFAULT VALUE Auto Fill The Default Value property is used to define a value that will automatically be entered for the field when you create a new record The values in existing records are not affected Although the default value is automatically entered into a field the value can be modified in datasheet view Expressions An Expression is a formula that calculates a value For example expr
11. Cancel 68 EXERCISE IMPORTING FROM AN EXCEL SPREADSHEET 5 10 To specify the data destination click the radio button to Append a copy of the records to the table Use the pull down box and select Customers Click OK Note Using append in this case will allow us to use the table structure that we ve created for the Customers table By choosing Import here Access would create a new table and populate it with the data However there would be no structure defined and no formatting specified Formatting fields after the data has been entered may become problematic The Import Spreadsheet Wizard opens Click the Show Worksheets radio button and choose the worksheet that holds the desired data Select the Customers worksheet Click Next E Your spreadsheet file contains more than one worksheet or range Which worksheet or range would you like Show Worksheets S Show Named Ranges Seet Sample data for worksheet Customers i F Ee LNa The next window states that Access will use the column headings found in the Excel worksheet as field names for the database table This is a default action Click Next Before Access imports the data it a gives you one last chance to change the table name Leave the name as Customers and Click Finish Do not save the import steps Click Close Open the Customers table in
12. i8 Herz Table Table SharePoint Table Templates Lists v Tables External D 7 The Design View for Tablel opens 48 DEFINING FIELDS Creating Data Structure When creating a table in Design View you are creating the table s Field Name Data Types structure Once you ve defined information about the table s fields you can switch back to Datasheet View to enter your data For each field in your table you will define a field name a data type and an optional description The field properties section in the lower half of the work area displays the properties for the active field The properties will vary depending on the active field To create a new field enter a Field Name in the Field Name column Field Names consists of up to 64 characters letters or numbers including spaces Data Types determine how data is stored They also define the allowable values for each field in the table Data Type Description Size Text Alphanumeric data numbers letters symbols and special characters Up to 255 characters Memo Alphanumeric characters Supports rich text Store up to 2 GB Display formatting bold italic bulleted lists 65 535 characters Number Positive or negative values that can be used in Up to 16 bytes of data arithmetic calculations Date Time Date and time information 8 bytes Currency Monetary values No rounding during calculations
13. 146 Applying And Removing Plterg eee 22 Autoformattmg Reports i deed 146 Blank Form Tool auster RR ninnau Rs 117 Calculating Aggregates In Queries eee 110 Calculating Aggregates On Tables eese 109 Calculating Aggregates ii So ee RAS 108 Calculations RE EE 98 C scad e 84 Clone The Database uoc me ues tai Dad uM dpi atus 162 Compact And EE 169 175 INDEX Conditional And Or Ouertes 95 Conditional Formatting esee 130 EE 128 Eeer 138 Create A Form Adding Controls esses 120 Create A Form Blank Form Tool eese 117 Create A Form Form Tool ee cete tecto aati 114 Create A Form Form Wizard essere 119 Create A Form Split Form Tool eene 116 Create A New Database ee ro eek dele diea edu qoe 42 Create A Report Report Tool 133 Create A Table ie Bate Bi opa dedicati d ee ee Sia 44 Create Mailing Labels iet entree ere het ea tee bo ees 154 Creating Many To Many Relationships esses 78 Creating One To Many Relaponsbups eese 76 Creating One To One Relationships A 75 Creating Relationships 23 02 3400 aac dace eei 74 Creation Tools POLIS EE 113 Custom Field Iu A Reports 6534 e eee 144 Database E 2 Database EE 8 Database Templates oco ior a 167 Datasheet Formatting eet tat i etis vig 26 176 INDEX Datasheet TE 12 Default E eS RP EHE 56
14. Customers Employees 7 Click Customers on the Tables tab Click OK Gelee Products Shippers Suppliers 8 Click the expand indicator on any record in the Employees table Notice the subdatasheet display Close Employees Click No to save layout changes Link Child Fields EmployeeID Me Link Master Fields EmployeeID Cancel 15 FINDING AND EDITING RECORDS Find Command Find Button Wildcard Character Steps to Find a Record The Find command can be used to quickly move the insertion point to a specific record in a table This is a time saving feature when your data tables are very large The first step in locating a record is to position the insertion point in the column of the data field you want to find Once the T cursor is placed in that field click on the Find button 7 located in the Find group on the Home tab of the ribbon DS The Find and Replace dialog box opens In the Find tab type in the data item value you seek and click the Find Next button The first record that meets the search criterion is automatically selected in the field of the table You can now edit the data in that record You can make your search more specific using the options located in the dialog box Clicking Find Next will take you to the next occurrence In Access the asterisk is a wildcard character A wildcard is a symbol used in a search to replace unknown characters For example if y
15. Datasheet view to examine the EERS data Toggle to Design view to check formatting Delete test rows 69 EXERCISE IMPORTING FROM AN EXCEL SPREADSHEET Import data into the Transactions table next The procedure is the same as defined on the previous pages Do a quick check of Excel column headers and data formats to ensure that they match the definitions in the database tables 11 Close all tables 12 On the External Data tab on the ribbon in the Import group click the Excel button The Get External Data Excel Spreadsheet wizard opens 13 In the File Name box specify the Excel file that contains the data Use the Browse button to locate the Transactions Data file in the Access 07 Class folder on your Desktop 14 To specify the data destination click the radio button to Append a copy of the records to the table Use the pull down box and select Transactions Click OK 15 The Import Spreadsheet Wizard opens Click the Show Worksheets radio button and choose the worksheet that holds the desired data Select the Transactions worksheet Click Next 16 The next window states that Access will use the column headings found in the Excel worksheet as field names for the database table This is a default action Click Next 17 Before Access imports the data it gives you one last chance to change the table name Leave the name as Transactions and Click Finish 18 Do not save the import steps Click Clos
16. Defining Eields teeth icio leto e ot odes tacta icd 46 Delete Uere ee 106 Deleting Objects n EE ee d e it dut 34 Design eene 92 100 Design View Report Totals 52 un onseteiqetet utet 141 Desien Vie E 30 Designing KREE 36 Displaying Records In A Subdatasheet AAA 14 Edit Or Replace Data de reete ete oat 18 Editing And Finding Records eter donee 16 Exploring An Existing Database eee 10 Export 2007 Database To Access 2003 sess 161 Export A Report To Ms Word 160 Export A Table To Excel 1 4 ie etd petes 159 Exporting Data eani 158 Field Properties a dria uer Dope Mdb Dua ee t aes 52 Filter By Porti cee Bei i e OS 24 Filtering By Selection eege aao scd iet 23 Filters Applying and Removing see 22 Find Duplicates e TEEN 90 Find Unmatched Queries aeos Ie Perth Pita aestate dieu 91 177 INDEX Finding And Editing Records AA 16 Porti Foo ue die oT M qu 114 Form E ossi tes toto tuned ced oe e aSa 119 Formatting The Datasheet sac E diee 26 Forms Adding Controls ied steiere geev 120 Forms Blank Form Tool a tipi reat e Ze odas Yee edat 117 Forms Formi Tool a etti csoa totam Dirt n 114 Forms Form WIZAaEE E 119 Forms Split Form Tool poete petet eee 116 Forms Creation Tools sep adeo dre catu tuba lesa eves 113 Iusto PEN M 112 r Re CN 134 Import Data From An External Source 64 Importing Data From An Access Database esses 70 Importing From An Excel Spreadsheet
17. T0103 Gilles 42 97 T0104 Frank 9 95 T0105 Victors 46 40 T0106 Sweeny 73 90 T0107 Zehper 82 93 T0108 Hardy 27 47 T0109 Redding 16 47 T0110 Sweeny 29 98 115 FORMS Many Uses Form Views View Types A form is a database object that you can use to enter edit or display data from a table or a query You can use forms to control access to specific rows or fields of data Controls can be added to a form to automate frequently performed actions The form is a different view of your database forms can be designed for effective retrieval of data through automated searches a visually attractive form is easier and more enjoyable to use a form can also include verification controls to help prevent incorrect data from being entered Access allows database users to view forms in different ways This involves viewing a form during design runtime or a combination of both The views are available on the Format tab in the Views group on the ribbon Form Layout Tools Home Create External Data Database Tools Format Arrangd Calibri Jk 4 alibi 2 E Conditional Formatting Form View Layout View K Design View View Description Design A detailed view of the structure of your form It contains the Header Detail and Footer sections A wide variety of controls such as graphic images and labels can be added in Design view The form is not actually running in this view
18. entered Leyes mee Some columns may contain data that is not entirely visible You 2 a Save 37 Spelling can adjust the column width in one of two ways on the Home tab Refresh gt T a a X Bv in the Records group click the More Button or you can position the mouse pointer on the right column boundary line in the header row between columns Double click the left mouse button when the pointer changes to a crosshair Record At the bottom of the Datasheet window is the Record Navigation Navigation Bar for the records in the table Buttons Previous Si Record M 1of9 bob BELA Current Record The Navigation buttons move you to the first last next previous new or any specific numbered record in the table Change Column On the Home tab in the Records group click the More Button Width 2 Atthe drop down list click column width 3 Atthe Column Width dialog box change the width and click OK 12 EXERCISE MOVING AROUND IN DATASHEET VIEW 1 The Employees table is open rn Employee LastName First Name Title TitleC Birth Date Hire Date Addres EE 1 Davolio Nancy Sales Associate Ms 08 Dec 1968 01 May 1992 507 20th Avi E 2 Fuller Andrew Vice President Sales Dr 19 Feb 1952 14 Aug 1992 908 W Capite E 3 Leverling Janet Sales Associate Ms 30 Aug 1963 01 Apr 1992 722 Moss Bay E 4 Peacock Margaret Sales Associate Mrs 19 Sep 1958 03 May 1993 4110 Old Red
19. or you can enter the values yourself To launch the Lookup Wizard click the data type column of the field in Design view Use the data type pull down menu to choose the Lookup wizard Another C id H Table Tools Way to launch the Lookup gt Home Create External Data Database Tools Design wizard is to click the Lookup T Ze Insert Rows Ai EB EE UE Column button in the Tools seed Ben group of the Design tab in eee Design view Property Indexes Shee Tools Show Hide Note Upon returning to Design view from the Lookup Wizard you will notice that the data type for the Lookup field is Text 1 Open the table in Design View 2 Click the Data Type column of the field to be created Click the drop down menu Z and select Lookup Wizard The Lookup Wizard window launches Specify that the lookup column will accept the values from a table or query and click Next Access will prompt you for table and field names Click Finish OR Specify that you will type in the values and click Next Enter the values in the column provided and click Next Change the column name if desired Click Finish 63 l Open the Customers table in Design View Click the data type column for the CCity EXERCISE CREATING A LOOKUP FIELD field Click the drop down menu and select Lookup Wizard The Lookup Wizard will launch Field Name Data Type 9 ic Text This wizard creates a lookup column whic
20. reports are created to be printed Occasionally a report contains so much information that the data to be displayed exceeds the size of the print area If the data overflows the page margins blank pages may result You must define the page setup options before printing your report to reduce these problems Before printing it is good practice to preview your report Reports are displayed in Print Preview so you have previously viewed them in this format The Page Setup tab will be used to define page options before printing The Page Setup tab is automatically enabled in the Design Print Preview and Layout views Under this tab in the Page Layout group is a collection of tools that help you set the paper size report margins and choose page orientation style After you make a change use the Record Navigation buttons to view several pages to ensure that formatting problems were not created on later pages Page Setup Option Permits You to Size Choose the paper size Portrait Print the report along the length of the paper Landscape Print the report along the width of the paper Margins Set the margins Three options are available Normal Wide and Narrow Show Margins Display the margins in the report window Print Data Only Print the data without the column headers Columns Divide the page into two or more columns Page Setup Open the Page Setup dialog box it contains the above option
21. 1 Close all tables Gg id P TIE Home Create External Data Li vi XML File the Access button The Get External Data Access Database keen List vi More 2 On the External Data tab on the ribbon in the Import group click 4 sy y etre Imports wizard opens Import 3 In the File Name box specify the Access file that contains the data Use the Browse button to locate the Employee Data 2007 file in the Access 07 Class folder on your Desktop 4 To specify the data destination click the radio button to Import tables queries forms reports macros and modules into the current database Because we currently do not have a table created to accept this data Access will create the table Click OK Get External Data Access Database Tele Select the source and destination of the data Specify the source of the data Filename c Desktop Access 2007 Class Employee Data 2007 accdb Browse Specify how and where you want to store the data in the current database uH Import tables queries forms reports macros and modules into the current database If the specified object does not exist Access will create it If the specified object already exists Access will append a number to the name of the imported object Changes made to source objects including data in tables will not be reflected in the current database Link to the data source by creating a linked table Access will create a table that wil
22. 8 bytes AutoNumber Automatically generated by Access when a new Up to 16 bytes record is added Yes No Boolean true false data 1 Yes O No 1 bit OLE Object Used to embed or link objects created in other Office Up to 1 GB applications Hyperlink For storing web email addresses Allows single click 1 GB access Also link to objects in another database Attachment Store digital images or file attachments to a record 2 GB compressed Lookup Allows creation of multivalued fields Displays in a Up to 255 characters per Wizard drop down list value 49 EXERCISE DEFINING FIELDS FOR CUSTOMERS 1 Close Tablel 2 Name and define the fields for the Customers table as follows Customers Field Name St ca CFName CLName CAddress CCity CState CZip CPhone Data Type Description Arbitrary number given to the customer Masked as SSN First Name Last Name Field Properties General Lookup Field Size Format Input Mask Caption Default Value Validation Rule Validation Text Required No Allow Zero Length Yes Indexed Yes No Duplicates Unicode Compression No IME Mode No Control IME Sentence Mode None Smart Tags A pattern for all data to be entered in this field 3 The description column is optional You need only to make an entry in this area if the field is not obvious 4 Save Customers table Close Customers table 50 EXERCISE CREATE amp DEFINE FIEL
23. A dotted border outlines the column Position the mouse pointer on the right orange border until the pointer changes to a double arrow Click and drag the mouse pointer to reduce the length of the column Repeat for the other columns on the report Swee Click the title Add the word Report Center the report on the page Zehoer Delete the time Preview the report d d d d d d d d d d d d d d d d d d d d o o o o o o o o o O o o o O O o oO oO ES ES ES E E ES SE ES ES SS E SS S D Li 138 GROUP REPORTS Categorizing Information is often easier to understand when it is divided into Data groups For example a report that groups sales by city can highlight areas that may need more of a company s advertising dollars Adding totals for each group in the report will also help with data analysis A basic grouped report can be created by using the Report Wizard Groups A group is a collection of records plus any introductory and summary information displayed with the records A group includes a group header possible nested groups detail records and a group footer When you group a report based on a field Access creates a group header section for that record Group headers are matched with group footers When a calculation is performed for a field the calculation is performed for each of the groups and the result is displayed in the group footer Report Sections To create useful reports placement
24. Adding Criteria EE 93 Select Queries CalculatioHs ee eegener 98 Select Queries Conditional And Or sues 95 Select Queries In Design View ed rete treat date oce pe oo 92 Simple QUOPIeS ege 89 SOLID REC OLAS Goes ara iy ree pod dto atenta dresses uA eD RUE 20 Split Form Tool Log ie ieee Deg ete detis use ipee 116 EE 4 EE 14 Subforms Enhance The Appearance esses 126 SUDIOLITIS ooi oce tente eite Uae d tated tenet uade 124 Tables Getting Familiar With Datasheet View 12 Tables Taking Control With Design View esss 30 Tables EE 44 ET 167 The Access User Interface custos p eege geen 6 Totals In A Report In Design View 141 Update RT 101 Use Msword Mail Merge With Access Data 150 SOT MCL AC TURA NUNT REI NE 6 Validation ISule uc pee tae Bee tel breach Pe oa oet o cn fite 58 180
25. Apr 1992 722 Moss Bay Blvd 4 Peacock Margaret Sales Associate Mrs 19 Sep 1958 03 May 1993 4110 Old Redmond Rd 5 Buchanan Jeremy Sales Manager Mr 04 Mar 1955 17 Oct 1993 14 Garrett Hill Employees Order Details Orders Products 6 Suyama Michael Sales Associate Mr 02 Jul 1963 17 Oct 1993 Coventry House Shippers 7 King Robert Sales Associate Mr 29 May 1960 02 Jan 1994 Edgeham Hollow 8 Callahan Laura Inside Sales Coordinato Ms 09 Jan 1958 05 Mar 1994 4726 11th Ave N E 9 Dodsworth Anne Sales Associate Ms 02 Jul 1969 15 Nov 1994 7 Houndstooth Rd New Suppliers External Data Database Tools 7 employees Field Name Employees Order Details HireDate Shipp Address Suppliers General Lookup Ferd 3 Click the ES View button on the ribbon to switch back to Datasheet view 11 TABLES GETTING FAMILIAR WITH DATASHEET VIEW Moving Around The table operations in Access perform much the same way as the in Datasheet spreadsheets do in Excel Use your right arrow key or press Tab or View Enter to move between fields in the table To move back a field at a time press the left arrow key or press the Shift key and then press the Tab key Use the up and down arrow keys between records rows The active record is outlined on the datasheet and displayed in the Current Record text box of the Record Navigation Bar An asterisk appears at the row where the next record will be
26. Close any tables that are currently open Click No to saving any changes 2 Onthe Database Tools tab in the Show Hide group click the Relationships button Add Ins s2 EF Property Sheet Object Dependencies Database Tools Relationships essage Bar Show Hide 3 Click the Shutter bar on the Navigation pane to close it Each table is related to at least one other table in the database Data can be retrieved through all of these relationships The common field has the same name in both tables however this does not have to be the case i Relationships Suppliers Order Details Customers 9 SupplierID OrderlD V CustomerID CompanyName 9 ProductID CompanyName ContactName UnitPrice Orders ContactName ContactTitle PEU Quantity 9Y Order ContactTitle Address 3 Discount CustomerID Address City V ProductID EmployeelD City Region ProductName OrderDate Region PostalCode SupplierID RequiredDate PostalCode Country Category ShippedDate Country Phone QuantityPerUnit ENDE ShipVia Phone de EAE V EmployeelD nein 8 HomePage LastName ShipName UnitsOnOrder ReorderLevel FirstName ipia Discontinued Title acy unm e TitleOfCourtesy ShipRegion HER Categories BirthDate ShipPostalCode 9 ShipperlD 9 CategoryID HireDate ShipCountry CompanyName Sarg Address Fhone escription City Picture Region PostalCode Country HomePhone Extension Photo Notes ReportsTo All
27. EZ or S on a transaction unless that customer employee or shipper already exists in our database We also cannot create orphans by deleting a customer and not deleting all related transactions for that customer For Access to automatically update and delete related records implement the Cascade options 1 Open the relationship window On the Database Tools tab click the Relationships button in the Show Hide group 2 To edit the Transactions Transaction Details relationship right click the relationship line and select Edit Relationship or double click the relationship line The Edit Relationships window opens 3 Check the Cascade Update Related Fields and Cascade Delete Related Fields boxes 4 Click OK Edit Relationships Table Query Related Table Query Transactions m Transaction Details T rs Join Type s Create New V Enforce Referential Integrity See v Cascade Update Related Fields V Cascade Delete Related Records J Relationship Type One To Many 88 EXERCISE CASCADE DELETE RELATED FIELDS OPTION 5 Open the Transactions table The Transactions table primary table is related to the Transaction Details table 6 Click the expand indicator for transaction T0111 The Transaction Details subdatasheet displays two related records for this transaction We will delete this transaction from the database T0111 627458965 11 Feb 08 S30 52806789
28. Ps Many to Many Relationship 129 EXERCISE SUBFORMS FORM WIZARD Books4U requires a form to display a list of books that each employee has sold The Employee s ID last name and title should be included Book information includes ISBN book title purchase price and purchase date 1 Click the Create tab In the Forms group click More Forms and then click Form Wizard On the first page of the wizard identify the tables and fields required to build this form In the Tables Queries drop down list select the Employees table Double click E ELName and Title On the same page of the wizard in the Tables Queries drop down list select the Books table Double click the ISBN Title and Price fields Click Next On the second page the wizard asks how you want to view your data We want to view each employee s list of books Select by Employees The wizard displays a small diagram of the form layout The box in the lower portion represents the subform At the bottom of the page select the Form with subform s option Click Next The third page of the wizard asks you to choose a layout for the subform Tabular or Datasheet Both layout styles arrange the subform data in rows and columns Although the tabular subform allows you to add color graphics and other formatting elements choose the Datasheet layout Click Next Choose the style for the form next If the form has a Tabular layout the for
29. Toggle to Design View You may notice that when converting an Access 2000 2003 database to an Access 2007 database your tabs may not show In previous versions of Access each table was open in separate overlapping ces windows To activate the tab ro mW feature for displaying tables click the Office button nad Besos emm Click the Access Options button at SSC the bottom of the window When Ga the Access Options dialog box SE opens click the Current Database button on the left side of the abase r tables in Datasheet view for this database window In the Application m ieu Options group under Document KR TN Window Options click the Tabbed TID MEG MACH SS Tu CUN RR Documents radio button and check ILLI the Display Document Tabs box Click OK Ribbon Name Iz NET mnm Close the Shippers table 76 CREATING RELATIONSHIPS Related Data Linking Tables Referential Integrity Types of Relationships Our Books4U database consists of several tables of related data It is essential to separate the data into multiple tables to reduce unnecessary data redundancy and to decrease possible errors when adding to or modifying the database The next step in the process is to establish table relationships that will ensure the accuracy of the data when generating reports or requesting information Data may be retrieved from several tables to generate a single request Link
30. and perform as foreign keys to relate to the two tables Date and other fields as necessary is an added field for more information about the purchase itself The foreign keys are the only necessary data redundancy as a result of this modified design 623179543 938716271 2 3 2008 657895123 938716271 2 5 2008 657895123 1558747524 2 9 2008 Looking closer at the tables in our Books4U database we see that the Transactions table was created as the junction table to relate Customers and Books The Transaction Details table reduces the data redundancy that would have been created if that information were left in the Transactions table We now have several one to many relationships replacing our many to many relationship Publishers The relationship between Transactions and Transaction Details was created in the previous exercise We will now create the relationship between Customers and Books using Transactions and Transaction Details as the junction tables 82 EXERCISE CREATING MANY TO MANY RELATIONSHIPS We will follow the same procedure as in the previous exercise in creating one to many relationships The Transactions table is already in the relationship layout area and will be used as the junction table Transaction Details will also be used to create the relationship between Customers and Books 1 The relationship layout area is open On the Design tab in the Relationships group click the S
31. data Create mailing labels Integrate programs export to Word Excel and Access 2003 Clone the database DATABASE CONCEPTS Access 2007 Database Definition Relational Databases Basic Elements of a Database Microsoft Access 2007 is a Database Management System that assists in the creation of a relational database to satisfy the diverse data tracking needs of an organization Access 2007 provides the user with various features for creating and editing data organized into tables forms and reports It also allows the import and export of data to and from various applications A database is a collection of logically related data that can be organized and accessed quickly The data can be stored as text numbers or graphics and can be searched retrieved and manipulated If you have large amounts of data data that is updated frequently have many people accessing the data and want to retrieve data in different formats a database will work best The database not only contains data but also a definition of the data which serves as the documentation for your database structure You won t need to go looking for the database manual to learn how your database was created That information is contained within the database itself This is one of the greatest advantages of database management systems Relational databases store data in a collection of related tables Each table contains one specific type of information This
32. file Books4U New Click OK 5 Click Create A new blank database is opened We have a new blank database We will import just the database structure from our original database 6 Onthe External Data tab in the Import group click Access On the Get External Data dialog box click the browse button Navigate to the Access 07 Class folder Double click Books4U 7 Click the Import radio button Click OK 8 The Import Objects dialog box opens Click the Tables tab Click Select All Repeat for the Queries Forms and Reports tabs 9 Click the Options button 10 Check to import relationships We do not want to import the data so click the Definition Only radio button We also want to import all queries as queries Click OK Import Import Tables Import Queries V Relationships Definition and Data As Queries F Menus and Toolbars Q9 Definition Only As Tables Import Export Specs F Nav Pane Groups 167 EXERCISE CLONE THE DATABASE 11 Close the Get External Data window 12 Close Tablel 13 In the Navigation pane use the pull down to click Object Types and All Access Objects displaying all objects in the database 14 Open the Books table in Design view The structures have been transferred However the Datasheet view displays no records Your tables are now ready to insert the new data 15 Examine the Forms and Reports Modify the relationship diagram 16 Close the Books4U New database
33. generates a report immediately without prompting you for information Books4U requires a simple report showing the total price for each transaction detail You have previously created a query to capture all required data l 8 9 10 Save the report Close the report In the Navigation pane click the Qty Price query to select it Gro E as the record source for your report i sBlankRepott Report ER L Report 2 report Wizar a On the Create tab in the Reports group click Report Access builds the report and displays it in Layout view Reports Preview the report Click Print Preview on the Quick Access Tool bar or click the Office button roll the mouse over the print option and click print preview On the Print Preview tab in the Zoom group click Zoom Zoom to 75 The report data spills onto another page Use the page navigation bar at the bottom of the screen Access used the name of the query to title the report Date time and page numbers are also provided as well as a calculated sum of the Total Price field We will modify the report by changing the title and reducing the spacing between the fields Close print preview The report is now in Layout view Save and name the report Oly Price Repor main Qty Price Report a D o a 1 m o o Ki t ei o Ei al e a D Resize the columns of the report to better accommodate the data Click one of the T text box controls
34. hold down the left mouse button drag the pointer to P in the Publishers table list box and release the mouse button Click the Enforce Referential Integrity box Click Create Move the table list boxes in the relationship window so that the relationship links are visible and not crossing over each other See diagram next page same as handout 84 EXERCISE CREATING MANY TO MANY RELATIONSHIPS Customers y ce CFName CLName CAddress CCity CState CZip CPhone r Transactions 9 Te Employees y ge EFName ELName EMiddle BDate HDate Title Emails V employeeid email Transaction Details T ISBN Qty Price Books V ISBN Title Author QtyOH Shippers Price V ss ps SName SContact SPhone Publishers V Ge PName 18 When you have the tables positioned so that all relationships are visible and no lines are crossed Save the relationship diagram 19 To delete any relationship right click the relationship line and select Delete or left click the relationship line and press the Delete key You will get a warning message from Access to confirm the delete action 20 To edit any relationship right click the relationship line and select Edit Relationship or double click the relationship line The Edit Relationships window opens 2 mO 22 To print your report the relationship window must be open On the Database Tools tab c
35. in the worksheet Export Excel Spreadsheet SIS Select the destination for the data you want to export Specify the destination file name and format Filename c Users moserk02 Desktop Books xlsx Browse File format Excel Workbook xisx EI Specify export options V Export data with formatting and layout Select this option to preserve most formatting and layout information when exporting a table query form or report V Open the destination file after the export operation is complete Select this option to view the results of the export operation This option is available only when you export formatted data Export only the selected records OK J Cancel 7 Save the Excel file Close the file Do not save the export steps Click Close 164 EXERCISE EXPORT A REPORT TO MS WORD Lori Jones Books4U bookkeeper needs the Sales Report in MS Word format 1 In the Navigation pane click on the Books4U Customer Sales Report to select it 2 Onthe External Data tab in the Export group click Word The Report RTF File dialog box opens 3 Click the Browse button 4 Atthe File Save dialog box navigate to the Access 07 Class folder on the desktop and click Save The export wizard automatically inserts the name of the report in the File name text box 5 Atthe Report RTF File dialog box click the Open the destination file after the export operation is complete check box Click OK MS
36. manager has provided a rough sketch of all data elements involved Each new customer will be printed on a new page Books4U Customer Sales Report Customer Transaction Oty Price Total Price Transaction Total Transaction Transaction Total Customer Total Final Report Total For each customer the report contains customer name and sales transaction number A sequence of repeating items purchased on the transaction is next The transaction purchase is totaled and if the customer has purchased again the transaction data and purchase data is displayed for a second time All purchases for the customer are then totaled The process is repeated for the next customer The report ends with total sales of purchases by all customers The report contains a date title and page number that are all repeated on each page 140 EXERCISE CREATE A GROUP REPORT REPORT WIZARD After you examine the sketch determine which table or tables contain the data that you want to display on the report If all the data is contained in a single table the report can be based on that table However in this example the data required is contained in three tables Customers Transactions and Transaction Details In this case it is better to combine the tables together in a query and then use the query as the record source We will use the
37. new filter is applied There are only three records displayed Orders Customer d Employee Order Dai Required Dat Shipped Dat Ship Via x Fr Chop suey Chinese King Robert 36 Nov 1997 04 Dec 1997 14 Nov 1997 Speedy Express v Chop suey Chinese Sawyer Margaret 20 Mar 1998 17 Apr 1998 08 Apr 1998 Speedy Express Chop suey Chinese Sawyer Margaret 16 Apr 1998 14 May 1998 27 Apr 1998 Speedy Express 7 To cancel the above filters and restore the table to its original view click the Toggle Filter button The Record Navigation Bar now indicates that the records are unfiltered o N lof830 gt Ck Unfiltered Search s 24 FILTER BY FORM Complex Filters This feature is useful for filtering several fields or for locating a specific record Access creates a blank datasheet with the same structure as your original datasheet and allows you to specify search criteria for one or more fields BI LS Y Selection For Example if one of your customers is having problems with a SE particular shipper display all shipments from this shipper to this 7 customer The first step is to open the Customers table and click the Advanced button in the Sort and Filter group on the ribbon Select Filter by Form from the drop down menu list Sort amp Filter You cannot specify field values for multivalued fields using this filter Fields with Memo Hyperlink Yes No or OLE object data types
38. of controls in a header detail or footer section determines how Access calculates results The following describes each section type Section Description Report Header Printed once at the beginning of the report Usually includes logo title and date This header prints before the page header When a calculated control is placed here the value is calculated for the entire report Page Header Printed at the top of every page Group Header Printed at the beginning of each new group of records Usually the group name is placed here When a calculated control is placed here the value is calculated for the current group Detail Printed once for every row in the record source Controls that make up the main body of the report are placed here Group Footer Printed at the end of each group of records Use controls to print summary information for the group here Page Footer Printed at the end of every page Print page numbers here Report Footer Printed once at the end of the report Print report totals or other summary information for the entire report 139 EXERCISE CREATE A GROUP REPORT REPORT WIZARD The Report Wizard allows you to create a more complex report that includes several layers of data in several sections You can also add aggregate totals to different sections of the report Books4U requires a printed report of all customer purchases Ana Rivera
39. orphan transaction records transactions for a non existent customer Customers y ce CFName CLName CAddress CCity CState CZip CPhone Two additional options are dimmed until you turn on referential integrity Cascade Update Related Fields and Cascade Delete Related Fields Checking these options will instruct Access to change a record in a related table if you make a change in the primary table You may encounter a situation where there is a valid need to change one side of a relationship To keep the data consistent Access will automatically update the affected rows in the related table as part of a single operation When you check the Cascade Update Related Fields option Access ensures that the update is completed in full so that the database is not left in an inconsistent state You might need to delete a row and all related records For example if we deleted a customer from the Customers table all related transactions will be automatically deleted from the Transactions table and all details of the transaction from the Transaction Details table to make certain that no orphan records remain Use the Cascade Delete Related Fields for this task 87 EXERCISE SET CASCADE OPTIONS In creating our relationships for Books4U we set the Enforce Referential Integrity option Access ensures that when entering data into a related table the reference data exists in the primary table We cannot enter a CZ
40. quickly as you work with them and can sometimes become fragmented because records and objects have been deleted or damaged This in turn can impede performance as the disk space grows larger than necessary Compacting the database defragments the file and reduces the required disk space Compacting and repairing a database also ensures optimal performance while using the files You can set a database option that causes the Compact and Repair feature to run automatically each time you close the database if you are a single user of the database Before you begin always make a backup Access includes a backup utility to facilitate this process 1 Make a backup Click the Microsoft Office Button point to Manage and then click Back Up Database 2 In the Save As dialog box that opens notice the file name that Access supplies the original name of the file and the date of the backup Click Save 3 With the database open click the Microsoft Office Button point to Manage and then click Compact and Repair Database You can select the Compact on Close database option if you want to automatically compact and repair a database when it closes 4 Click the Microsoft Office Button and then click Access Options at the bottom of the window Access Options 5 In the Access Options St Options for the current database dialog box click Current Current Database Database Datasheet Application Options e Object Designe
41. relationships in the diagram are one to many the infinity symbol o shows the many side of the relationship To read the relationship between two tables Customers and Orders for example A Customer can have many Orders A specific Order is for only one Customer 34 DELETING OBJECTS IN ACCESS Exercise In a multiuser environment deleting any object from the database Caution should be performed only by those authorized to do so in most cases once an object has been deleted the result is permanent Even the Undo button cannot help you recover data that has been deleted Access is a relational database Data in the deleted table could be linked to other tables thus creating orphans For example a Customers table has related records in an Orders table If a customer is deleted all of those customer s orders become orphans All data about the customer for those orders is lost and the integrity of the data is compromised Warning When you try to delete an object in Access you will receive a Message warning message reminding you of the consequences if the action is taken Every object has a different set of consequences Be sure to thoroughly read each warning message In most cases you will not be able to recover the data Make a Copy Before deleting an object from the database even if you have removed all relationships for that object it s a good idea to make a copy To copy a table select the table from the Naviga
42. saved for future access Steps to Import data from an External Source On the External Data tab in the Import group select the file format of the source file The Get External Data dialog box will open Specify the location of the source data Select the option for importing appending or linking to the source file Click OK The Wizard will guide you to complete the import procedure 67 EXERCISE IMPORTING FROM AN EXCEL SPREADSHEET In this exercise we will import data from several Excel spreadsheets into our Books4U database using the Append option You can import data from one worksheet at a time Most failures during append operations occur because the source data does not match the structure and field settings of the destination table Before importing data from a spreadsheet check to make sure that the column headers in Excel match exactly the field names in the database Also check the cell formats of the spreadsheet data to ensure that they match with the data type definitions in the table All Access Objects 1 Close all tables went Category 2 Check that you have three tables Customers Transactions and Transaction Details If you do not see them click the pull down menu on the Navigation TAREA pane and select Object Type EEES Queries 3 On the External Data tab on the ribbon in the Import group click the Excel forms button The Get External Data Excel Spreadsheet wizard opens que
43. so you cannot see the underlying data while you are making design changes Form The form is running in this view so you can see the underlying data on which the form is based You can use this view to add records edit records or navigate through a table Changes to the design of the form cannot be implemented in this view Layout The form is running in this view so you can see the underlying data on which the form is based You can also make changes to the form design in this view Because you can see the data while you are modifying the form this is a useful view for setting the size of controls or performing almost any other task that affects the appearance and usability of the form 116 FORMS CREATION TOOLS Form Access 2007 offers several form creation tools that can be used Creation Tools interchangeably depending on the needs of the user the Form Tool creates a new form with one click a Split Form shows two synchronized views for a form with one half displaying the form using Form view while the other half displays data in Datasheet view the Form Wizard guides the user through a series of dialog boxes to generate the form offering more choices for the form layout In order to use the form creation tools you must first select the table object in the Ee Hep E Navigation pane upon which to base the new rom form tems More Forms pesign form The form creation tools are loca
44. specified records by arranging data in a specific order Sorting can be performed on single or multiple fields Click on any record in the field you wish to sort The sort buttons are located in the Sort amp Filter group on the Home tab in the EN ribbon When you click on the Sort Ascending button the records d in the field are sorted in alphabetical order A Z When you click on the Sort Descending button the records in the field are sorted in reverse alphabetical order Z A A 7 ue Selection Y Advanced Filter Sort amp Filter You can also do a sort using more than one field Columns may be rearranged to perform this sort Select the columns to be sorted and then click the Ascending or Descending button Access sorts first by the leftmost column in the selection then by the next column and continues this pattern for the remainder of the sort keys Access saves the sort order when the table is closed To restore the records to the original order click the Clear All Sorts button in the Sort amp Filter group This step must be done before you save the design changes Numbers stored in fields that are not defined as numeric are sorted as character text data All field values must be the same length to sort character type numbers as numbers Ifa selected field is empty the record that contains it is listed first Numbers are sorted before letters 20 EXERCISE SORTING A TABLE 10 Open the P
45. tab 99 Ech r SOR Print Close the report Printer LS s Name PRINTSRY 1 Maricopa edu Printer0060 D Properties Status Ready Type HP Color LaserJet 4650 PCL 6 Where DO 312B Tag 112055 Comment DO 3128 Tag 112055 Print Range Copies 9 All Number of Copies Pages From d AR ap Print to File Setup Lamar l Cancel 154 USE MSWORD MAIL MERGE WITH ACCESS DATA Mail Merge Data Source Mail Merge Wizard Steps to Using the Mail Merge Wizard The Microsoft Word Mail Merge Wizard enables you to link data in an Access table or query to a Microsoft Word document creating letters e mail messages mailing labels or envelopes Using the Wizard you can either link to an existing document or create a new document and then link the data to it To use the Microsoft Word Mail Merge Wizard in Access first select the table or query that you want to merge into your letter by clicking it in the Navigation pane Then from the ribbon on the External Data tab in the Export group click More and then select Merge it with Microsoft Office Word The Mail Merge Wizard opens and offers you a choice to link to an existing document or create a new document and then link the data to it The fields from the selected table or query are available from the Insert Merge Field button on the ribbon when the document opens For step by step instructions on how to set up a Word ma
46. table 4 Open the Transactions table in Design View Place the cursor in the data type column of the TDate field 5 Click the Format property box Click the pull down on the far right of the box Format Medium Date BE 4 Input Mask General Date 6 19 2007 5 34 23 PM Caption Long Date Tuesday June 19 2007 Default Value Medium Date 19 Jun 07 Validation Rule Short Date 6 19 2007 bs Validation Text Long Time 5 34 23 PM Required Medium Time SEM PM Indexed Short Time 17 34 IME Mode No Control IME Sentence Mode None Smart Tags Text Align General Show Date Picker For dates Access 2007 provides a set of predefined date and time formats 6 Click the Medium Date format 7 Save the Transactions table 56 INPUT MASKS Consistency of Data Create Your Own Steps to Add Input Mask Properties An input mask establishes a pattern in the Datasheet view which indicates how data is to be entered into the field Input masks ensure that data is entered consistently into the tables In addition to specifying the position and amount of characters in a field you can customize masks that restrict the data entered and whether or not the data is optional The Input Mask Wizard contains a list of predefined masks provided by Access 2007 You can also customize your own input mask using the Customize Input Mask Wizard The Edit List button in the Input Mask Wizard allows you to change any of the exist
47. table structure ensures that data is stored only once greatly reducing the amount of duplicate data contained within the database The limited data duplication that occurs is through the primary and foreign key fields needed to relate the tables This relational structure helps to preserve the integrity of the data when changes to the data occur Element Description Table A group of records for one specific type of information Record A group of fields Field A single component that describes the record Value An allowable entry for a field DATABASE CONCEPTS Excel Table E Ka Si B H MS Sans Serif 10 IA Al E General i aie Meet Il E Zei 3 Delete g Paste BZIU E Ai T a sa 90 Conditional Format Cell e Sort amp Find amp F d Ji m doct A Formatting as Table Styles Format 27 Filter Select Clipboard Ta Font EI Alignment bi Number mell LAE Styles IL celis Editing 115 A B a NA E i G H J K 1 dateregister firstname lastname colle eposit member workshopid orksho roomname starttime speakei 2 07 Dec 06 Patricia Yee PVC 150 00 Yes Y040 Computers in the Classroom Yucca Room 1 30 PM Dr Joan Pullman 3 05 Dec 07 Lori Jones SCC 150 00 Yes Y040 Computers in the Classroom Yucca Room 1 30 PM Dr Joan Pullman 4 07 Dec 06 Ana Rivera RIO 100 00 No S030 Making Learning F
48. tables have ISBN in common Position the mouse pointer over SBN in the Books table list box hold down the left mouse button drag the pointer to SBN in the Transaction Details table list box and release the mouse button The Edit Relationships dialog box appears Click the Enforce Referential Integrity box Click Create Create the relationship between Employees and Transactions using E as the common linking field Position the mouse pointer over EZ in the Employees table list box hold down the left mouse button drag the pointer to E in the Transactions table list box and release the mouse button Click the Enforce Referential Integrity box Click Create Add Shippers and Publishers to the relationship window Click the Show Table button in the Relationships group On the Tables tab select Publishers shift Shippers to select both tables and click the Add button Click Close Move the Shippers table list box below and to the right of the Transactions table list box These two tables have SZ in common Position the mouse pointer over S in the Shippers table list box hold down the left mouse button drag the pointer to S7 in the Transactions table list box and release the mouse button Click the Enforce Referential Integrity box Click Create Move the Publishers table list box below and to the right of the Books table list box These two tables have P in common Position the mouse pointer over P in the Books table list box
49. than typing the date manually for each transaction setting the default value to Today s date will automatically generate that value in the TDate field 4 Open the Transactions table in Design view 5 Place the cursor in the data type column of the TDate field 6 Use the Expression Builder to set the default Click the Default Value box in the P ressien Builder Field Properties pane Click on the Build Det button Lz that appears at the end of the box The Expression Builder window opens gt lt lt gt And Or Not Like j Paste Help Functions lt All gt a A If you do not know the expression to set o2 GE a e Constants e Ze today s date default use the buttons and operators on cs d columns to help Date is a function so start Tent by double clicking Functions and then DateSerial DateValue Built In Functions The second column Day will display the Type options 7 Select Date Time as the type The set of expressions available are displayed in the third column Double click the Date expression The expression Date is displayed in the Expression Builder window Click OK 8 The expression now appears in the Default Value of the field properties 9 Save the Transactions table 10 Toggle to Datasheet view to see the automatic entry for the TDate field Transactions Y T x i M TDate H 24 Jan 08 60 VALIDATION RULE Conditional Test Validation Text The Validation
50. to MS Word When you export a report the wizard exports the report data and layout It tries to make the Word document resemble the report a closely as possible Macros and Modules cannot be exported Steps to Exporting Data Click the desired object for export in the Navigation pane Onthe External Data tab in the Export group click the Excel or Word button The Excel Spreadsheet dialog box or the RTF File dialog box opens Click the Browse button Atthe File Save dialog box navigate to desired folder then click Save Click the desired options Click OK 163 EXERCISE EXPORT A TABLE TO EXCEL Ana Rivera manager of Books4U wants to look at the book information over the weekend but she does not have Access installed on her laptop She wants to export the book information to Excel 1 In the Navigation pane click on the Books table to select it 2 Onthe External Data tab in the Export group click Excel The Excel Spreadsheet dialog box opens 3 Click the Browse button 4 Atthe File Save dialog box navigate to the Access 07 Class folder on the desktop and click Save The export wizard automatically inserts the name of the table in the File name text box 5 Atthe Excel Spreadsheet dialog box click the Export data with formatting and layout check box 6 Click the Open the destination file after the export operation is complete check box Click OK Excel opens with the book data
51. 0 Singer 055327757X 1 7 99 7 99 T0100 Singer 446530697 1 13 99 13 99 T0102 Zehper 1558747524 2 14 95 29 90 T0103 Gilles 766825361 1 17 98 17 98 T0103 Gilles 785288376 1 24 99 24 99 Se p PERS Save the query by clicking the close button on the query window Name the query Qty Price 102 103 ACTION QUERIES IN DESIGN VIEW Change Data or Action queries make changes to data or the data structure Four Data Structure types of Action queries are available in Access update append make table and delete Disabled Mode By default Access disables all action queries unless your database resides in a trusted location If you try to run an action query and it seems like nothing happens check the Access status bar at the bottom of the screen for the following message This action or event has been blocked by Disabled Mode On the Message Bar click the Options button The Microsoft Office Security Options dialog box appears Click Enable this Content and then click OK see page 7 Run the query again e Security Warning Certain content in the database has been disabled Options Run to Update To run an Action query first change the query type by clicking the appropriate button on the Query Tools Design tab Query Type group In the Results group click the Run button Clicking the Run button will update the values in the database Once you create and run an update query you cannot undo the changes therefore use wi
52. 168 NOTES 169 Appendices Additional Features in Access 2007 170 APPENDIX A ON LINE HELP Accessing Help Online Help As you work with Access 2007 and find that you want to develop more complex designs using tools and options that are unfamiliar you might need some assistance in discovering what they can do for you The Access 2007 Help feature is a complete user manual with both offline and online links to direct you An extensive online default Help resource contains information on all Access features and commands To open the Access Help window click the Help button located in the upper right corner of the screen just below the Title bar The main Access Help window contains links to categories of help topics Either click a category link or type a search word or phrase in the textbox and click Search 9 Access Help OOM Ag Ty ype a word or phrase here JD Search Offline Help Activating Access Data collection What s new Getting started Glossary Accessibility Getting help Access projects Data pages Attachments Macros and programmability Application deployment eveloper Extensions Customizing Database design Expressions External data Filtering and sorting Forms and reports Queries Saving and printing Security and privacy Tables Work with SharePoint sites Adding charts diagrams or tables File and data management Working in a differ
53. 4 ISBN 938716298 938716271 7 Click the collapse indicator for transaction T0111 8 To select the record click in the gray column to the left of the expand indicator for transaction TO111 9 In the ribbon on the Home tab in the Records group click the Delete button Access displays a warning message Click Yes Microsoft Office Access 53 Relationships that specify cascading deletes are about to cause 1 record s in this table and in i related tables to be deleted Are you sure you want to delete these records l Yes No l Help 10 Open the Transaction Details table The result of deleting transaction T0111 from the Transactions table cascaded down to also delete any related records in the Transaction Details table The Cascade Delete Related Fields option ensures that no T0110 1559276126 1 18 00 T0110 1599869802 1 4 99 T0110 451205367 1 6 99 Deleted Deleted Ra amp Deleted Deleted Deleted Deleted orphans exist 11 Closing and re opening the Transaction Details table will erase any records flagged for deletion 89 NOTES 90 QUERIES Retrieve Data Current Every Time New Query Query Wizard Options A query is an Access object designed to retrieve data from one or more tables or queries A query is much like a filter but unlike a filter a query can extract data from multiple sources The query can be created for various purposes from a simple s
54. AZ 85466 602 355 124 668 75 4123 Tim Green 266 S Lilac Mesa AZ 85699 602 33 H 675 84 7965 Harry Rundle 245 N Crabappl Scottsdale AZ 85385 480 144 775 Datasheet View 677 85 1234 Kyle Ogden 744E Willow Scottsdale AZ 85385 602 2574519 677 85 2741 Pauline Jeffs 96 W Butternut Chandler AZ 85478 602 333 58 685 23 6547 Fawn Todd 602 S Maple Phoenix AZ 85264 480 255 44 685 26 4913 Mel Singer 297W Poplar Chandler AZ 85478 480 211 33 T S ga Seance n 12 Sanuioa Senttcdala A7 25295 laani 755 ear m d Search 4 li J 2 92 1792 He Record I4 4 26 of 31 Access creates the form and displays it in Layout view Recall that in Layout view you can make design changes to the Form while it is displaying data With a split form you can also make data changes to the Datasheet that will reflect back to the Form Allow Edits must be set in the Split Form property sheet press F4 3 Click the Layout View button on the Status bar in the lower right corner of the screen 4 Locate and select the record for Isabel Quail in the Datasheet The record is displayed in the form above 5 Change Isabel s first name to Connie Press the Enter key The changes sync to the form above 6 On the form click the C field Left click the right orange border of the textbox for C and while holding down the mouse button drag the mouse to the left to shorten the textbox Repeat the steps for the CCity fiel
55. Box Launcher Message Bar The Access Interface consists of various tools Located in the top left corner of the office window frame this button displays a set of commands for creating a new database opening saving printing managing emailing publishing and closing a database Access Options assists you in customizing the Access environment A list of the most recently viewed documents allows quick access to databases you are currently working on The Quick Access Toolbar can be customized to hold your most frequently used commands It remains visible at all times reducing the need to search for the proper command tab By default it resides above the ribbon but can be moved below the ribbon Contains task specific commands grouped together under command tabs This new feature of Access 07 is the replacement for the former menus and toolbars Displays database objects such as tables forms queries and reports Can be customized to display objects by various groupings Located at the bottom of the user interface the frame can be customized to show additional features such as database views Caps Lock Scroll Lock or Num Lock The Window Frame was previously known as the status bar Miniature buttons found on command tabs provides additional commands and tools associated with the group Security features cause the message bar to appear below the ribbon when you open a database outside of a trusted location Enable the f
56. DS FOR TRANSACTIONS 1 Create the Transactions table in Design View On the Create tab of the ribbon in the Tables group click the Table Design button Refer to the Transactions table design on page 41 for the required fields 2 Our design for the Transactions table shows a field called Total As discussed earlier calculated fields are usually not stored in the database Access 2007 will calculate the total and present it in either form or report format when needed Therefore we will not create a field for Total in the Transactions table ES Transactions Field Name Data Type Description T amp Text Transaction Number Ca Text Customer Number of Customer Ordering the books Date Time Date of the transaction Ss Text Shipper who will ship the order to the customer E amp Text Employee who sold the books to the customer 3 Save the table Name it Transactions Click OK 4 Access alerts you to define a primary key Click No Microsoft Office Access mx There is no primary key defined i Although a primary key isn t required it s highly recommended A table must have a primary key for you to define a relationship between this table and other tables in the database Do you want to create a primary key now Cancel 5 Close the Transactions table 51 EXERCISE CREATE amp DEFINE TRANSACTION DETAILS 1 Create the Transaction Details table in Design View On the Create tab of the ribbon in
57. Employee table Address column right click the pull down menu and select Hide Columns Also hide Employee ID Title of Courtesy City Region Postal Code Photo and Notes columns Address 507 20th Ave ELAS 908 W Capital Wa 722 Moss Bay Blvd 5a Copy 4110 Old Redmond Paste Sort Ato Z SortZtoA 14 Garrett Hill Column Width Coventry House Edgeham Hollow 4726 11th Ave N 7 Houndstooth Rd Hide Columns Unhide Columns Freeze Columns Unfreeze All Columns Find Insert Column Lookup Column Delete Column Rename Column To select multiple columns at the same time right click any column pull down and select Unhide columns A list of columns in the datasheet is shown along with a checkbox Uncheck those columns that you wish to temporarily hide from view Click Close Column Preview the datasheet The datasheet now fits on one page ae Click the Print button to print the datasheet V First Name V Title Title Of Courtesy If more formatting needs to be done close the preview to return v sith pate to the datasheet view You can change the font size and type Jose using the options in the Font group on the Home tab V Region Postal Code To return the datasheet to its original layout format right click Psi E on any column header pull down menu and select Unhide m Columns Check all boxes 29 NOTES TABLES TAKING CONTROL WITH DESIGN VIEW Table Struc
58. G ONE TO ONE RELATIONSHIPS In a one to one relationship for each record in the primary table there will be one and only one record in the related table The common data field will be the primary key in both tables One to one relationships are rarely included in the database design Depending on the amount of data it may be more efficient to combine the tables In our Books4U database the employee s email address was overlooked during the design phase We will combine the data later but before we do that we must establish a relationship between the two tables The layout on your handout will guide you 1 Close all tables 2 On the Database Tools tab in the Show Hide group click the Relationships button Data Database Tools Design Close Database Tools Sr Property Sheet Object Dependencies Table 22 All Relationships Relationships The Design tab is activated In the Relationships group click the Show Table button 3 The Show Table window opens On the Tables tab select Employees shift Emails to select both tables and click the Add button Click Close 4 Both tables are displayed in the Relationship layout area Click and drag the edges of the tables to expand them if necessary to view all field names S J Table Query Related Table Query uem Edit Relationships 5 The common field in both tables is Employee Number Note that the field name does not have to match in bot
59. ION QUERIES DELETE A Delete query allows you to delete records from database tables Use with caution as you cannot undo the changes Make a copy of the table s first and practice your delete query on the copied table before running the query on the original table Delete all Scottsdale customers from the database 1 Close all database objects 2 Make a copy of the Customers table Single click on the Customers table in the Navigation Pane to select it Click the Office button and select Save As from the drop down menu Click Save Object As 3 Inthe Save As dialog box the Save Customers To textbox defaults to the name Copy of Customers Click OK 4 The Copy of Customers table opens in Datasheet view Click on the CCity column header The column is selected 5 Onthe Home tab Sort amp Filter group click the Ascending key to sort the city names in alphabetical order Examine the data There are 7 records displaying Scottsdale 6 Close the table Click Yes to save the design changes 7 Click the Create tab In the Others group click the Query Design button The Show Table dialog box opens 8 Select the Copy of Customers table Click Add Click Close 9 Weare deleting all customer records that contain Scottsdale in the CCity column Double click the CCity field from the Copy of Customers field list 10 On the query grid in the Criteria row of the CCity column type Scottsdale This is the specific data we are se
60. L Document Export selected object to an HTML document 3 Select to create the link to an existing document Click OK rr ete eg ies File Export selected object to a Paradox mA Si Pa dB Em D I o Pa SE FH Ke Eg Lotus 1 2 3 File Export selected object to a Lotus o 1 23 file ie eg be data to a ee ord Merge it with Microsoft Office Word ocument so that you can print form letters or Use Word Mail Merge wizard to address envelopes merge data Cancel What do you want the wizard to do a Link your data to an existing Microsoft Word document 7 Create a new document and then link the data to it 4 In the Select Microsoft Word Document dialog box locate and select the Sales Promotion Letter file from the Access 07 Class folder Click Open The document opens in MS Word The Mail Merge pane is open on Step 3 of 6 In step 3 we are creating the link between the data source in Access and the Word document This link is automatically created because we started the wizard from Access Under Select recipients Use an existing list is selected and the name of your data source is displayed under Use an existing list Use an existing list Currently your recipients are m Edit recipient list Click Edit recipient list if you want to customize the contents of the table or query You can filter sort and validate the data Click OK to continue 5 Click Next Write your letter at the botto
61. LE Creating a New Access 2007 will open a new table under the Datasheet tab called Table Tablel The Navigation Pane shows the category All Tables with Table1 listed Although a new table can be created by simply adding records to the blank datasheet creating a table in Design View allows you to set data types and design properties Design view gives you total control in defining table structures When Access 2007 opens the new table in Datasheet view the cursor is positioned in the first empty cell in the Add New Field column An JD field is automatically added to the table Begin Tablei ID Add New Field New typing to add data Entering data is similar to entering data in an Excel spreadsheet however data must be entered in contiguous Table Contains rows and columns starting at the upper left corner of the Only Data datasheet Do not format the data by including blank rows or columns The visual presentation of your data will be done in forms and reports The table structure is created while you enter data Access 2007 sets the data type of the field based on the type of data that you enter If this is not correct it may be difficult to change the data type after data has been entered into the table New Table in By starting in Design View you set the data types and field Design View properties the way you need them and then add data later Access 2007 does not add the ID field to the new table
62. N Books4U Books 125 EXERCISE CREATE A FORM ADDING CONTROLS 10 11 12 13 14 15 16 17 18 In Form view notice that there is a large space between the field names and the data of the Detail section Click the Design View button on the Status bar in the lower right corner of the screen While holding down the Shift key click each of the Label controls until they are all selected In the Font group on the ribbon click the Align Text Right button Click to bold the text Narrow the space between the fields If you select the label control of the field the text box control will be selected automatically at the same time While holding down the Shift key click ISBN Title Author and P Label controls to select them Notice a broken border around the corresponding Text Box controls Position the cursor on the bottom right corner of any of the selected Label controls until the cursor changes to a diagonal double arrow Left click and drag the mouse straight up 1 4 inch All Label and Text Box controls are resized Place the Publisher s name below the P The publisher s name is in the Publishers table To display the Field List pane click the Add Existing Fields button in the Tools Add Existing Property group on the ribbon The Field List pane opens piis Sheel K e Ela In the middle section of the Field List pane Fields available in related tables click the plus sign 4 nex
63. NOT NULL Field must not be blank IS NOT NULL AND Both conditions must exist for data to be accepted gt 1 AND lt 10 OR At least one of the conditions must exist Red OR Blue 61 EXERCISE VALIDATION RULE VALIDATION TEXT The Books4U database must ensure that if a book appears in a transaction the quantity must be greater than zero at least one book is purchased This field cannot be left blank 1 Open the Transaction Details table in Design View 2 Place your cursor in the Data Type column of the Qty field 3 Inthe Field Properties pane click the Validation Rule box we will not use the Expression Builder for this example Type gt 0 and Is Not Null Field Properties General Lookup Field Size Long Integer Format Decimal Places Auto Input Mask Caption Default Value Validation Rule gt 0 And Is Not Null Validation Text 4 Savethe Transaction Details table Toggle to Datasheet view 5 To test the rule type 0 in the Qty field and press Enter Access 2007 checks the data entered against the data validation rules for this field Because the data entered violates the rule of quantity being STN greater than zero a predefined eme message is displayed and Access BEE docsmotaccepbthe enu Cek ORK E vec eM E ER to correct the entry J Heb To display a message that is easier to understand for the user customize the message in
64. Price fields from the Qty Price field list The fields appear in the query grid Sf Property Sheet xyz 6 On the Query Tools Design tab in the Show Hide group click the ks eq Table Names Totals button to add a Total row to the query design grid Totals di A 2 Parameters Remember that aggregates are calculated on groups of data The Show Hide default option for the total row is Group By To retrieve a listing of total sales for each customer the data must be grouped by customer and then summed 7 In the Total row of the CLName field Group by is the correct option Access will group the data by customer name Keep the default settings 8 Click the Total row of the Total Price field The pull down menu contains the list of aggregate options Select Sum Toggle to Datasheet View to see the results 9 Close the query Save it as Total Sales by Customer CLName SumOfTotal Price CLName Total Price Qty Price Qty Price Group By Group By v rank 9 95 Group By Gilles 42 97 e Avg Hardy 27 47 Min Karry 34 99 Max Count Redding 16 47 Singer 21 98 Sweeny 103 88 Victors 46 40 112 83 Expression Where Zehper 114 EXERCISE CALCULATING AGGREGATES IN QUERIES 10 In the ribbon on the Create tab in the Other group click the Query Design button The Show Table dialog box opens 11 Click the Qty Price query Click Add Click Close In this query w
65. Qty Price query for our record source 1 Use Report Wizard to create the Customer Sales Report On the Create tab in the Reports group click Report Wizard fmm 2 Labels E _ Blank Repot Report Report Ex Report Wizard Design 2 In the Report Wizard dialog box click the down arrow to the right of Reports the Tables Queries list box and click the Qty Price query to select it 3 In the report customer name is displayed before transaction number We must choose the fields in the order that they will appear on the report Double click the CLName and then the 7 fields Click the Add all Fields button to move the rest of the fields in the Available Fields list box to the Selected Fields list box Click Next 4 In the next window Access makes an assumption as to how you wish to view the data based on the data in the query By clicking a selection on the left a preview is displayed on the right of the window Click by Customers The preview displays the data closest to the format we are seeking Click Next How do you want to view your data CLName T by Transactions by Transaction Details ISBN Qty Price Total Price 3 Show me more information P Cancel lt Back Next gt p Finish 5 We do not want to add any other grouping levels Click Next 6 We do not need any sort order on the detail information Click Next Note The Customer Transaction and Total Purchase totals o
66. Rule property is used to enter a conditional test that automatically checks for allowable values for that field If the data entered fails to satisfy the condition Access 2007 refuses to accept the data and displays a predefined error message To customize the error message that is displayed when the validation rule is violated type a validation message in the Validation Text property box Expression The Expression Builder helps to Builder build your formula with buttons for common operators Comparison and Logical operators are also available When you double click on the operators in the third column Access moves them up to the Expression Builder window and starts to build your formula You add specific information For example if your date must fall after 2007 create a formula requiring the date to be greater than 12 31 2007 Common Arithmetic Operators Expression Builder 70 And Is Not Null Paste CS ks Cancel Help Operator Function Example NOT Tests for contrary values Use before any comparison NOT 5 operator IN Tests data for values that equal any member of a IN Phoenix Tempe comma separated list List must be in parentheses BETWEEN Range of values State upper and lower bounds BETWEEN 1 AND 10 separated with an AND LIKE Matches patterns in Text and Memo data types Use LIKE Rio wild card characters for a similar match IS
67. Technology Training Services Introduction To Microsoft Access 2007 MARICOPA COMMUNITY COLLEGES Introduction to Access 2007 Written by Kathleen A Moser PhD Technology Training Services April 2008 Maricopa County Community College District April 2008 The Maricopa County Community College District is an EEO AA institution This training manual may be duplicated or put on the Internet for instructional purposes Please give credit to the Maricopa Community Colleges and to the author s This training manual is not to be sold for profit Technology Training Services Maricopa Community Colleges 2411 West 14th Street Tempe Arizona 85281 6942 480 731 8287 http www maricopa edu training TECHNOLOGY TRAINING SERVICES VISION amp MISSION Vision Mission Technology Training Services is dedicated to improving employee job performance at all levels by exceeding expectations in the areas of technology training instructional design and customer support Technology Training Services provides leadership and support to the Maricopa Community College District as the District implements new technologies that address challenging administrative needs and educational standards We design develop and deliver the highest quality in service technology training materials and support to all of the employees of the Maricopa Community Colleges To fulfill this mission we e Provide responsive and accessible technology
68. Verify that the email address is the correct address for that employee To do this compare the employeeid and E fields They should match When Access combines data from two tables it will join the tables based on the relationship in our relationship diagram as shown above the query grid In this case the linking fields are employeeid E Access will join these fields based on an exact match of the data contained in the fields Notice that the names of the fields do not have to match E amp EFName ELName EMiddle BDate HDate Title employeeid email M 318 20 6119 Patricia Yee J 7 27 1987 9 17 2005 Cashier 318 20 6119 cindy alpha gmaricopa edu 325 67 8902 Lori Jones M 11 10 1973 8 21 2005 Bookkeeper 325 67 8902 j54 yahoo com 427 51 6969 Harry Peters H 1 15 1990 9 5 2005 StockPerson 427 51 6969 HP23 9pgmail com 513 45 6789 Ana Rivera T 5 12 1977 8 10 2005 Manager 513 45 6789 ariv Qbks4u com 526 40 7823 John Bullock o 3 30 1986 9 25 2005 Cashier 526 40 7823 letitsnow hotmail com 528 06 7894 Betty Smith p 9 3 1988 9 7 2005 SalesPerson 528 06 7894 holidayfun google com 6 Toggle back to Design View Now that you ve verified that the employeeid fields match you no longer need the employeeid field from the Emails table Delete it by clicking the area in the grid directly above the employeeid field name to select the column and pressing the Delete key Toggle back to Datasheet View Your result now shows the o
69. Word opens and the Sales report is displayed in a Word document Monday April14 2008 Books4U Customer Sales Report CLName Sweeny T To106 ISBN Oty Price Total Price 15008 Transaction To tal Customer Total The file extension rtf signifies that the file is saved in rich text format which preserves formatting such as fonts and styles 6 Save the Word document Close the file 7 Click the close button on the Export RTF dialog box 165 EXERCISE EXPORT 2007 DATABASE TO ACCESS 2003 Ana Rivera manager wants Lori Jones bookkeeper to have a look at the database this weekend and report any errors in recording information However Lori uses Access 2003 at home and the software will not open the Access 2007 Books4U database Ana needs to convert the database so that Lori can use it at home 1 Close all database objects 2 Click the Office Button On the menu for amp p Md Save As under Save the database in another e format click Access 2002 2003 Database ne cen c M DARE Al Save Object As 5 e i Save the current database object as a new 3 At the Save As dialog box navigate to the y Qen objed Access 07 Class folder Modify the name if Ei E PESCH necessary Note the file type H Save the database in another format E m S D Ir KL 4 Click Save e Access 2007 Database Save a copy that will be compatible with s 2007 A 5 The databas
70. a form Default Value Automatically assigns the specified value to a field when a new record is added Does not alter the value in existing fields Saves data entry time Validation Rule Establishes a rule to set boundaries for entering field values Validation Text Displays a message that is displayed when the validation rule is breached Required This field must contain a value in every record if set to Yes If set to No field can be left blank Decimal Places Can specify up to 14 decimal places for number or currency data types Indexed Creates an index to speed up read access to data in this field Append Only Allows edits to a Memo data type while retaining a history of the data in that field over time Changing the property to No will erase field value history 55 EXERCISE SETTING FIELD SIZE amp FORMAT PROPERTIES 1 Open the Customers table in Design View Place the cursor in the data type column of the CState field 2 The field properties pane displays all allowable properties for this field The field size property defaults to 255 characters for the Text data type The CState field will take up only 2 characters By changing the field size to 2 Access will not allow an entry greater than 2 characters thus validating the data Access will not however validate which characters are allowable for the field Change the field size to 2 3 Savethe Customers
71. ad Grandma Kelly s Homestead Condiments 12 d cle Bob s Organic Dried Pears Grandma Kelly s Homestead Produce 12 column A thick black line appears prthwoods Cranberry Sauce Grandma Kelly s Homestead Condiments shi Kobe Niku Tokyo Traders Meat Poultry between columns as you drag e indicating the position the new position of the column when you release the mouse Select both the Category and Supplier columns Position the mouse pointer in the Category column heading until the pointer changes to a downward pointing black arrow hold down the left mouse button drag right until the Category and Supplier columns are selected and release the left mouse button Click the Ascending button and then click in any cell to deselect the columns The records are sorted first alphabetically by Supplier then alphabetically by Category Product Name H Category t Supplier f Quantity Click on the Close button ax to close C te de Blaye Beverages Aux joyeux eccl siastiques 12 75cl the Products table Click No when Stee verte Beverages Aux joyeux eccl siastiques 750 cc pei Sasquatch Ale Beverages Bigfoot Breweries 24 1202 prompted to save the changes Steeleye Stout Beverages Bigfoot Breweries 24 1202 Laughing Lumberjack Lager Beverages Bigfoot Breweries 24 1202 Chai Beverages Exotic Liquids 10 boxes Chang Beverages Exotic Liquids 24 1202 Lakkaliko ri Beverages Karkki Oy 500 ml Ipoh Coffee Beverages Leka Trading 16 500g Outback Lager Beverag
72. adding and maintaining records in tables to make the task easier for employees We will use the Form Tool to create a simple form for the Employees table 1 Left click the Employees table in the Navigation pane to select the table 2 Inthe ribbon click the Create tab In the Forms group click the Form button Access creates the form using all fields in the table in a vertical layout The records are displayed in Layout View with the Form Layout Tools Format tab active Remember the views are available on the Format tab in the Views group You also have quick access to the form views at the right end of the status bar in the lower right corner of the screen Form View ERA Design View Layout You will recall that the Employees table is related to the Transactions table in a one to many relationship Access adds the datasheet to the form that is based on the related table You can delete the datasheet from the form if you do not need it in Layout view click anywhere in the datasheet and press the Delete key Do not delete the related datasheet Cem Employees Ef 318206119 EFName Patricia ELName Yee EMiddle J BDate 7 27 1987 HDate 9 17 2005 Title Cashier Employees table is the table that the form is based on email cindy alpha maricopa edu T z Cs H TDate H Sb T0109 623179543 11 Feb 08 S20 TO110 658652654 11 Feb 08 S20 Record Lei nr Search Re
73. also the most limited You cannot order the data nor can you request specific data Retrieve a listing of the books in our database Include title quantity and price 1 Close all database objects In the ribbon on the Create tab in the Other group click the Query Wizard button Click Simple Query Wizard and then OK 3 Choose the table and fields that you want in the result In the Table Queries textbox use the pull down menu and select the Books table 4 Under Available Fields double click Title QtyOH and Price This action adds those fields to the Selected Fields list in the order you would like them displayed in the result Click Next Which fields do you want in your query You can choose from more than one table or query Tables Queries Table Books Le Available Fields Selected Fields ISEN Tie Author Cancel 5 In the next window keep the default choice to view Details Click Next 6 Name the query Books Qty amp Price Click Finish 3 Books Qty amp Price Title QtyOH Price The Greatest Salesman in the World 2 7 99 Beginner s Guide to Computer Basics 4 21 99 Personal Excellence 5 17 95 Scan Proof Your Life 4 10 36 As a Man Thinketh 3 9 95 The Power of Focus 6 14 95 Think and Grow Rich 8 18 00 E 52 7 Close the result window The result instructions are automatically saved 92 EXERCISE QUERY WIZARD FIND DUPLICATES We want to know wh
74. and 1558747524 2 select Sum 766825361 1 785288376 1 Once a totals row is created it can never be 1557423601 1 removed Instead you hide the row When 312332181 1 you display the row again Access 671745212 1 remembers the function that you applied to 05532775 7X 1 each column in your datasheet and the row Ium 2 Em f 1557423601 2 appears in its previous state 596527608 1 761140670 1 7 Note that the Totals button you clicked 789 to create the totals row is active adl eeng Sum orange To hide a totals row click the 7661 Average Totals button it acts as a toggle 979 1554 Maximum 1595 Minimum 451 Standard Deviation Variance 8 Access removes the totals row 113 EXERCISE CALCULATING AGGREGATES IN QUERIES The aggregate feature for queries allows more flexibility in summarizing data Ana Rivera manager of Books4U would like a listing of the total sales to date for each customer and a listing of the total sales for each transaction 1 Close all database objects 2 In the ribbon on the Create tab in the Other group click the Query Design button The Show Table dialog box opens 3 We have already calculated the total price paid total sales for each transaction detail using an expression That data result is a query and is named Qty Price Click the Queries tab in the Show Table dialog box 4 Click the Qty Price query Click Add Click Close 5 Double Click the CLName and Total
75. appears that each field will have one value For example a Book has only one price a Customer has only one address and an Employee has only one title The Transaction table contains data that will cause problems for database maintenance First you should not store the results of calculations in tables as is the case with the Total field Access can perform the calculation when you want to see the result Access will calculate this total each time the report is printed This ensures that you receive the most current data each time Information should be stored in its smallest form Customer Name should be separated as first name last name However customer first and last names are already stored in the Customers table When we relate the tables we will resolve this situation The current design for Transactions will cause unnecessary duplication of data For example consider a situation when a customer purchases several books at the same time Name Address Date Employee ISBN Qty Price Jones 123 Any St 1 27 08 Smith 0145879 14 95 Jones 123 Any St 1 27 08 Smith 2587468 a 21 45 Jones 123 Any St 1 27 08 Smith 4258713 11 99 40 DESIGNING THE DATABASE Transaction Table Transaction Detailed Items 5 Identify Keys 6 Determine Table Relationships The transaction entries for this purchase create unnecessary dupli
76. ar filter from Customer ED 10254 Chop suey Chinese Buchanan Jeremy 11 Jul 1996 08 Aug 1996 23 Jul 1996 United Packd meno 10370 Chop suey Chinese Suyama Michael 03 Dec 1996 31 Dec 1996 27 Dec 1996 United Packd Cactus Comidas para llevar 10519 Chop suey Chinese Suyama Michael 28 Apr 1997 26 May 1997 01 May 1997 Federal Ship da dus t 10731 Chop suey Chinese King Robert J6 Nov 1997 04 Dec 1997 14 Nov 1997 Speedy Expr Com rcio Mineiro 10746 Chop suey Chinese Davolio Nancy 19 Nov 1997 17 Dec 1997 21 Nov 1997 Federal Ship pH US Sy 10966 Chop suey Chinese Sawyer Margaret 20 Mar 1998 17 Apr 1998 08 Apr 1998 Speedy Expr Drachenblut Delikatessen 11029 Chop suey Chinese Sawyer Margaret 16 Apr 1998 14 May 1998 27 Apr 1998 Speedy Expr Du monde entier 11041 Chop suey Chinese Leverling Janet 22 Apr 1998 20 May 1998 28 Apr 1998 United Pack New CHEERS d Record M4 4of8 gt M IA M Plet starch 4 The Record Navigation Bar indicates that the data is filtered This customer is having problems with shipments from MzSelection 29 the Replace Speedy Express Filter out those records so that we can focus Equals Speedy Express Does Not Equal Speedy Express N on them Choose a Speedy Express record from the column Ship Via in the display Click the Selection button in the Filter amp Sort group Click Equals Speedy Express Contains Speedy Express Does Not Contain Speedy Express 6 The
77. are also prohibited Apply Filter Enter the first set of values then click the Or tab and enter the next set of values Each Or tab represents an alternate set of filter values To apply the filter click the Toggle Filter button in the Sort amp Filter group Access will show only those records that match your input Undo the Filter To undo a filter click the Toggle Filter button again To see the full view of the data click the filtered button on the Record Navigation Bar Remove or l Reapply To remove the filters a Filter 1 Click Filtered on the Record Navigation Bar to return to the full view of data Selection d 2 A d Y Advanced Filter e Fit We Clear All Filters Sort YS Filter By Form N Click the Advanced button on the ribbon and choose Clear All Filters from the drop down list Y Apply Filter Sort When you remove the current filters the filters are temporarily removed from all of the fields in the view until you clear them To reapply the most recent filters x Advanced Filter Sort 1 Click Unfiltered on the Record Navigation Bar 2 The filter will be available as the most recent until all filters are cleared 25 EXERCISE FILTERING BY FORM Contact all Marketing Assistants in Brazil and Canada about new marketing procedures 1 Open the Customers table 2 Click the Advanced button in the Sort and Filter group on the ribbon Select Filter by Form from the
78. atasheet view to enter the data Entering data in Access is similar to entering data in an Excel spreadsheet However start in the top left column and enter data by rows not by columns Use the Tab key to advance the cursor to the right Access will Save the data to the table when you press the Enter key at the end of the row Populate Tables Our Books4U database contains several tables This workshop is by Importing not an exercise in typing data into a table We will therefore Data populate our tables by importing data from multiple external sources This is common if you are switching from Excel to Access upgrading from a previous version of Access to Access 2007 or simply copying and pasting a portion of another table within your database What can be When you import data a copy of that data is placed in a new or Imported existing table without altering the source file Data can be imported from several sources Excel spreadsheets a table in another Access database SharePoint lists text files HTML documents and other sources Options When importing data from sources such as Excel you can specify various data storage options importing the data into a new table appending a copy of the records into a specified table or linking to the data source using a linked table See Appendix C to link data The Get External Data dialog box is used to specify the source and destination of the data to be imported The import steps can also be
79. ate Crosstab Delete Table Results Query Type 8 Onthe Query Tools Design tab in the Results group click the Run button Access displays a warning message that data will be updated and that the operation cannot be undone Click Yes Microsoft Office Access EJ You are about to update 1 row s i A Once you click Yes you can t use the Undo command to reverse the changes Are you sure you want to update these records 9 To view the results of the update open the Publishers table Simon amp Schuster is now the PName for publisher 324 PName Success Publishers Wildside Press Simon amp Schuster Warner Books Adison Wesley Rich Press 10 It is not necessary to save this query Close the query window Click No 106 EXERCISE ACTION QUERIES MAKE TABLE The Books4U database contains an Employees table and an Emails table Our relationship diagram shows a one to one relationship between these two tables It was stated earlier that one to one relationships are rarely included in the database depending on the amount of data it may be more efficient to combine the tables The employee s email address was overlooked during the design phase of Books4U and was added later into another table Because email adds only one more field we can combine the data in these two tables using a query without having to manually enter the email address into the Employees table Once the data is combined we then mak
80. ate tab in the Other group click the Query Design button The Show Table dialog box opens Select the source data Customer name is in the Customers table Transaction number ISBN qty and price are in the Transaction Details table The Transactions table shows the relationship junction table between Customers and Transaction Details Select the Customers table press and hold the Ctrl key and select the Transaction Details and Transactions tables Release the Ctrl key Click Add Click Close Double Click T in the Transactions field list Next double click the CLName field in the Customers field list Double click ISBN Qty and Price in the Transaction Details field list The grid displays the field names and table names of the required data stored in the database Click in the Field row in the blank column next to Price Type the expression Total Price qty price Field T CLName ISBN Qty Price Total Price qty price Table Transactions Customers Transaction Details Transaction Details Transaction Details Sort A Show v V MI Criteria s e s or On the Query Tools Design tab in the Show Hide group click the Property Sheet button In the Format property box use the pull down menu to display field formats Select Currency Click the close button at the top right of the Property Sheet task pane Toggle to Datasheet View T amp si CLName ISBN Qty Price Total Price T010
81. ates and visible aggregates An aggregate function can also be performed on one or more columns of a query result set without having to change the design of your query Calculating totals for queries is more flexible data can be categorized into groups and totals can be calculated for each group Calculating total sales for a particular book is possible in this case Use the Totals button in Design view to add a Totals row to the query grid Several aggregate options are available to choose from 112 EXERCISE CALCULATING AGGREGATES ON TABLES 1 Close all database objects 2 Make a copy of the Transaction Details table Single click on the Transaction Details table in the Navigation Pane to select it Click the Office button and select Save As from the drop down menu Click Save Object As 3 In the Save As dialog box the Save Transaction Details To textbox defaults to the name Copy of Transaction Details Click OK The table opens in Datasheet view 4 Onthe Home tab in the Records group click Totals New x Totals 2 cA Save 7 Spelling Refresh All X Delete More Records The Totals row appears beneath the asterisk row In the Totals row click the down arrow in the column that you want to calculate 5 Click the Totals row in the Qty column Click the down arrow and select Count ISBN z 055327757X 1 6 Click the Totals row in the Price 446530697 1 column Click the down arrow
82. automatically You have more control in setting the primary key field and the overall table structure To open Tablel in Design View on the Home tab in the Views group click on the Design View button You will be asked to save the table At this point give the table a name and click OK cu Ca id x od e Home Create GC I Calibri View Paste B Views Clipboard Steps to Create a 1 Click the Create tab on the ribbon New Table in Design View 2 Click the Table Design button in the Tables group 46 47 EXERCISE CREATING A NEW TABLE IN DESIGN VIEW 1l Tablel has been created and is currently displayed in Datasheet View 2 Onthe ribbon make sure that the Home tab is active In the Views group click the View button drop down list Ga d EISE s a Home Create External Data ia BZ 3 7 Cien Vio Aen SC View us 5 BZ U A gt Font Datasheet View A D Design View 3 The Design View icon is the default display Click Design View Notice that the View button is a toggle between the Datasheet and Design Views 4 You will be asked for a permanent table name to continue Type Customers and click OK Save As TS zl Table Name Customers OK Cancel 5 To create a new table in Design View directly make sure that the Create tab is active on the ribbon 6 Inthe Tables group click the Table Design button EV 2 Home Create z
83. b 08 9 49 pie Record 4 4 1of6 bM No Filt Search Record M 4 5of6 boo Hob KK No Filter Search 132 CONTROL LAYOUTS Arranging the Display Tabular Layouts Stacked Layouts Arranging the Display Switching Control Layouts Removing Controls from Layouts When forms are created Access arranges the label and text box controls into one of two control layouts Tabular and Stacked Control layouts are guides that align the controls horizontally and vertically to give your form a uniform appearance You can have multiple control layouts of either type on a form In tabular layouts controls are arranged in rows and columns like a spreadsheet with labels across the top Tabular control layouts always span two sections of a form whichever section the controls are in the labels are in the section above In stacked layouts controls are arranged vertically with a label to the left of each control Stacked layouts are always contained within a single form section Access automatically creates stacked control layouts when you create a new form by clicking Form in the Forms group on the Create tab or by clicking Blank Form in the Forms group on the Create tab and then dragging a field from the Field List pane to the form On an existing form you can create a new control layout by first selecting the control and then clicking either the tabular or stacked buttons in the Control Layout group on the Arrange tab
84. cate data Name and address are already stored in the Customers and Employees tables Name address and date are repeated for each book purchased on this transaction We need to revise our design The problems arise in the Transaction table because there are two subjects being represented the transaction and the items on that transaction We need to separate them The transaction data should describe characteristics about the transaction date customer the shipper and the employee The transaction items include ISBN qty and price The modified design is shown below Transaction Customer Date Employee 12546 C02 01 81 08 E05 Transaction ISBN Qty Price 12546 0145879 1 14 95 12546 2587468 1 21 45 12546 4258713 1 11 99 Our modified design shows the transaction as the only duplication However this is a necessary duplication as it links the two tables together and creates a relationship More about this later Each table should include one or more fields identified as the Primary Key field The Primary key ensures that all records in the table are unique and thus reduces unnecessary data redundancy The primary key field for the Transaction table is transaction What is the primary key for the Transaction Detailed Items table In a relational database information is divided into separate subject based tables Table relationships are used to bring the information to
85. ce Total Price T Footer CLName Footer 4 Page Footer ee Ee b ttttttzssrttttsagn 4 Click the Report View button on the Status bar in the lower right corner of the screen 5 Examine the data The report is very similar to the sketch provided We need to add labels to identify each aggregate total Click back to Design View 6 Adda Label control to the T Footer section On the Design tab in the Controls group click the Label control 146 EXERCISE ADDING TOTALS TO A REPORT IN DESIGN VIEW 7 Inthe 77 Footer section draw a Label control box to the left of the Total Text Box control Label the control Transaction Total Repeat the process for the CLName and Report Footer sections labeling the controls Customer Total and Total Purchases respectively T Footer CLName Footer 4 Page Footer Report Footer 8 Click the Report View button on the Status bar in the lower right corner of the screen to view the changes The report is easier to read with the label controls in place CLName Zehper T To1o2 ISBN Oty Price Total Price 15587 2 14 95 29 90 Transaction Total 29 90 T To107 ISBN Oty Price Total Price 59652 2 534 99 534 99 7614c 1 12 95 12 95 78973 2 534 99 534 99 Transaction Total 82 93 Customer Total 112 83 Total Purchases 365 48 9 Save the report The next
86. cord M 1of6 WY d Search Record Navigator i bar for the form Record Navigator bar for the datasheet 118 x 2e Febo Transactions table is the related table EXERCISE CREATE A FORM FORM TOOL 10 11 Click the Form View button on the Status bar in the lower right corner of the screen You can browse through the data of the Employees table in this view as in Layout view and you can make modifications to existing data or add new data in Form view Use the Record Navigator bar at the bottom of the form to advance the Employee records Note that the datasheet will show the related Transactions for each employee Go to a new blank Employee record by clicking the New Record button on the Employees record navigator bar Record 4 1 of 6 bbs b New blank Record Click the EZ textbox Enter a new record using the following data Tab to advance to the next field E 999 999 999 don t include dashes EFName Your first name ELName Your last name EMiddle Middle initial BDate Your birth date HDate Today s date Title Salesperson Email Your email address Click the first new record of T in the datasheet Enter a new related record using the following data Tab to advance to the next field Press the Enter key to accept the changes T T0111 C 699 74 5211 don t include dashes TDate Today autofilled S S30 Close the form Click Yes to save Name the form Employee Form Click OK In the Naviga
87. d 120 7 Close the form Click Yes to save the changes Name the form Customers Split Form EXERCISE CREATE A FORM BLANK FORM TOOL The Blank Form tool is a quick way to build a form especially if you plan to use only a few fields 1 In the ribbon click the Create tab In the Forms group click the Blank Form button Access opens a blank form in Layout view and displays the Field List pane In the Field List pane click the plus sign next to the Shippers table E Formi x Field List X H o f Fields available for this view x Shippers i i Edit Table Sit S10 sd SName State Handlers SName SContact SContact Monica Jones SPhone iSPhone 480 555 2525 Label Control Fields available in related tables Transactions Edit Table Text Box Control Fields available in other tables Books Edit Table Customers Edit Table Emails Edit Table Employees Edit Table Publishers Edit Table Transaction Details Edit Table Record 4 4 1of5 gt ES Search E Show only fields in the current record source 2 To add a field to the form double click the S7 field or click and drag it to the form To add all remaining fields from Shippers at once hold down the CTRL key and click each field Drag them to the form As with the Form and Split Form tools each form created is a series of objects called controls Each field from the table has a Label Control and a Text Box Control Th
88. d Mail Merge With Access Data 155 Create Mailing Labels ee do Eech 159 Integrate Programs Exporting Data eese 163 Export A Table Lo EE 164 Export A Report To Ms Word AAA 165 Export 2007 Database To Access 2003 sese 166 Clone The Database 42 52 59 ne be etn deg 167 Appendix A On Line Help 171 Appendix B Database Templates eese 172 Appendix C Link To An External Table eeeeeeeeeeeen 173 Appendix D Compact And Repair 174 ACCESS 2007 OBJECTIVES This workshop has been designed to present the following competencies l 2 3 4 5 10 11 12 13 14 15 16 17 18 19 Review general database concepts and terms Describe the Access 2007 interface and database objects Explore an existing database Design the database Create a new database create tables define fields set field properties Create a lookup field Import data from external sources Excel Access 2003 Access 2007 Create relationships Create queries Select queries duplicate unmatched conditional calculation Action queries update make table delete Calculate aggregates sum count on tables in queries Create forms and subforms Manage control layouts and conditional formatting Create reports Create a group report Add controls to reports Use MS Word mail merge with Access
89. d the end result Gather those documents that are required for day to day business operations such as sales slips purchase invoices books lists and customer information Include spreadsheets printed documents index cards and any other paper or electronic format used List each item that comes to mind Don t worry about including everything perfectly at first Be sure to get input from everyone who will be using the database 38 DESIGNING THE DATABASE Consider the types of reports that you will want the database to generate Are there inquiries for your product that come up often such as book prices quantity on hand publisher information or release dates for new books that you need to access quickly Do you need to store pictures logos comments or perform calculations Preliminary List of Required Data ISBN Order Sales Date Publisher Contact Title Order Date Supplier Name Shipper Name Publisher Name Purchase Date Supplier Address Shipper Address Qty Discount Price Supplier Phone Shipper Phone Cost Employee Supplier Contact Shipper Contact Price Pay Rate Employee Name Credit Card Customer Name Start Date Employee Address Book Category Customer Address Birth Date Dependents Total Amount Customer Phone Author Publisher Address Item Contact PO Publisher Phone Qty On Hand 3 Arrange Data The above list is a subset of the data required for Books4U Each Into Tables data item describes or is part of an entity i
90. d to the assist the user in data entry These objects or controls are formatted to create a graphical user interface to enter display and edit data Forms can be customized to include calculations graphics and other objects Reports are screen outputs of data arranged in various formats as required by the user Reports can also be customized to include calculations graphics and other objects and are most often created for the purpose of printed output Pages Macros and Modules are beyond the scope of this introductory class and will not be discussed Tabbed A new feature to Access 2007 is Tabbed Document Window Documents Viewing This feature allows all open database objects to be displayed in the same window allowing easy navigation between the objects za Customers zi Orders Cl Employees 2 Quarterly Ord 9 Invoice When converting a 1997 2003 Access database to a 2007 Access database your tab viewing feature may not automatically activate You can activate this feature through the Access Options button on the Office Button pull down menu See the notes on Importing DATABASE OBJECTS Navigation Pane Categories Tables and Related Views ER Categories Table ust kd Alphabetical List of Products ee Si Category Sales for 1997 Tables and Related Views lesch GH Product Sales for 1997 xen Date kd Products by Category PANES Date kd Sales by Category ES Categories E Alphabetical List of Pr
91. dialog box Name the query and select Query as the type Click Cancel 3 Brazil and Canada Cutomers Customer Company Nam Contact Nam Contact Titl Addre Cii Regio Postal Cod Country FAMIA Familia Arquibalc Aria Cruz Marketing Ass Rua Or s Sao F SP 05442 030 Brazil LAUGB Laughing Bacchus Yoshi Tannamu Marketing Ass 1900 Oak Vanc BC V3F 2K1 Canada MEREP Mere Paillarde Jean Fresniere Marketing Ass 43 rue St Moni Qu bec HU 1C3 Canada QUEEN Queen Cozinha L cia Carvalho Marketing Ass Alameda Sao F SP 05487 020 Brazil 7 Remove the filter from the Customer table by clicking the Toggle Filter button 26 PREVIEWING amp PRINTING FORMATTING THE DATASHEET Print Options Access 2007 has several options for printing the datasheet view Quick Print will send the object to your default printer without changing any print settings The Print Preview command lets you see how your data will look on the printed page and assists in changing layouts before printing The Print command allows you to make changes to the layout before you print an object Click the Microsoft Office button and then click Print The Print dialog box appears Preview and print the view ih Print Wee Select a printer number o other printing options X Print Preview review and make changes to pages before i Print Manage Printing Items in a Datasheet Access Help facility To Se
92. displaying subdatasheets is the ability to edit in a table while viewing related information from another table Because duplicate data is reduced switching back and forth between table tabs would otherwise be necessary to ensure you are editing the correct record If a table has more than one relationship defined you can choose the subdatasheet you want to see using the More button in the Records group on the Home tab of the ribbon New Totals 4l y se cH Save 4 spelling l P o Ad Filter Delete 53 More Records lt 2 AF Row Height I Subdatasheet gt Hide Columns N Unhide Columns EH Ereeze Unfreeze F3 Column Width If you switch to another subdatasheet and close the primary table you will be prompted to save the layout changes This means that the next time you open the table and use the expand indicator to display a subdatasheet the new subdatasheet will display Using the Expand All submenu will open all subdatasheets of related data to the primary table The Collapse All submenu will close all subdatasheets 14 EXERCISE DISPLAYING SUBDATASHEETS 1 Click the expand indicator next to record to display the related record in a subdatasheet Employees Em City LastName First Name Title T 1 Seattle Davolio Nancy Sales Associate 2 Tacoma Fuller Andrew Vice President 3 Kirkland Leverling Janet Sales Associate 4 Redmond Sawyer Margaret Sale
93. drop down lists to choose the field from your database that corresponds to the address information Mail Merge expects listed on the left Press the Enter key twice to advance the cursor 2 lines 7 Remember this matching for this set of data sources on this computer We can place the greeting line in our letter in one of 2 ways on o J eg the Mailings tab in the Write amp Insert Fields group click Greeting line Clicking the Greeting line in the Mail Merge pane will display the Insert Greeting Line dialog box Select a format to display the recipient s name Click the Match Fields button The Match Fields dialog box opens Use the pull downs to resolve any conflicts with the required names for the greeting line and the field names in the database table Click OK View the greeting line in the preview box Click OK Before moving to the next step select the Address Block on your letter Right click and select Paragraph The Paragraph dialog box opens Modify the spacing specifications to ensure that the address block prints out in single space Change the Before After spacing to zero Click OK April 9 2008 AddressBlock GreetingLine Spacing This Satu Before Appreciat i Line spacing There will After Single will follow E Don t add space between paragraphs o Same style 157 EXERCISE ACCESS TABLE AS A DATA SOURCE 15 The address block and greeting line set up is now i
94. drop down menu list 3 Using the pull down menus click Marketing Assistant from the Contact Title column and select Canada from the Country column We also need marketing assistants in Brazil Click the Or tab at the bottom of the window Again using the pull down menus place Marketing Assistant and Brazil in their respective columns Z Customers Filter by Form Contact Name Contact Title Address City Region Postal Code Country Phone o Marketing Assistant Canada Lookfor 4 Or 7 4 To apply the filter click the Toggle Filter button on the ribbon Notice the Filtered indicators in the result EE Customers Company Nam Contact Nam Conta tW Addre Cit Regio Postal Cod Country tv Assistant Alameda Sao F SP 05487 020 Brazil ing Assistant Rua Or s S o F SP 05442 030 Brazil eting Assistant 43 rue St Moni Qu bec HU 1C3 Canada arketing Assistant 1900 Oak Vanc BC V3F 2K1 Canada Queen Cozinha L cia Carvalho Market Familia Arquibalc Aria Cruz Mar M re Paillarde Jean Fresni re M Laughing Bacchus Yoshi Tannamu 5 Click the Filtered button on the Record Indicator Bar to temporarily remove the filter To reapply the most recent filter click on the Unfiltered button jamm Save Customers to 6 To save the filtered data as a query click the Office button and Brazi and Canada Cutomers choose Save As to display the Save As
95. e 19 Open the Transactions table in Datasheet view to examine the data Toggle to Design view to check formatting 20 Finally import data into the Transaction Details using the same Append procedures as outlined above Use the Browse button to locate the Transactions Data file in the Access 07 Class folder on your Desktop 21 The data destination is the Transaction Details table 22 The Excel worksheet that contains the data is the Transaction Details worksheet 23 Check the data in both Datasheet and Design view of the Transaction Details table 70 EXERCISE IMPORTING FROM AN EXCEL SPREADSHEET The Books table is the next table we will import Data for this table is also stored in an Excel spreadsheet However the import procedures will vary slightly as we do not currently have a table defined for this data Access will create a new table to store the data Formatting will be done after the data has been entered 24 Close all tables 25 On the External Data tab on the ribbon in the Import group click the Excel button The Get External Data Excel Spreadsheet wizard opens 26 In the File Name box specify the Excel file that contains the data Use the Browse button to locate the Book Data file in the Access 07 Class folder on your Desktop 27 To specify the data destination click the radio button to Import the source data into a new table in the current database Because we currently do not have a table created t
96. e label control contains the field name The text box control is the field placeholder where data is retrieved from the database and can be entered or edited The controls can be moved resized formatted or deleted from the form 121 A form s style can be modified after the form has been created by changing the color theme and fonts Tools in the Controls group on the Format tab can be used to add a logo title page numbers or the date and time to a form We ll customize the form next 122 EXERCISE CREATE A FORM BLANK FORM TOOL 3 Close the Field List task pane at the right side of the work area by clicking the Close button at the top right corner of the pane The form is in Layout view 4 Onthe Format tab in the Controls group there are hal 5 Title E several control tools that we can use to customize the L p form Click the Logo button 09 EY Date and time wf Fields i Controls 5 At the Insert Picture dialog box navigate to the Access 07 Class folder on the Desktop Double click MCCCDlogo 6 Click the Title button in the Controls group Click the Title control to rename it Books4U Shippers Form Press Enter to commit the change 7 Click the Title control Position the mouse pointer on the right edge until the cursor changes to a left right pointing arrow click and drag the right border to accommodate the title to display on one line To reposition the control click inside the control and drag
97. e all of the fields in the Available Fields list box to the Selected Fields list box Click Next gt 4 With Columnar already selected as the form layout click Next 5 Using the scroll bar preview each style s colors and backgrounds in the preview window Click Flow to select it and then click Next 6 Name the form Books Form Click Finish Switching to Design view will add a wider variety of controls to the form On the Design tab in the Controls group the number of available controls is displayed We will use several of these controls to modify our Books form REES Logo Text Label Button Ei Box s3 Controls 124 EXERCISE CREATE A FORM ADDING CONTROLS The Books Form was created by Access and opened in Layout view We will switch to Design view to take advantage of additional controls 1 Click the Design View button on the Status bar in the lower right corner of the screen The Books form is divided into three sections Form Header Detail and Form Footer The Form Header section contains the title which is the name of our form We can also add a logo and a date to the header section 2 Expand the Form Header section Roll the cursor to the area between the Form Header and Detail sections When the cursor changes to a double arrow left click and drag the line down to expand the header section 3 Click the title Rename the title Books4U Books Click a blank area of the form to dese
98. e are looking for total sales for each transaction The data comes from the same source however we need to group the transaction data and sum the total sales Include the Customer s name in this list to compare with the previous result 12 Double Click the T CLName and Total Price fields from the Qty Price field list The fields appear in the query grid 13 On the Query Tools Design tab in the Show Hide group click the Totals button to add a Total row to the query design grid 14 In the Total row of the T field Group by is the correct option Access will group the data by Transaction number Keep the default settings 15 In the Total row of the CLName field Group by is the correct option Because there is only one customer per transaction Access will group the data by Transaction number and customer name together Keep the default settings If you do not specify the Group by option for customer name Access will display the following message Microsoft Office Access EJ i You tried to execute a query that does not include the specified expression CLName as part of an aggregate function OK Help 16 Click the Total row of the Total Price field The pull down menu contains the list of aggregate options Select Sum Toggle to Datasheet View to see the results 17 Close the query Save it as Total Sales gt T CiName SumofTotal by Transaction T0100 Singer 21 98 T0101 Karry 34 99 T0102 Zehper 29 90
99. e click Author Publisher name comes from the Publishers field list Double click PName The grid displays the field names and table names of the required data We have all of the required fields We must now tell Access to display the results alphabetically by book Title Click the Sort row in the Title column Choose Ascending from the drop down menu Publishers 9 ISBN SR Title y pe Author PName QtyOH Price p Descending Criteria not sorted or 96 PName Publishers v EXERCISE SELECT QUERIES ADDING CRITERIA 6 Restrict the data result further by specifying the Publisher s name The query calls for those books published by Wildside Press In the Pname column on the Criteria row type Wildside Press Field Author PName Table Books Publishers Sort Show Kl v v v Criteria Wildside Press or 7 Toggle to Datasheet view Access displays the result of the query ISBN H Title x Author H PName 761140670 Never Too Late to Love a Computer Abby Stokes Wildside Press 521528747 Global Warming The Complete Briefing John Houghton Wildside Press 451205367 The Richest Man in Babylon George S Clanson Wildside Press 1599869802 Acres of Diamonds Russell H Conwell Wildside Press 446394599 Lincoln on Leadership Donald T Phillips Wildside Press 979738504 Lawyers are Liars Mark J Kohler Wildside Press When you save the query Access does not save this result The instructions that
100. e for the Books4U database 43 CREATE A NEW DATABASE Start Fresh Thus far we have viewed an existing database and examined the process of database design In the following sections we will develop and implement the five tables outlined in the relationship diagram of the design section When you close a database or launch Access 2007 the Getting Started welcome screen is displayed There are three options to opening a database We followed the third option Open Recent Database to get acquainted with the Northwind database Another option is to open a database using a template We will not be using a template in this course More information on templates can be found in Appendix B To create a new database click the Blank Database button under the New Blank Database section of the Getting Started screen You are then prompted for a database name Access 2007 will save Auto Save changes to your database as you work and will use this name during development To change location of the file click Browse next to the File Name box browse to and select the new location and then click OK Access 2007 database file names end with the file name extension accdb and this is added to your file name Click the Create button Steps to Create a New Database Atthe Getting Started welcome screen click the Blank Database button under the New Blank Database section Type in your database name in the Blank Database pane Change th
101. e in the alternate format opens e a Access 2002 2003 Database A tanase CE 6 Click the Options button on the Message bar m a Access 2000 Database and Enable the content m ren C777 EE 7 In the Navigation pane use the pull down to NUMOS click Object Types and All Access Objects T close Database displaying all objects in the database 2 Access Options X Exit Access Ee EE Navigate To Category Custom v Object Type Tables and Related Views Created Date Modified Date Filter By Group Tables Queries Forms Reports y All Access Objects 8 Examine the list to be sure that all objects were transferred 9 Close the database 166 EXERCISE CLONE THE DATABASE The Books4U database did an excellent job of tracking annual data Ana Rivera wishes to start the New Year with new data and would like to archive data from the existing year However she does not want to recreate the database structure Cloning the database will allow us to import the database structure without the data It will keep the original database in tact 1 Open the Books4U database Click the Options button on the Message bar and Enable the content 2 Click the Office Button and then click New 3 Atthe Getting Started with Microsoft Office Access welcome screen click the Blank Database icon in the middle pane 4 In the right pane click the browse icon Navigate to the Access 07 Class folder Name the
102. e last day of each month in the Label control Click a blank area of the form to deselect the label control 22 Click the label control again to activate the Font group to change the italic and bold font properties Align the control in the center of the form Click the Form View button on the Status bar in the lower right corner of the screen to view the changes 05 Mar 08 055327757X The Greatest Salesman in the World Og Mandino Warner Books Orders are completed on the last day of each month Record M lof34 gt Mk K Search 23 Click the New Record button on the Record Navigator bar Access creates a blank record Enter information for a fictitious book with you as the author Type 100 for D As you tab to Pname you will notice that Access auto fills the data from the database one to many relationship between Books and Publishers 24 Save the form Close the form 127 NOTES 128 SUBFORMS Form tool Sub datasheet Sub Forms Forms are linked When working with related data tables you often need to view data from more than one table or query on the same form For example customer information and all data for each order placed by the customer is on the same form You will recall that Access 2007 creates a similar display with the Form tool if the form data has a one to many relationship with another table query in the database Access adds a datasheet to the form that is based on the r
103. e location of your file by clicking on the Browse icon next to the File Name box Select the new location and click OK Access 2007 will add the extension accdb to your file name Click the Create button 44 EXERCISE CREATING A NEW DATABASE 1 Close the Northwind database Click the Office button at the top left of the screen and then click Close Database Click the Blank Database button under the New Blank Database section of the Getting Started screen You will be prompted for a database name New Blank Database Blank Database 3 In the Blank Database pane type Books4U in the File Name box Click the file icon to the right of the file name box to place the location of your database in the Access 07 Class folder on the desktop Blank Database Create a Microsoft Office Access database that does not contain any existing data or objects File Name Books4U 3 C Users moserk02 Documents Create Cancel 4 Click the Create button id r a Table Tools a Home Create External Data Database Tools Datasheet bz es PA mE Insert Data Type T s2 ES SS IN ai Seet S Temm View New Add Existing Lookup x SEHR Relationships Object Field Fields Column li Rename f S20 Dependencies Views Fields amp Columns Data Type amp Formatting Relationships All Tables Tablet _ ID Add New Field E Tablet Table New 45 CREATE A TAB
104. e many transaction details Transaction details belong to one and only one transaction Transactions d T KE cs Transaction Details TDate Te se ISBN Es Qty Price 79 NOTES CREATING MANY TO MANY RELATIONSHIPS Table Design Customers In a many to many relationship multiple records in one table can correspond to multiple records in a related table In our Books4U database the relationship between customers and books is that a customer may purchase many books and a particular book may be purchased by many customers Consider the table design required to show this relationship CFName CLName CAddress CCity CState CZip CPhone ISBN 627458965 Abgail Books ca Zehper 45 Oakwood Tempe AZ 85281 4809996547 1558747524 623179543 Hugo Redding 15 E Pear Tempe AZ 85281 4801155564 938716271 623179543 Hugo Redding 15 EPear Tempe JAZ 85281 4801155564 1558747524 Title Marjie Markowski 362 Jensen 155874752 938716271 Linking Fields Redundant Data 4 The Power of Focus Jack Canfield 6 14 95 401 627458965 i No Excuse Marjie Markowski 3 11 95 362 657895123 In the Customers table ISBN is the linking field with the Books table To retrieve more information about the books that Abgail and Hugo have purchased we take the SBN in the customer record and use it to look up the SBN in the book table Conver
105. e precedes the expression followed by a colon For example to calculate a 596 increase in sales the expression is stated as Profit Sales 1 05 On the Create tab in the Other group click the Query Design button The Query grid and Show Table windows display From the Show Table window select the desired table s Click the Add button for each table Click Close From the table s field list double click the required fields to move them down into the query grid Select the fields in the order you want them to appear in the datasheet Enter Criteria to restrict the data retrieved Use the Sort feature to order the data Click Show to display the field In ablank column of the criteria grid enter an expression in the Field row On the Query Tools Design tab in the Show Hide group click the Property Sheet button In the Format property box use the pull down menu to display field formats Click the appropriate format Click the close button at the top right of the Property Sheet task pane Enter sort and criteria information for the new field 1f desired Toggle to Datasheet view Save and name the new query 101 EXERCISE SELECT QUERIES CALCULATIONS Calculate the total price for each detail in a transaction Total Price 2 Qty Price Show transaction number customer s last name ISBN qty price and total price in the result l 2 7 Close all database objects In the ribbon on the Cre
106. e the query our new Employees table so that the data is physically stored Subsequent emails will then be entered into the Employees table and the need for the Emails table is no longer necessary 1 Close all database objects 2 In the ribbon on the Create tab in the Other group click the Query Design button The Show Table dialog box opens 3 Select the Emails table press and hold the Ctrl key and select the Employees table Release the Ctrl key Click Add Click Close We want to select all fields from both tables in the same order as in the current Employees table We will select the emails from the Emails table and place it after the title field Include the employeeid from the Emails table in the grid to verify that the data in the linking field matches 4 Double Click the fields one at a time from the Employees field list Next double click the fields from the Emails field list The grid displays the field names and table names of the required data stored in the database The last two fields are from the Emails table y Es EFName ELName EMiddle BDate V employeeid HDate email Title Field Es EFName ELName EMiddle BDate HDate Title employeeid email Table Employees Employees Employees Employees Employees Employees Employees Emails Emails s Show v vi vi di v Eil L v Criteria or 107 EXERCISE ACTION QUERIES MAKE TABLE 5 Toggle to Datasheet View
107. e the same as they do with Access forms Design view displays the detailed structure of your form While you can modify the report sections and controls you cannot see the data In Report view the data is displayed but changes to the design of the report cannot be implemented Layout view allows you to view the data and make changes to the report design A forth view Print Preview displays the appearance of your report when printed on paper In this view you can set report page layouts before printing the report Report Creation Tools Report Creation Tool Description Report Creates a report that uses all the fields in a table or query The report will be displayed in Layout view Blank Report Aids in the creation of a report from scratch The report is developed by adding and positioning controls of your choice Report Wizard The wizard takes the user through a series of steps to specify the layout and background themes of the report It offers options for grouping and sorting data It also helps you analyze data from one or more tables by providing options for summarizing data using aggregate functions Report Design Helps to create a more detailed structure of your form in Design view With this tool you have a wider range of controls you can add to your report You can also resize report sections and edit text box control sources 137 EXERCISE CREATE A REPORT REPORT TOOL The report tool
108. e warning message Click the pull down menu in the Data Type column of the Phone field and select Text 5 Place your cursor in the first empty row at the bottom of the table Enter Field Name ContactAddress Data Type Text Description Form of address Mr Mrs etc 6 Select the new row by clicking on the record indicator and release the mouse Re click the record indicator and while holding the mouse key down drag the field below the ContactName field 7 Click the Fax record indicator to select the row Click the nsert Rows button in the Tools group of the Design tab 8 Selectthe new blank row To delete the row press the delete key or click the Delete Rows button in the Tools group of the Design tab We do not need to add a field here 9 Click the Close button B to close the table Click No Microsoft Office Access E i Do you want to save changes to the design of table Customers No Cancel 10 We will address field properties and other Design view options later in the course 32 RELATIONSHIPS Related Data E lt 5 Relationships Linked Tables Referential Integrity Types of Relationships Access is a relational database management system The database consists of several subject based tables Data may be retrieved from several tables when generating reports or requesting information Table relationships allow data in tables to be joined to bring the information togethe
109. edmond Sawyer Margaret Sales Associate 1 Place the cursor in the field to be changed Click the Replace Button in the ribbon In the Find What field type the entry you want found and changed In the Replace With field enter the replacement entry Click on either the Replace or the Replace All button A message box opens to confirm the change Click Yes Click the Close button to close the window 18 EXERCISE REPLACING DATA In the title field of the Employees table change all occurrences of Sales Associate to Sales Representative 1 Open the Employees table Click in any record in the Title field From the ribbon click the Replace button In the Find What field type Sales Associate In the Replace With field type Sales Representative Click on the Replace All button Oe SEN ot SOP op A message box opens Click Yes Microsoft Office Access ese You won t be able to undo this Replace operation Do you want to continue Yes No 8 Click the Close button Note that the change has been made 19 SORTING RECORDS How and Why Single Field Sort Multiple Field Sort Remove the Sort A Rules for sorting Alphanumeric Data By default records in a table are displayed alphanumerically and sorted in ascending order by the primary key field values To reduce the time you spend searching for data Access provides several options that will assist you in locating and viewing
110. een 2 The totals for each transaction are displayed in the 7 Footer section We will display the total quantity of books there as well A Text Box control is used to display data that is created from the record source Although our Total Quantity is not stored in the database quantity Qty is stored We will tell Access to sum the Qty field similar to the Total Price field earlier 3 On the Design tab in the Controls group click the Text Box control 4 Position the mouse pointer in the T Footer section directly underneath the Qty Text Box in the Detail section Click and drag to create an object the approximate height and width as the Qty control 5 The Text Box control is Unbound it doesn t have source data To bind the Text Box control to the Qty field and sum the value we need an expression Expressions in Access begin with an equal sign Type sum Qty and then press Enter function as with all functions encloses the arguments in parenthesis Qty is in square brackets to 4 Report Header signify that the data is found in faseHeader um the database The sum ds Books4U Customer Sales Report CLName Header Jeans i THE ES CLEName 6 Note that when we create a Text Box control Access Hus meecnspenuncu m n e n s rene enn penne en pese will also create a Label uU mec MES UU DEE EB control for us In this case feti
111. eking 11 On the Query Tools Design tab in the Query Type group click the Delete button The Delete property now appears on the grid The default instruction for this property is Where The instruction that will be executed is delete all records from the Copy of Customers table where the CCity data Scottsdale Field CCity Table Copy of Customers Delete Where Criteria Scottsdale or 110 EXERCISE ACTION QUERIES DELETE 12 On the Query Tools Design tab in the Results group click the Run button to make the changes in the database Access displays a warning message that data will be deleted from the table and that the operation cannot be undone Click Yes Microsoft Office Access EJ You are about to delete 7 row s from the specified table I amp Once you dick Yes you can t use the Undo command to reverse the changes Are you sure you want to delete the selected records Show Help gt gt 13 Click the Close button the query window Click No to save the changes 14 In the Navigation pane double click the Copy of Customers table to open it 15 Examine the data Notice that the 7 Scottsdale records have been deleted Again this is a permanent deletion 16 Click on the Close button on the Copy of Customers table 17 In the Navigation pane right click the Copy of Customers table On the pull down menu left click Delete Click Yes to delete the table from the databa
112. elated table query A subform is a form that 1s inserted in another form called the main or primary form In a one to many relationship the main form shows the one side of the relationship while the subform displays the many side Access creates two forms one for the main form and one for the subform The main form and subform are linked so that the subform displays only records that are related to the current record in the main form For example when the main form displays employee information the subform displays only the transactions for that employee To create the subform all relationships must be established first This allows Access to automatically create the links between the main form and subform When identifying the tables that will be used in the main form subform there does not need to be a direct one to many relationship between the tables Access will create the subform with tables that have a many to many relationship utilizing the modified design of one to many relationships that were created to resolve the many to many relationship issue For example a customer can purchase many book titles and a book title can be purchased by many customers In creating the main form subform Access utilizes the intersection table used to form the one to many relationships Intersection Tables ustomers d ce z CFName CLName CAddress CCity CState CZip CPhone D V ISBN Title Author QtyOH Price
113. election of a field to complex conditional statements or calculations Access includes the Simple Query Wizard to assist in creating an uncomplicated query As you create the query Access generates a series of instructions to find and retrieve the desired data When the data is found it is displayed in Datasheet view When you save the query for future viewing Access does not save the Datasheet view of the results Access saves the instructions to retrieve the data This ensures the most current view of the data at any given time The instructions are displayed in SQL view Because the result of a query is a table queries can be queried Access retrieves the queried data first and then will retrieve data based on those results On the Create tab in the Other group there are two options for creating a new query the Query Wizard and Query Design There are four options included in the Wizard Query Macro Query Allows You To Simple Select fields from multiple tables and queries Does not allow specific criteria for the selection Crosstab Calculate totals such as sum average and count and then group them Find Duplicates Find duplicate field values Find Unmatched Find records in a table with no related records in another table for example customers with no transactions 91 EXERCISE QUERY WIZARD SIMPLE The Simple Query Wizard is the easiest way to create a query However it is
114. en 2 Onthe Design tab in the Relationships group click the Show Table button 3 The Show Table window opens On the Tables tab select Transactions shift Transaction Details to select both tables and click the Add button Click Close 4 Both tables are displayed in the Relationship layout area Click and drag the edges of the tables to expand them if necessary to view all field names 5 The common field in both tables is T Position the mouse pointer over 77 in the Transactions table list box hold down the left mouse button drag the pointer to T in the ee E Transaction Details table list box and release pene MO eek sal Create the mouse button The Edit Relationships dialog box appears BE e re Gemeen Join Type Note the One to Many relationship type Bi Enforce Referent integrity Create New Access determined the relationship type based Cascade Update Related Fields on the common field that was used to join the Cascade Delete Related Records tables 7 in the Transaction Details table is a foreign key Relationship Type One To Many e 6 Click the Enforce Referential Integrity box Click Create Note 7 is not the primary key of the Transaction Details table In this relationship it is the foreign key that relates to the primary key of the Transactions table The infinity symbol o shows the many side of the relationship For each transaction there may b
115. ent language Training emos Help information is available without internet access although fewer resources display help topics when using offline help 171 APPENDIX B DATABASE TEMPLATES Access provides database templates that can be used to create new database files The templates are complete applications that contain predefined tables queries forms reports and relationships They are designed so that you can create a new database quickly however you can make modifications to suit your personal needs The Getting Started with Microsoft Office Access welcome screen features a number of online templates Getting Started with Microsoft Office Access Featured Online Templates B Ax Assets Contacts Issues Events Lei a i PY Ns E vo ZE A f 3 Marketing projects Projects Sales pipeline Tasks F3 Double click Contacts in the Featured Online Templates section in the center pane 2 Click the browse button next to the file name text box in the right pane 3 Atthe File New Database dialog box navigate to the Access 07 Class folder add your initials to the file name and click OK 4 Click the Download button Access downloads the database template from Microsoft Office Online and displays the Contact List form along with the Access Help window 5 Close the Help window and click the shutter bar to open the Navigation pane Navigate To Category Contacts Navigation
116. es Pavlova Ltd 24 355n Rh nbr u Klosterbier Beverages Plutzer Lebensmittelgrofm rkte AG 24 0 51 Guarana Fantastica Beverages Refrescos Americanas LTDA 12 355n Aniseed Syrup Condiments Exotic Liquids 12 550n Sirop d rable Condiments For ts d rables 24 500 n Grandma s Boysenberry Spread Condiments Grandma Kelly s Homestead 12 80zj Northwoods Cranberry Sauce Condiments Grandma Kelly s Homestead 12 1202 LGula Malacca Condiments Leka Trading 20 2kegH 21 22 APPLYING AND REMOVING FILTERS Viewing Subsets Database tables store large quantities of data You may need to see of Data only a portion of that data for example one state or one city or sales from one customer Using the Find option to manually check through an entire table is tedious at best and not very efficient 4 Using the filter feature you can display an entire subset of data us from your table The records that do not meet the filter criteria are temporarily hidden from view You can apply a filter to data that has been filtered multiple filters can be applied to a table at the same time Filtering Options The Sort amp Filter group on the Home tab in the ribbon has three options the Selection drop down list the Advanced drop down list and the Toggle Filter button Option Description Allows you to filter items depending on the value of the selected item If you are filtering by a Name field the options list includes selecti
117. essions can be created to auto fill the current date or to leave a field blank Today s Date A common default value expression is today s date in the date Default field In order to have the current date auto fill into a date field type the following expression into the date field s Default Value property Date Steps to Add j Default Value Open the table in Design view Properties Place your cursor in the data type column of the field that requires the Default Value Inthe Field Properties pane click the Default Value property field box Click the Build button Lal at the end of the box The Expression Builder opens Create your expression using functions constants or operators When finished click OK Create a default value directly by clicking the Default Value field and entering a value or expression Save your design changes 59 EXERCISE ADD A DEFAULT VALUE 1 The Customers table is open in Design view If it is not open it now Books4U customers reside in the state of Arizona To ease entry of data into this field a default value will be defined 2 Place the cursor in the data type column of the CState field 3 Click the Default Value box in the Field Properties pane Type AZ Save the table Type the data in uppercase if that is how you will be using it Books4U also would like to ease the data entry for transactions The date of the transaction will always be today s date Rather
118. ext to the Transactions table Double click the TDate field to add it to the subform Close the field list pane To modify the form further logo format controls etc click to Design view On the Design tab in the Controls group a larger number of available controls are displayed 20 Change the effect of the text box controls that display employee information Click the E text box control While holding down the Shift key click the ELName and Title text box controls to select them 21 In the Controls group click the Special Effects button At the pull down menu click P gr p shadowed Click to Form e i d ab Aa be a a r a gt E specatetect Fat view If there is no change 0 Bl toe BRE O ig special Effect Raised using the effects you may choose a different style In Design view on the Arrange tab click AutoFormat Form Design Tools Books4U Database Access 2007 Microsoft Access Design Arrange Special Effect Sunken Special Effect Etched Special Effect Shadowed Special Effect Chiseled iE au n E 526407823 J ELName Bullock j Title Cashier Books Sold P isBN Title TDate Price 766825361 Leadership 31 Jan 08 17 98 __ 766825361 Leadership 08 Feb 08 17 98 1559276126 Think and Grow Rich 05 Feb 08 18 00 1557423601 Asa Man Thinketh 05 Feb 08 9 95 785288376 The 21 Irrefutable Laws of Leadership 31 Jan 08 24 99 446394599 Lincoln on Leadership 08 Fe
119. ey must never be blank for any record Relationships Because data is kept in its simplest form to reduce redundancy it is necessary to combine data from several tables when creating reports or requests for information Setting up the method for combining data in different tables through relationships ensures the accuracy and integrity of your information Similar Records All records belonging to a table takes on the same format size and field names Too much missing information in a record suggests that the data may belong in a different table Allowable Values Each field has a set of allowable values ensuring the validity of the data Design Process The database design process consists of several steps 1 Determine the purpose of your database 2 Gather existing data and make a list of required fields 3 Arrange the data into tables 4 Use sample data to troubleshoot for possible problems such as reducing redundant data 5 Identify primary keys and specify foreign keys used to relate tables 6 Determine table relationships and revise the table design as necessary 37 DESIGNING THE DATABASE Go Team Example Books4U 1 Purpose 2 Gather Information Database design is very much a Team sport Because the database is a shared resource input from each person and department using the database is essential to ensure its completeness While data can be added after the init
120. fice Access window opens The Getting Started screen is divided into three sections The Template Categories section at the left is used to preview and download predefined templates In the center the New Blank Database section is used to start a new database The section at the right Open Recent Database is used to open an existing database file Click the More hyperlink in the Open Recent Database section Template Categories no Getting Started with Microsoft Office Access Local Templates From Microsoft Office Online New Blank Database I Open Recent Database Business Education ES More Personal Blank Database G Kies 2007 Sample Featured Online Templates Ki Users Registration 07 1 3 2008 V G Users ManytoMany 2007 D A 1 3 2008 E LI Users moserk02 ManytoMany Assets Contacts Issues Events C8 Office Online amp SES What s new in Access 2007 ZS Ei Usel GG Wsers Worthwind 2007 P i 22 geet rm Ten Learnt more about uie new reatures ana Lei vu tia improvements k Get the latest content while working in the 2007 Microsoft Office system 9 Guide to Access 2007 User Interface Organize all your objects using the new easy access Navigation Pane More on Office Online Training Templates Downloads T EXERCISE STARTING ACCESS l 2 Open the Northwind da
121. g the Date control Click the bottom and right orange borders and drag to shorten You can change the font size of the title and fit the control box to accommodate it oft 13 Save the report We will now add the totals to the report EXERCISE ADDING TOTALS TO A REPORT IN DESIGN VIEW 145 Access 2007 permits the calculation of a sum average count or other aggregates for a field A grand total is added to the end of the report and group totals are added to any groups that exist on the report Report Design Tools P d ata Database Tools Design Arrange 1 Click the Design View button on the Status bar in the lower right p Es BR E ei corner of the screen Conditiona MERERI Te Sum ub S x E d Average N 2 Click the Total Price text box in the Detail section to select it Count Records Count Values On the Design tab in the Grouping amp Totals group click the ES Totals pull down menu and select Sum Min Standard Deviation Variance 3 Access adds a calculated text box control to the Report Footer section which creates a grand total Access also adds group footer sections to Transaction and Customer groups and places a total in each footer group Report Header Page Header Books4U Customer Sales Repart CLName Header CLName CLName E H T Header ISBN Qty Price Total Price Detail ISBN ory Pri
122. g the change Use caution when changing data types of populated fields Add a new field Place your cursor in the first empty row at the bottom of the field list and type the new field information Insert a field Create a blank row by placing the cursor in the row below the insertion field and clicking on the nsert Rows button in the Tools group on the Design tab Move a field Select a row by clicking on the record indicator the gray shaded bar at the far left of the row and releasing the mouse Then click and hold the mouse pointer on the record indicator to display a solid line above the selected record Drag the field to the desired position Delete a field Select the row by clicking the record indicator Press the delete key Pay close attention to the warning message 31 EXERCISE EDITING IN DESIGN VIEW 1 Select the Customers table and click on the M A Design button 2 Change the PostalCode field name to ZipCode a 3 Change the Phone Data Type to Number Save Microsoft Office Access x Some data may be lost i y The setting for the FieldSize property of one or more fields has been changed to a shorter size If data is lost validation rules may be violated as a result Do you want to continue anyway mm 4 You are attempting to change the structure of your database Weigh the consequences carefully before committing the changes Click No in th
123. gether as needed In the above example transaction is used to relate transactions and detailed items Notice that in the transaction table customer information is represented by only customer If we know the customer we can go to the Customer table and retrieve that entire customer s information To make this relationship complete add customer to the Customer table 41 EXERCISE UNDERSTANDING RELATIONSHIPS Customers EmpBenefits Dependents DeptLookup DFName Title Relationship Copy of Shippers Shippers SE SName SAddress SCity SState SZip SPhone SFax SContact Vendors Contact VURL PurchaseD Publishers P PName PAddress PCity PState PZip PPhone PFax PContact Once you have determined the table relationships test your design for any problems that may occur in retrieving the data How do you navigate through the database to answer the following questions 1 What is the name of the customer who purchased The Seeker 2 On July 15 2007 who sold the most copies of Harry Potter and the Deathly Hallows What is their current salary as we need to give them a raise 3 That book shipment that came in today was damaged Find out who shipped it and get the contact info so that we can file a claim Let the vendor know which books were damaged 42 For the remainder of this workshop we will develop the outlined tables abov
124. h displays a list of values CFName Text you can choose from How do you want your lookup column to get CLName Text its values CAddress Text CCity E m rent the lookup column to look up the values in a table or CState Text CZip Memo 9 I will type in the values that I want CPhone Number Date Time Currency AutoNumber Yes No OLE Object Hyperlink Attachment umm Mex 2 Click the radio button 7 will type in the values that I want Click Next What values do you want to see in your lookup column Enter the number of columns you want in the list and then type the values you want in each cell To adjust the width of a column drag its right edge to the width you want or double dick the right edge of the column heading to get the best fit Number of columns 1 Coli Phoenix Scottsdale Glendale Mesa Tempe Chandler Cancel lt Back Next gt Finish 3 Type the six major cities of the Phoenix valley as shown Use the Tab key to advance to the next entry If you press Enter before all of your entries are typed click the Back button When you are finished with your entry click Next 4 Youcan make a change to the name of the lookup column Click Finish You are returned to Design view The data type for your lookup field is displayed as Text 5 Save the table 64 EXERCISE CREATING A LOOKUP FIELD 6 Click the Lookup Tab for the CCity field in the Field Prope
125. h tables Position the mouse z cancel pointer over EZ in the Employees table list box e i employeeid Join Type hold down the left mouse button drag the pointer CH ees to Employeeid in the Emails table list box and Enforce Referential Integrity en Crate News release the mouse button The Edit Relationships Cascade Update Related Fields dialog box appears Cascade Delete Related Records Relationship Type One To One G Note the One to One relationship type Access determined the relationship type based on the common field that was used to join the tables EZ in the Employees table is related to employeeid in the Emails table 6 Click the Enforce Referential Integrity box Click Create Save the relationship Employees y Ee EFName ELName EMiddle BDate HDate Title For each employee there is one and only one email address Each email address belongs to one and only one employee Emails Y employeeid email 78 EXERCISE CREATING ONE TO MANY RELATIONSHIPS In a one to many relationship for each record in the first table primary table there will be one or more related records in the second table related table The common data field will be the primary key in the primary table and a non primary key field or foreign key in the related field This relationship is found most often in Access databases 1 The relationship layout area is op
126. how Table button ed The Show Table window opens On the Tables tab select Books lt shift gt Customers to select both tables and click the Add button Click Close LA 4 Both tables are displayed in the Relationship layout area Click and drag the edges of the tables to expand them if necessary to view all field names Reposition the table layout following the handout 5 Move the Customers table list box to the left of the Transactions table list box These two tables have CZ in common 6 Position the mouse pointer over CZ in the Customers table list box hold down the left mouse button drag the pointer to C in the Transactions table list box and release the mouse button The Edit Relationships dialog box appears Edit Relationships PJE Table Query Related Table Query Cancel CS D CS aa eg Join Type New V Enforce Referential Integrity Create New Cascade Update Related Fields F Cascade Delete Related Records Relationship Type One To Many 7 Click the Enforce Referential Integrity box Click Create 8 The Books table can be related to Customers through the Transaction Details table The Books table and the Transaction Details table have ISBN in common 83 EXERCISE CREATING MANY TO MANY RELATIONSHIPS 10 11 12 13 14 15 16 17 Move the Books table list box to the right and below the Transaction Details table list box These two
127. ial design is complete it is much harder to modify your database structure Depending on the modifications it may be easier to destroy your design and start over In the pages that follow we will be using an example of a bookstore s database to illustrate the concepts described here Picture in your mind any bookstore that you frequent It could be the bookstore in the mall or the college bookstore on campus Close your eyes and look around at the items contained within Bookstores today sell much more than just books CDs DVDs magazines games stationary cards pens and don t forget the coffee bar in the corner To keep the application from getting too complex our bookstore Books4U supplies only books Our task is to design a database to capture all relevant data for our business A statement that defines the scope of our database will help to guide its design It will imply the general kinds of data to be included as well as who the users will be Specific queries forms or reports are not stated The Books4U database will keep information on the books we sell the customers we sell to the vendors we purchase from the shippers we use to deliver book purchases by mail and the employees of our store We will need to track sales for quarterly and yearly financial records All employees will have access to book information Management will use data to calculate yearly sales earnings Start with existing information keeping in min
128. il merge process see Office Word 2007 Help Select the table or query to be merged with the form letter On the External Data tab in the Export group click More and then select Merge it with Microsoft Office Word The Microsoft Word Mail Merge Wizard opens Choose to create a new document or link to an existing document and click OK The document opens Follow the steps of the wizard On the Mailings tab in the Write amp Insert Fields group click the Insert Merge Fields button to insert Access fields into the document To complete the mail merge click to step 6 of the process To personalize your letters click Edit individual letters in the Wizard pane Save or print the form letters Click the Microsoft Word Close box 155 EXERCISE ACCESS TABLE AS A DATA SOURCE We will start the Mail Merge Wizard from Access and create a direct link between a table and an existing MS Word document to generate a sales promotion letter for all Books4U customers Jet Word a cl AJ S S Cyan H 3 i p Excel SES Ep mores SE E 1 Click the Customers table in the Navigation pane to select it PEERETS We Expor selected object to an Access as the source data T ume mmm XML File oa Export selected object to an XML file 2 On the External Data tab in the Export group click More and E C I x then select Merge it with Microsoft Office Word The EE Microsoft Mail Merge Wizard opens HTM
129. ilbert is a new city that has just opened up for us Modify the lookup field in Datasheet view to include this city 1 The Customers table is open 2 Toggle to Design View Click the Lookup Tab for the CCity field in the Field Properties pane Field Properties General Lookup Display Control Combo Box Row Source Type Value List Row Source Phoenix Scottsdale Glendale Mesa Tempe Chandler Bound Column 1 Column Count 1 Column Heads No Column Widths 1 List Rows 16 List Width 1 Limit To List Yes Allow Multiple Values No Allow Value List Edits Yes List Items Edit Form Show Only Row Source V No 3 Change the Allow Value List Edits property to Yes Save the Customers table 4 Toggle to Datasheet view 5 Right click any record in the CCity column and then click Edit List Items on the shortcut menu cut a Copy i Paste 2 sotAtoz Z sortZtoa Totem d Edit List Items ext Filters Type each item on a separate line Equals Glendale Phoenix Does Not Equal Glendale Scottsdale Contains Glendal Glendale Contains Glendale Mesa Does Not Contain Glendale Tempe Chandler 12 Edit List Items N Gilbert 6 Add Gilbert to the bottom of the list and then click OK Default Value OK Cancel 66 IMPORT DATA FROM AN EXTERNAL SOURCE Back to Now that the table structures have been defined and data types Datasheet View assigned in Design view toggle to D
130. information is GroupHeader x printed Click Before Selection Format Data Event Other AN Name GroupHeader 5 Close the Property Sheet Visible Yes e 2 j Height 0 4063 6 Click the Print Preview button on the Status bar Back color sFFFFFF in the lower right corner of the screen Alternate Back Color FFFFFF Special Effect Flat 7 Advance to the next page using the Record Auto Height No Navigator bar an TOW Can Shrink No k Display Wh Always 8 Customer information is no longer split across sispay tenen nway Keep Together Yes multiple pages Repeat Section No Force New Page one 9 Save the report New Row Or Col On Click Before Section On Format After Section L On Dbl Click Before amp After On Mouse Down On Mouse Up On Mouse Move On Paint On Print On Retreat Ta 148 EXERCISE ADD A CUSTOM FIELD TO A REPORT There may be situations where you need to calculate values during report execution and display the calculated values as an additional field in the report In this exercise we will add a custom field to a report that will display the result of a calculation The procedure outlined in this exercise can be used to add any field to a report Ana Rivera has decided that she not only wants a dollar total for each transaction she wants also a total number of books purchased for each transaction 1 Click the Design View button on the Status bar in the lower right corner of the scr
131. ing patterns For example the pattern for telephone number 602 255 5545 can be changed to 602 255 5545 You can also add new patterns Open the table in Design view Place your cursor in the data type column of the field that requires the Input Mask In the Field Properties pane click the Input Mask property field box Click the Build button Lz at the end of the box The Input Mask Wizard opens Select one of the predefined Masks Click the Try It field Type in a value for the field to examine how the data is displayed To edit a Mask select the Mask and click the Edit List button eau The Customize Input Mask Wizard window opens Edit the Input Mask field and click the Close button Click the Finish button The Input Mask Wizard closes Save your design changes 57 EXERCISE INPUT MASK 1 Open the Customers table in Design view The finance manager noticed that we did not include the customer s phone number in the table Add CPhone to the end of the field list as shown below Tab to include the data type 2 Save the Customers table Transaction Details Transactions Cutomers Field Name Data Type Description V ice Text Arbitrary number given to the customer Starts with the letter C CFName Text First Name CLName Text Last Name CAddress Text CCity Text CState Text CZip Text CPhone Text Which input mask matches how you want data to look
132. ing the tables together to retrieve the required data creates the relationship Related tables are linked through a common field Depending on how the tables are related will determine the type of relationship required Note The linking field may not be the only field that the two tables have in common To avoid confusion give the linking field the same name in both tables When creating relationships one table is typically the primary table with the second being the related table When the tables are joined based on a common field between the two Access ensures the integrity of the data For example Order information related table for a Customer primary table will not be allowed into the database unless the customer exists In other words only customers in your database are allowed to have orders Trying to find or reference a non existing customer for an order would create an orphan order Referential integrity is used to prevent orphans and keep references in sync so that the above situation never occurs Relationship Description One to one The common data appears only once in both tables One to many The common data appears once in the primary table but many times in the related table Many to many The common data can appear many times in both tables A third table must be created to reduce the redundancy of the data and to create two one to many relationships 77 EXERCISE CREATIN
133. is less than 3 display the text in bold red color This will alert the user that the book needs to be reordered Click OK Conditional Formatting Default Formatting This format will be used if no conditions are met AaBbCcYyZz Condition 1 Field Value Is E less than E TERE Lon uo QF l Add gt gt Delete OK Cancel 6 Click through the records on the record indicator bar to identify the books that need to be reordered 7 Savethe form Close the form 135 NOTES 136 REPORTS Summarizing Data Record Source Report Views When making decisions we can summarize the data that is viewed and manipulated on forms by generating reports Like forms Access 2007 offers several types of reports A report contains information that is pulled from tables or queries as well as information that is stored with the report design such as labels headings and graphics The tables or queries that provide the underlying data are the report s record source If the fields that you want to include all exist in a single table use that table as the record source If the fields are contained in more than one table use one or more queries as the record source Those queries may already exist in your database or you may need to create new queries specifically to fit the needs of your report Access provides several views to edit and modify reports Design Report and Layout views operat
134. it to the new position 8 To change the form s style click the More button located at the bottom of the vertical scroll bar in the AutoFormat group in the Form Layout Tools Format tab The drop down style gallery is displayed AutoFormat 9 Click the Office style button 1 column Ae row at the drop down list 10 Click the Title control object and drag it to reposition it as shown 11 Click the Logo image Drag the right bottom borders to resize the image Click ina blank area of the form to deselect the logo control object Books4U Shippers Form S10 sName State Handlers Phone 480 555 2525 tact Monica Jones 12 Click the Save button on the Quick Access Toolbar Name the form Shippers Form Close the form 123 EXERCISE CREATE A FORM FORM WIZARD The Form Wizard provides more choices for the form s design The user is guided through a series of dialog boxes to generate the form 1 On the Create tab in the Forms group click the More Forms drop down button and click Form Wizard 2 In the Form Wizard dialog box click the down arrow to the right of the Tables Queries list box and click the Books table to select it Which fields do you want on your form You can choose from more than one table or query Tables Queries Table Books X Available Fields Selected Fields a lt lt Cancel Einish 3 Click the Add All Fields button to mov
135. izard fee This wizard creates standard labels or custom labels What label size would you like Product number Dimensions Number across Unit of Measure Label Type English Metric Sheet feed Continuous Filter by manufacturer Avery Le E 3 Choose label size 5383 In the Filter by manufacturer text box use the pull down to select Avery 4 Select English in the Unit of Measure field and Sheet feed in the Label Type field Click Next What font and color would you like your text to be Text appearance Font name Font size Book Antiqua 10 ws Font weight Light Text color Bi Italic 5 At the Font name text box use the pull down Z and select Book Antigua Change font size to 70 Click Next Choose the fields you want on your labels from the Available Fields list and double click them to add to the Prototype label box Create your address label as you would a regular address with spaces and commas 160 EXERCISE CREATE MAILING LABELS 6 From the Available fields list double click CFName In the Prototype label window press the space bar one time Double click CLname Press the Enter key 7 Double click CAddress Press the Enter key 8 Double click Ccity type a comma and press the space bar one time Double click CState Press the space bar twice Double click CZip 9 Click Next What would you like on your mailing label gt Construct y
136. k 8 Click on Find Next Access selects Margaret Peacock 9 Click on Find Next Note the current record text box on the Record Navigation Bar 10 Click the Cancel button 17 REPLACE OR EDIT DATA Find and Replace Sac Replace Replace or Replace All Steps to Replace Data Use the Replace tab in the Find and Replace dialog box to automatically change a field entry using the steps in the previous section Click the Replace Button in the Find Group on the Home tab of the ribbon Find and Replace aes Find Replace Find What Sales Associate v Find Next_ Replace With Sales Representative SE Look In Employees v Replace Match Any Part of Field v j Search Al Le Replace Ail Match Case Follow the steps in the previous section to find the first occurrence of the data you wish to change In the Replace With field enter the data change Use the options for a more specific search The Replace and Replace All buttons allow you to replace one entry at a time or all occurrences at once When making changes to a record directly in the table a pencil icon will appear in the record selector bar blank column at the left edge of the datasheet The pencil icon indicates that the current record is being edited and the changes have not yet been saved Changes to data will be saved when you move to another record in the table or click the Enter key on the keyboard 2 4 R
137. k Show to display the field Save and name the new query Toggle to Datasheet view 95 EXERCISE SELECT QUERIES ADDING CRITERIA Create an alphabetical listing of all books published by Wildside Press Display the ISBN Title Author and Publisher Name Close all database objects 2 In the ribbon on the Create tab in the Other group click the Query Design button The Show Table dialog box opens we used this dialog box to create our relationship diagram 3 Select the tables or queries where the required data resides ISBN Title and Author are in the Books table Publisher Name is in the Publishers table Select the Books table press and hold the Ctrl key and select the Publishers table Release the Ctrl key Click Add Click Close The top portion of the query window shows the table field lists and the relationships between the tables The bottom portion or design grid represents the columns in the query results that will be displayed in datasheet view Place the field names on the grid in Show Table LS Iess Tables Queries Both Books Customers Emails Employees Publishers Shippers Transaction Details Transactions Add Close the order that you want the fields displayed in the query results Double clicking the field name in the table field list adds the field to the grid columns Double click the SBN field in the Books field list Double click Title and then doubl
138. l maintain a link to the source data Changes made to the data in Access will be reflected in the source and vice versa NOTE If the source database requires a password the password will be stored with the linked table OK l Cancel 74 EXERCISE IMPORTING FROM AN ACCESS DATABASE 10 11 I2 The Import Objects Wizard opens Across the top are the objects tabs We can import all objects in the same import operation by clicking each tab and selecting the desired object Click the Tables tab In the window below the object tabs click the Employees and Emails tables to select them On the right hand side of the wizard click the Options gt gt button to view specifics of the import operation Because we are importing only the Employees table all boxes in the Import group are unchecked We want to import both the data and the data definition of the Employees and Emails tables In the mport Tables group choose Definition and Data Click OK Import Objects P E Tables Queries Forms Reports Macros Modules Emails OK Employees Cancel Select All Deselect All Import Import Tables Import Queries Relationships Definition and Data As Queries E Menus and Toolbars Definition Only As Tables E Import Export Specs C Nav Pane Groups Do not save the import steps Click Close Open the Employees table in Datasheet view to examine the data To toggle to Design view to check formatti
139. le by adding records in the Datasheet View a new feature in Access 2007 automatically creates a field named ID that is defined as the primary key The data type assigned to ID is the AutoNumber type assigning a sequential number with each new record Primary Key To create a Primary key field for a table place your cursor Button anywhere in the field row and click the Primary Key button in the Tools group on the Design tab Access places a key icon to the left of the Field Name to designate that field as the Key field Field Name dateregistered employeeid __ firstname lastname college Steps to Assign a P Primary Key While in Design View place your cursor anywhere in the field row that you have determined to be the Primary Key Click on the Primary Key button in the Tools group on the Design tab Click the Save button LI on the Quick Access bar 53 EXERCISE CREATING A PRIMARY KEY 1 Open the Customers table in Design View 2 Place your cursor anywhere in the C7 field row On the Design tab of the ribbon in the Tools group click the Primary Key button 3 M Table Tools External Data Database Tools Design a Insert Rows Kgl E Delete Rows E uM Primary E t Validat Property Indexes Key ad Lookup Column Sheet Show Hide 4 Save the Customers table If you are prompted for a Table name to save the table type Customers Click OK Do not close the Custome
140. lect Do This Data in a field Drag across the data to select An entire field Click the left edge of the field where the pointer changes to a white plus pointer Adjacent fields Drag from the left edge of a field across the fields that you want to select A column Click the column header Adjacent columns Drag from the field name at the top of the column across the columns that you want to select A record Click the record selector of a row Multiple records Drag from the record selector of the first record across the records that you want to select All records Click the down arrow on the Select button in the Find group on the Home tab and then click Select All 27 EXERCISE EDITING IN DATASHEET VIEW Open the Employees table 2 Click the Microsoft Office button and then point to Print The Print dialog box appears Click Print Cs Ha pe Northwind 2007 Database Access 2007 Microsoft Access Print Preview Add Ins D G t SL a Size Portrait Landscape Man d v Preview Columns 3 The mouse pointer is a zoom tool Click to magnify the image 4 Click the Next Page button located on the Navigator bar to view all pages Note that the Employees table is split across multiple pages with the default margins and orientation Change the format and orientation so that the datasheet will fit on one page 5 C
141. lect the title Click the title again to display the control borders Resize the title control by dragging the left and bottom borders until the title is displayed on one line Use the Font group to change the type bold and size font properties It is not necessary to highlight the text only to select the title control box 4 Reposition the title on the form Position the mouse pointer over the control until the pointer turns into a four headed arrow Click and drag the control to the center of the header section Click a blank area of the form to deselect title control 5 To add the logo click the Logo button in the Controls group on the ribbon At the ba Insert Picture dialog box navigate to the Access 07 Class folder on the Desktop Double click MCCCDlogo Drag the right bottom borders to resize the image Click a blank area of the form to deselect the logo control Logo 6 To add a date to the form click the Date amp Time button in the Controls group on the ribbon 7 In the Date and Time dialog box check the box to include date uncheck the box to include time Click the radio button of the second date format Click OK 8 Access places the date in the upper right corner of the form Click the date control and use the Font group to change the color and bold font properties Click a blank area of the form to deselect the date control 9 Click the Form View button on the Status bar in the lower right corner of the screen AYA
142. lick the Relationships button in the Show Hide group The Design tab is activated On the Design tab in the Tools group click the Relationship Report button After making modifications to the relationship diagram save the diagram Pa id m1 Rd Relationship Tools 27 Home Create External Data Database Tools Design a X Clear Layout Ki E amp Hide Table l a Relations c 34 Direct Relationships wg Show as Close Table 38 All Relationships Relationships 23 The report format opens and the Print Preview tab is active In the Page Layout group change print orientation to Landscape to fit the diagram to the page The Margins button allows you to modify the layout further if needed Click the Print button 24 Close the relationship window NOTES CASCADE OPTIONS Referential Integrity Orphans Cascade Update and Delete When creating relationships we have the option to enforce referential integrity This ensures data validity across tables by preventing orphan records and by keeping references synchronized so that there is no data that points to non existent records In our Books4U database there is a one to many relationship stated between the Customers table and the Transactions table Before we can add a transaction record the customer referred to in CZ must exist in the Customers table Similarly we cannot delete a customer record if there are related transactions for that customer it would cause
143. lick the Landscape button in the Page Layout group of the Print Preview tab While the Landscape orientation allows more columns to fit on a page it still requires 3 pages to fit the datasheet Another method to fit more text on a page is to reduce the margins Normal Top 0 757 Bottom 0 75 Left 0 35 Right 0 35 Wide 6 Click the Margins button in the Page Layout group The drop LES ZE 07s zz Right O75 down list shows the predefined margin options You can set your EE own custom margins if none of the predefined margin settings are LJ Ri 025 Right 0 25 appropriate for your data 7 Click outside the options list to remove it Click the Page Setup button in the Page Layout group The Page Setup dialog box appears On the Print Options tab change the left and right margin settings to 0 25 Click OK m Changing the margins did not allow the datasheet to Prnt Options Page fit on one page We can now format the datasheet to SE reduce the number of columns shown Bottom 1 Left 0 25 Right 0 25 Print Headings Split Form EXERCISE EDITING IN DATASHEET Cx Cem 28 VIEW 10 11 12 13 14 Close Print Preview by clicking the button in the Close Preview group You have decided that not all of the data on the datasheet needs to be printed By hiding columns they are temporarily unavailable for viewing On the
144. m Oa id 9 HL Home Create External Data d gt _ Text File oP WP E 59 XML File Saved Access Excel SharePoint a List ng More Imports Import 4 Inthe File Name box specify the Excel file that contains the data Use the Browse button to locate the Customer Data file Get External Data Excel Spreadsheet Select the source and destination of the data Specify the source of the data File name JC Desktop Access 07 Class Customer Data den in the Access 07 Specify how and where you want to store the data in the current database Class folder on your Import the source data into a new table in the current database If the specified table does not exist Access will create it If the specified table already exists Access might overwrite its contents Desktop with the imported data Changes made to the source data will not be reflected in the database Append a copy of the records to the table customers EI If the specified table exists Access will add the records to the table If the table does not exist Access will create it Changes made to the source data will not be reflected in the database Link to the data source by creating a linked table Access will create a table that will maintain a link to the source data in Excel Changes made to the source data in Excel will be reflected in the linked table However the source data cannot be changed from within Access
145. m of the Mail Merge pane to continue 156 EXERCISE ACCESS TABLE AS A DATA SOURCE 10 11 12 13 14 In step 4 we will create an Address Block and a Greeting Line for our letter Position the cursor after the date in the letter and press the Enter key once to advance the cursor to the next line We can place the address block in our letter in one of 2 ways mu PEE on the Mailings tab in the Write amp Insert Fields group click In order to use special features Mail Merge needs to know which fields in your recipient list match to the required fields Address Block Clicking the Address block in the Mail Mer ge Use the drop down list to select the appropriate recipient list field for each address field component pane will display the Insert Address Block dialog box EAE tock F First Name CFName ux Select a format to display the recipient s name Click the Geen Ge E Match Fields button The Match Fields dialog box opens Company rot matched s Address 1 CAddress m Address 2 not matched On the left side of the Match Fields dialog box the required d Citv CCitv City CCity ici He He T T names of the address block are listed Use the pull downs to w cmu e ostal Code match those names with the field names in the database table Country or Region not matched x Optional information Click OK Addresses from the database are displayed in the CCE Ped 1 b preview box Click OK Use the
146. matting style will also be used for the subform As you click through the choices on the right the wizard previews the style to the left Make a selection Click Next 10 On the last page of the wizard name the forms as shown below Click Finish What titles do you want for your forms Form Employees Subform Books sold by Employees That s all the information the wizard needs to create your rm Do you want to open the form or modify the form s design Open the form to view or enter information Modify the form s design 130 EXERCISE SUBFORMS ENHANCE THE APPEARANCE 11 The form opens in Form view To increase your viewing area of the Employee form click the shutter bar on the Navigation pane As you click the record navigation bar of the main form Employees the books sold by each employee is displayed in the subform In some cases the subform columns do not accommodate the data The subform also needs to be larger to include more records 12 Click the Layout View button on the Status bar in the lower right corner of the screen 13 Resize the columns of the subform to accommodate the data Click the record navigation bar of the main form to ensure that the column sizes will contain all column data 14 Employee record 6 has more records than those displayed on the current form To expand the subform click the subform to select it An orange border appears around the subform Position the mou
147. n the form 6 Savethe form Close the form Employee Book Sales ER ELName 528067894 Smith Title SalesPerson Books Sold ISBN Title M TDate Price H 789736136 How Computers Work 07 Feb 08 34 99 761140670 Never Too Late to Love a Computer 07 Feb 08 12 95 596527608 Access 2007 The Missing Manual 07 Feb 08 34 99 1558747524 The Power of Focus 31 Jan 08 14 95 1557423601 Asa Man Thinketh 31 Jan 08 9 95 446530697 Running With the Giants 30 Jan 08 13 99 055327757X The Greatest Salesman in the World 30 Jan 08 7 99 Record M 1of7 LA H Record M 4 6of6 LR 134 EXERCISE CONDITIONAL FORMATTING Using the conditional formatting feature you can set formatting options for a control based on a condition The conditions can be based on field values falling within a range or based on expressions 1 2 Click the Layout View button on the Status bar in the lower right corner of the screen 3 4 5 In the lower portion of the Conditional Formatting dialog box keep the default Open the Books Form Select the QtyOH text box control On the Format tab in the Font group click Conditional settings for the first text box Field Value Is In the second text box use the pull down and select less than In the third text box type the number 3 Apply the Bold font property Use the pull down to select the red text color The condition states that if the value of the QtyOH field
148. n our business For example ISBN is an identifier that describes Books Title also describes Books Birth Date describes Employees Order is part of the transaction process of purchasing a book The entities become the subjects of our tables The data items that describe the table entity become the fields of that table Keep field data as simple as possible to allow for sorting flexibility For example it becomes complex to request a sort on cities when city is part of an address field Keep the city data item separate to facilitate a more efficient search The same is true for the Name field Separate first name and last name to allow an alphabetical listing of customer s last name 39 DESIGNING THE DATABASE Include only those fields that describe the table For example credit card may mistakenly be placed in the Customers table when in fact it is part of the process of a Sales Transaction Books Customers Transactions Employees ISBN First Name Customer Name First Name Title Last Name Customer Address Last Name Author Address Date Birth Date QtyOH City Employee Name Hire Date Price State ISBN Department Publishers Zip Qty Title Price Total 4 Use Samples to Troubleshoot Problems The lists above show 4 possible table designs for our database The data in Books Customers and Employees follow design guidelines Each field describes the entity of the table the data items are in their simplest form and it
149. n the sketch are calculated fields We will create those directly on the report 141 EXERCISE CREATE A GROUP REPORT REPORT WIZARD 7 Click the Outline radio button on the layout choice and choose the Landscape page orientation Click Next 8 Click the Flow style Click Next 9 In the last window name the report Books4U Customer Sales Report Click Finish The report opens in Print Preview 10 Examine the data Close the Shutter Bar on the Navigation pane and use the Zoom button in the Zoom group to reduce the magnification of the report for better viewing Use the Record Navigator bar to advance to the next page of the report Note We cannot use an aggregate total in the query to obtain the customer total here If we did that in the query we would lose access to the details of each transaction Access would display a summary total for each customer For the Customer Sales Report we need to retain all transaction information CLName CLName Sweeny Total Price 519 90 554 00 Total Price Victors To105 Oty Total Price 56 99 514 95 57 99 We need to make a number of adjustments to our report to satisfy the requirements given to us by Ana Rivera the manager The report requires aggregate totals for each transaction and customer and a report total We must add the report title date and page number to all new pages Each customer must go on a new page Other cosmetic modifications can be d
150. n your sales promotion letter Click Next Preview your letters to continue 16 Click through the recipients in the Mail Merge pane to view the letter 17 Click Next Complete the merge All letters are created To print all letters click Print To edit specific letters Click Edit individual letters The Merge to New Document dialog box opens Click OK Merge to New Document Merge records All Current record From To OK Cancel 18 Using the vertical scroll bar scroll down to view all letters 19 Close the window Click No to save the changes Close MS Word Click No to save changes April 9 2008 JanetPenny 455 N Palm Glendale AZ 85466 Dear Janet This Saturday and Sunday only for our preferred customers Books4U will have a Customer Appreciation Day sale at all valley locations For two days only all in stock books are 40 off There will be a storyteller reading from different books throughout the weekend A schedule will follow Lots of gamesfor the kids will be provided along with balloons and a finger painting corner A number of celebrities will also be in attendance for meet and greet book signings throughout the 2 day event Be sure to stop by for free coffee and cake This sale kicks off Saturday morning at 6am and runs through Sunday evening 6pm Hope to see youthere AnaRivera Manager Books4U 20 MS Word closes and you are returned to the Access window 158
151. ng equals not equals contains or does not contain the value of the item selected Allows you to filter multiple fields in a form apply filters that are not commonly available in the filter list or save your filter as an actual query object in the database Used to move between the filtered and unfiltered view of data in the table Filters can be Filters can be saved as a query and stored as an object in the Saved database Steps to Save f the Filter as a Filter the records in the desired table Query Click the Office button and choose Save As to display the Save As dialog box In the Save As dialog box Name the query in the Save To textbox From the As drop down list select Query Click OK 23 EXERCISE FILTERING BY SELECTION 1 Click the Orders tab 2 Place your cursor on any record in the Customer field 3 Click on the Filter button on the ribbon Access provides a drop down list in the active column and includes in the filter list box each unique field value that exists P within the column Adjacent to each field value is a check box Clear the checked nm field uncheck the Select All checkbox Check the box labeled Chop suey Chinese When you click OK only those records with a matching field value are displayed All other records are temporarily hidden SortAtoZ E Orders Xni Order Customer Employee _ Order Dai Required Dat Shipped Dat Ship Via Cle
152. ng click the Home tab and then the View button on the ribbon Note that the Title field is a Look Up field E is set as the primary key field Open the Emails table to examine the data in both Datasheet and Design views Close the Employees and Emails tables 75 EXERCISE IMPORTING FROM AN ACCESS DATABASE To import a table from a previous version of Access follow the procedure on the preceding pages The Shippers table can be found in an Access 2003 database 13 14 15 16 17 18 19 20 21 On the External Data tab on the ribbon in the Import group click the Access button The Get External Data Access Database wizard opens In the File Name box specify the Access file that contains the data Use the Browse button to locate the Shippers Data 2003 file in the Access 07 Class folder on your Desktop To specify the data destination click the radio button to Import tables queries forms reports macros and modules into the current database Because we currently do not have a table created to accept this data Access will create the table Click OK When the Import Objects Wizard opens click the Tables tab In the window below the object tabs click the Shippers table to select it On the right hand side of the wizard click the Options button In the Import Tables group choose Definition and Data Click OK Do not save the import steps Click Close Open the Shippers table in Datasheet view
153. not possible to determine that this content came from a trustworthy source You should leave this content disabled unless the a e content provides critical functionality and you trust its source Navigation Pane More information File Path C Users moserk02 Documents Northwind 2007 accdb Help protect me from unknown content recommended Open the Trust Center Microsoft Office Window Frame Unlike other Microsoft applications only one database can be open at a time in Access DATABASE OBJECTS Navigation Pane The database contains a variety of objects used to enter retrieve Categories manage and view data The Navigation Pane on the left side of the Access User Interface organizes the database objects into categories and is used to open an object Tables are the basic building block of the database They contain information for related items and are broken down into individual units of information Data is stored in rows and columns Queries are instructions that extract specific information from one or more tables When you create and save a query Access stores the set of instructions needed to create the result of the query not the result itself This ensures that you receive the most current data each time you run the query Queries can also be used to perform calculations Query Wizards are available to ease query development for the novice database user Forms are a group of objects which are use
154. o accept this data Access will create the table Click OK 28 The Import Spreadsheet Wizard opens Click the Show Worksheets radio button and choose the worksheet that holds the desired data Select the Books worksheet Click Next 29 Make sure that you manually check the First Row Contains Column Headings box When we appended data to an existing table this action was automatically set for us Click Next 30 This window allows you to specify which fields you will be importing and permits a change to the field name Click on each field to select it Use the slider bar at the bottom of the window to view all fields If you do not wish to import a particular field check the Do not import field box We will be importing all fields and retain the existing field names Click Next You can specify information about each of the fields you are importing Select fields in the area below You can then modify field information in the Field Options area Field Options Field Name ISBN Data Type Text X Indexed No Do not import field Skip M Computer Basics EXERCISE IMPORTING FROM AN EXCEL SPREADSHEET 34 99 B62 71 31 32 33 34 35 36 37 38 In this window we define a primary key for our table From past discussions it is necessary to set a primary key to ensure that all records in the table are unique Itis never a good idea to allow Access to add the p
155. o our repeat customers are 1 In the ribbon on the Create tab in the Other group click the Query Wizard button Click Find Duplicates Query Wizard and then OK 2 Choose the table you want to search for duplicate values Repeating C in the Transactions table tells us that the customer has purchased from us more than once Click on the Transactions table and then Next 3 Choose the field that may contain duplicate information Under Available Fields double click CZ This action adds the fields to the Duplicate value Fields list Click Next 4 Double click any additional fields you would like to see in the query result Double click TDate Click Next Do you want the query to show fields in addition to those with duplicate values For example if you chose to look for duplicate City values you could choose CustomerName and Address here Available fields Additional query fields Cancel Back I Next gt Einish 5 Name the query Repeat Customers and click Finish 6 The result displays the C and date of each transaction for repeat customers Repeat Customers CS TDate x 627458965 07 Feb 08 627458965 31 Jan 08 658652654 11 Feb 08 658652654 05 Feb 08 7 Close the result window The result instructions are automatically saved 93 EXERCISE QUERY WIZARD FIND UNMATCHED Retrieve a list of employees who have not sold any books Include first and last name of the empl
156. ocius 95 Select Queries Adding Criteria esses 96 Select Queries Conditional And Or sess 98 Select Queries Calculations 2 5 2 5 2 rete bates guod 101 Action Queries In Design View 104 Action Queries Update seiiet ife itv tu eio v geo ibus Sege 105 TABLE OF CONTENTS Action Queries Make Table 107 Acton Queries Delete oe e atte 110 Calculating A PEIOEALOR geed 112 Calculating Aggregates On Tables eese 113 Calculating Aggregates In Queries esee 114 OUTS ERN NM HC On 116 Forms Creation Tools EE 117 Create A Form Form Tool iet gebe oer dd 118 Create A Form Split Form Tool eee 116 Create A Form Blank Form Tool eese 121 Create A Form Form Wizard uet asee eei centered ogaut 124 Create A Form Adding Controls eee 125 SUDIOLITIS eerste 124 Subforms Enhance The Appearance esssss 131 Control Layouts T M 133 Conditional Formatting ee e tette th eer S errans 135 BRS OP ES to ee 132 Create A Report Report Tool 138 Group Reports eene 139 CONTOS eegene 143 Adding Totals To A Report In Design View 145 Modifying The Report Property Sheet 148 Add A Custom Field To A Report 149 TABLE OF CONTENTS Apply An Autoformat To A Renort 151 Print A Reports i eee eU 153 Use Mswor
157. oducts E i ig Catalog a D Products by Category Reports D Sales by Category Pages D I sales by Category Subreport Macros Customers Modules Customers Table Invoices All Access Objects Invoices Filter Orders Qry Quarterly Orders Customer Orders Customer Phone List Customers a HM a p Cd C iy D Tabbed Document Window Viewing Vf FKI Alfreds Futterkiste Obere Str 57 City ege Bein Postal Code County Gemary xi a Fax Phone 030 0074321 030 0076545 EXPLORING AN EXISTING DATABASE Table Views Changing Views Steps to Switch Views Tables are the building blocks of the database and contain information describing the table Data is organized into rows and columns There are two primary views for using tables in Access Design View and Datasheet View Datasheet View displays the data in a manner similar to a spreadsheet with a grid of columns and rows and can be used to enter data In Design View the structure of the table is defined including names descriptions and data types of all fields describing the table The design view serves as the documentation for your database structure The View button on the Home tab allows you to switch back and forth between these two views You can click on the View button down arrow for a drop down menu of the two view choices or you can click on the button and it will toggle you back and forth between the two views De EI S Table Tools No
158. one later 142 CONTROLS Enhancing Controls are objects that display data perform actions and let you Reports view and work with information that enhances the user interface such as labels and images Controls can be selected sized aligned and moved We previously visited controls when creating forms Three Types The controls placed on a form or report are classified into three of Controls types depending on the type of data they are associated with These three types are summarized below Control Type Description Bound control The source of data is a field in a table or query Bound controls are used to display values from the database The values can be text dates numbers Yes No values images or graphs A Text Box is the most common type of bound control Unbound control A control that doesn t have a source of data Unbound controls are used to display information lines rectangles and images The Label controls are a type of unbound control Calculated control The source of data is an expression rather than a field An expression is a combination of operators control names field names functions that return a single value or constant values An expression can use data from a field in the report s record source or from a control on the report Control Source When you create a report it is most efficient to add and arrange all the bound controls first as they are created directly from the repor
159. ou were searching for Oak Street but were not sure of the exact address you could search the Address field for Oak which would retrieve every record with the word Oak somewhere in the address 1 Ata form or in a table in Datasheet View place the cursor in the field you are searching ES Click on the Find button fina in the ribbon The Find and Replace window displays Enter search criteria in the Find What field Click on the Find Next button to see the first matching record Click on the Find Next button to see the next matching record When there are no more matches a dialog box opens Click OK Cancel returns you to the database 16 EXERCISE FINDING RECORDS 1 Open the Orders table 2 You want to find all the orders placed by Employee Peacock however you don t know the first name Place your cursor in any record in the Employee field 3 Click on the Find button fins on the ribbon in the Find group Find and Replace Es Fnd Replace Find What Peacock Cancel Look In Orders Match Whole Field Search All F Match Case 4 Inthe Find What field type Peacock 5 Click on Find Next A message box tells you that there were no records found Microsoft Office Access E o Microsoft Office Access finished searching the records The search item was not found 6 Click OK 7 Inthe Find What field type Peacoc
160. ouble click the New Employees table We now want our New Employees table to be the original all subsequent queries will now be based on this table Currently we have queries in Books4U that are based on the old Employees table We need to change the names of the tables to reflect the change in data Close the New Employees table Right click the Employees table in the Navigation pane Select Rename from the drop down list Rename the table Old Employees Right click the New Employees table in the Navigation pane Select Rename from the drop down list Rename the table Employees Open the Employees table in Design View Set E as the primary key Save the table Close the Employees table Delete the Old Employees table Right click the table in the Navigation pane Select Delete Click Yes to destroy all relationships to the Old Employees table Check the relationship diagram On the Database Tools tab in the Show Hide group click the Relationships button The relationship between Employees and Transactions has been deleted We need to recreate it Position the mouse pointer over EZ in the Employees table list box hold down the left mouse button drag the pointer to E in the Transactions table list box and release the mouse button Click the Enforce Referential Integrity box Click Create Close the Employees amp Emails query Click Yes to save the changes Close the relationship layout Save the changes 109 EXERCISE ACT
161. our label on the right by choosing fields from the left You may also e type text that you would like to see on every label right onto the prototype Available fields Prototype label CAddress CFName CLName CCity CAddress CState CCity CState CZip a CZip Cancel i lt Back Next gt Finish 10 Sort the labels by double clicking the CLName field Click Next 11 In the last wizard window name the report Customer Labels Click Finish 12 The report opens in Print Preview layout Jed Andes Zeb Curry 25 N Smoketree 75 W Spruce Phoenix AZ 85264 Glendale AZ 85466 Wally Durham ValFrank 64 E Sycamore 92 N Magnolia Scottsdale AZ 85384 Phoenix AZ 85264 13 Save the report Close the report 161 NOTES 162 INTEGRATE PROGRAMS EXPORTING DATA Sharing with An advantage of a suite program like Microsoft Office is the Excel or Word ability to exchange data from one program to another Access offers a feature to export data to different file formats such as Excel or Word A copy of the data is created in an external source file and the original data is retained in the database Using the Export group on the External Data tab allows you to export either a table query or form to Excel If a table contains subdatasheets or a form contains subforms you must export each subdatasheet or subform to view them in Excel Access allows you to export a table query form or report
162. oyee hire date and title 1 In the ribbon on the Create tab in the Other group click the Query Wizard button Click Find Unmatched Query Wizard and then OK 2 Our data is coming from the Employees and Transactions tables In this window we identify Employees as the table which may have no related data Select the Employees table Click Next 3 Select Transactions The Transactions table contains the related data Click Next 4 The next window requests the linking field between the two tables Select E for both tables Click Next What piece of information is in both tables For example a Customers and an Orders table may both have a CustomerID field Matching fields may have different names Select the matching field in each table and then click the lt gt button Fields in Employees Fields in Transactions blw nim Matching fields ES lt gt ES Cancel Back Next gt Finish 5 Double click EFName ELName HDate and Title in the Available fields list to add the data to the Selected fields list These fields will be in the query result Click Next 6 Name the query Employees Not Selling Books Click Finish Employees Not Selling Books EFName ELName HDate Title Lori Jones 8 21 2005 Bookkeeper Harry Peters 9 5 2005 StockPerson 7 Close the result window The result instructions are automatically saved 94 SELECT QUERIES IN DESIGN VIEW
163. positioned Click the Title label control in the Report Header section to select it Position the mouse pointer inside the orange border of the title control until the pointer changes to a double arrow Click and drag the mouse pointer until the title is centered above the data on the report Release the mouse pointer 5 The Date control is located in the Page Footer section We need to move it up to the Report Header section Click the date control to select it Position the mouse pointer inside the orange border of the date control until the pointer changes to a double arrow Click and drag the mouse pointer through the various report sections until the date is above and to the right of the title in the Report Header section of the report Release the mouse pointer 6 The Page Number control must be centered on the page Click the page control in the Page Footer section to select it Position the mouse pointer inside the orange border of the page control until the pointer changes to a double arrow Click and drag the mouse pointer until the page number is centered Release the mouse pointer 144 EXERCISE ENHANCING REPORTS IN DESIGN VIEW 7 Click the Report View button on the Status bar in the lower right corner of the screen to view the changes Go back to Design View to make any modifications to the controls 8 Preview the report Click Print Preview on the Quick Access Tool bar or click the Office button roll the mouse over the p
164. r as needed After your tables are created they must be linked together This is established through a linking field or a common field between the tables Depending on how the tables are related will determine the type of relationship required When creating relationships one table is typically the primary table with the second being the related table When the tables are joined based on a common field between the two Access ensures the integrity of the data For example Order information related table for a Customer primary table will not be allowed into the database unless the customer exists In other words only customers in your database are allowed to have orders Trying to find or reference a non existing customer for an order would create an orphan order Referential integrity is used to prevent orphans and keep references in sync so that the above situation never occurs Access creates relationships based on the common field that was used to join the tables We will create relationships later Relationship Description One to one The common data appears only once in both tables One to many The common data appears once in the primary table but many times in the related table Many to many The common data can appear many times in both tables A third table must be created to reduce the redundancy of the data and to create two one to many relationships 33 EXERCISE VIEW RELATIONSHIPS 1
165. reates an OR condition 7 Click the Or row in the Title column Type Leadership The grid thus far shows that we are searching for Chandler customers who have purchased Think and Grow Rich and any customers who have purchased Leadership We must specify that the book Leadership was also purchased by Chandler customers 8 Click the Or row in the CCity column Type Chandler 98 EXERCISE SELECT QUERIES CONDITIONAL AND OR Books Customers Transactions 9 ISBN Transaction Details ce Title big CFName Author T ad CLName QtyOH ISBN TDate CAddress Price Qty ss CCity P Price Er CState CZip CPhone 4 Lu Field CFName CLName CPhone CCity Title TDate Price Table Customers Customers Customers Customers Books Transactions Transaction Details Sort Show v v vj vj vj vi vi Criteria Chandler Think And Grow Rich or Chandler Leadership 9 Toggle to Datasheet view Access displays the result of the query CFName CLName CPhone CCity kb Title M TDate H Price T Justin Sweeny 6028886542 Chandler Think and Grow Rich 11 Feb 08 18 00 Sara Hardy 6025587456 Chandler Leadership 08 Feb 08 17 98 Justin Sweeny 6028886542 Chandler Think and Grow Rich 05 Feb 08 18 00 When creating this query it is necessary to include the CCity field in the query grid to restrict the results to Chandler customers However as the results show CCi
166. riginal Employees table including the email field 7 Savethe query by clicking the Save button on the quick access toolbar Name the query Employees amp Emails Leave the query open in Design View We now want to replace the original Employees table with the query result that we just created As a query the result data is not stored only the instructions to re create the query are stored We want the results to be stored as the original table so we have to make our query a table 8 Inthe ribbon on the Query Tools Design tab in the Query Type group click the Make Table button The Make Table dialog box opens In the Table Name textbox name the new table New Employees Click OK Make Table aial EJ Make New Table Table Name New Employees Cancel Current Database Another Database 108 EXERCISE ACTION QUERIES MAKE TABLE 9 10 11 12 13 14 15 16 17 18 19 On the Query Tools Design tab in the Results group click the Run button Access displays a warning message that data will be inserted into a new table and that the operation cannot be undone Click Yes Microsoft Office Access k zd You are about to paste 6 row s into a new table Once you dick Yes you can t use the Undo command to reverse the changes Are you sure you want to create a new table with the selected records Yes No Examine the new table On the Navigation pane d
167. rimary key keep control of your data at all times For the Book data the ISBN number is a unique key that has already been provided through standard coding Click the middle radio button to choose the primary key Use the pull down menu to select the SBN number Click Next Microsoft Access recommends that you define a primary key for your new table A primary key is used to i c uniquely identify each record in your table It allows you to retrieve data more quickly E un von gan Sum vn van lt um vn van SI am vun van ME un wn gan Let Access add primary key Choose my own primary key ISBN EN Ss No primary key Ir The final window allows us to name our new table Type Books Click Finish Do not save the import steps Click Close Open the Books table While in Datasheet view resize the columns to display all data Notice that all columns except QtyOH and Price are in text format Check that QtyOH is in number format and Price is in currency format toggle to Design view by clicking on the Home Tab View button P is the Publisher number of the book Notice that P is repeated several times in the Books table To have access to the rest of the Publisher information we will import the data Follow the same procedure above to import the Publisher data Use the Browse button to locate the Book Data file in the Access 07 Class folder on your Desktop Select the Publishers worksheet
168. rint option and click print preview Advance to the next page using the Record Navigator bar We need to add title and date to the top of each page 9 Click the Design View button on the Status bar in the lower right corner of the screen Expand the Page Header section by positioning the mouse pointer between the Page Header and CLName Header sections until the pointer changes to a Page Header CLName Header S double arrow Click and drag downward to expand the Page tes et Header section 4 T Header 10 Click the Title control hold down the Shift key and click the Date control Both controls are selected Right click the mouse select copy Click in the Page Header section Right click the mouse and paste Reposition the Title and Date controls 11 As you preview the changes you will notice that the Title and Date appears twice on the first page This is because we left the controls in the Report Header section In Design View Report Header Section select the Title and Date controls Press the Delete key Collapse the Report Header section by placing the mouse pointer just above the Page Header border until the mouse control turns into a double arrow Click and drag the Page Header border until it touches the Report Header Report Header Page Header 00 Books4U customer bales Re CLName Header 12 Adjust the control boxes to fit the text The Date control can be shortened by clickin
169. roducts table Click on the Category column header to select it F d A p m New E Totis BI Gyr Y Selection Click on the Sort Ascending button 2 on the ribbon Note the swe P spening 3 Orga ai X Delete EH More 2 di order of the records sa Sort iter R zj 7 Supplier Category Click on the Sort Descending button AH on the ribbon Note the Beverages order of the records E Fees Breweries Beverages Restore records to their original order by clicking the Clear All CZ umm ading Beverages Sorts button Now sort the Suppliers alphabetically within an alphabetical listing of Categories In other words for each Category in alphabetical order show each Supplier in alphabetical order As Category is sorted first it must reside to the left of the Suppliers Position the mouse pointer in the Category column heading until the pointer changes to a downward pointing black arrow and click the left mouse button The column is now selected Release the mouse Product Name m Supplier category Quantity Pe ai x Liquids Beverages 10 boxes x 20 ang Exotic Liquids Beverages 24 1202 bo While holding down the left mouse hiseed Syrup Exotic Liquids Condiments 12 550 ml b ef Anton s Cajun Seasoning New Orleans Cajun Delights Condiments 48 6 oz jars button drag the Category column ef Anton s Gumbo Mix New Orleans Cajun Delights Condiments 36 boxes header to the left of the Supplier andma s Boysenberry Spre
170. rs Application Title 6 Under Application EA o S Ze eg Proofing Application Icon Browse ptions select the NS cas Advanced e Compact on Close check Display Form none box Customize V Display Status Bar Add ins Document Window Options Overl Window 7 Youcan check the file Trust Center e Stee size before and after the Resources V Display Document Tabs 1 W Use Access Special Keys G Compact and Repair piesa sien option to see that the Remove personal information from file properties on save Use Windows themed Controls on Forms amount of disk space is much smaller Enable design changes for tables in Datasheet view for this database V V Enable Layout View for this database d C Mi Check for truncated number fields Picture Property Storage Format Preserve source image format smaller file size D Convert all picture data to bitmaps compatible with Access 2003 and earlier 174 INDEX Access 2007 Object yE Senie orm d peo poi pe e ote 1 Action Queries Delete Aa obtiene edm 106 Action Queries Make Table 103 Action Queries Update eege 101 Action Queries In Design View 100 Add A Custom Field To A Report 144 Adding Controls in Forms EE 120 Adding Criteria QUEMES certatim edge edv betae sedet pd 93 Adding Totals To A Report In Design View 141 Aggregates In Queries pepe en oleh de he 110 Aggregates On Tables odio Dealer fen 109 JNPEIGOdIeS Ee 108 Apply An Autoformat To A Renort
171. rs table Save As Table Name Customers OK Cancel 5 Follow the steps above to create a primary key for the Transactions table T is the primary key for the Transactions table We will not create a primary key for Transaction Details at this time 54 Field Properties Pane FIELD PROPERTIES The lower half of the work area in Design View displays the available field properties depending on data type The field property can be used to control the display of the data and for data validation Special characters such as dashes in a phone number are not actually stored in the table They are used for display purposes only to create a more readable format If a field property is changed after data has been entered into the table a warning message is displayed as data could be lost if a field size or data type is modified Make a backup copy of the table data before making changes to the table structure Several Field Properties Property Description Field Size Maximum size for data Defaults to 255 characters for text Also used for number or Autonumber data types Format Customize the way data is displayed For example using gt forces all characters to display in uppercase Use when you need to display data in a consistent format Input Mask Displays editing characters to guide data entry dashes in phone number Caption Will be used as the column heading in datasheet view and the label in
172. rthwin Home Create External Data Database Tools Add Ins Datasheet 1 a R ab Refresh All W s Calibri 11 SS SS d FS WEE AC H x lg Font x Rich Text i Datasheet View zzz hi 4 PivotTable View a PivotChart View QZ Design View 1 On the Navigation Bar open an existing table Vi 2 Click iz to change to Design view 3 Click to change to Datasheet view 10 EXERCISE CHANGING TABLE VIEWS Use the pull down menu in the Navigation pane to select Tables Double click the Employees table to open it The table opens in Datasheet view eld M Table Tools e Home Create External Data Database Tools Addins Datasheet DA Nev Total A CG Sel H 4 Repl ka Cr 3m e New E Totals H r Selection d i Replace da sae 4 Spelling Y Advanced gt GoTo View A J ze Refresh 5 Filter Find S B4 HIaAia E Ale e ESMore 7 e lg Select Views Clipboard Font f Rich Text Records Sort amp Filter Find Security Warning Certain content in the database has been disabled Options Pull Down Menu Tables Z Employees ES categories Employee Last Nam First Nam Title Title Birth Date Hire Date Address ER customers 1 Davolio Nancy Sales Associate Ms 08 Dec 1968 01 May 1992 507 20th Ave E 2 Fuller Andrew Vice President Sales Dr 19 Feb 1952 14 Aug 1992 908 W Capital Way 3Leverling Janet Sales Associate Ms 30 Aug 1963 01
173. rties pane The Row Source property lists the allowable values for this field Field Properties General Lookup Display Control Combo Box Row Source Type Value List Row Source Phoenix Scottsdale Glendale Mesa Tempe Chandler Bound Column 1 Column Count 1 Column Heads No Column Widths 1 List Rows 16 List Width 1 Limit To List Yes Allow Multiple Values No Allow Value List Edits Yes List Items Edit Form Show Only Row Source V Ne 7 To ensure that these are the only values that will be accepted for the CCity field change the Limit To List property to Yes CAddress CCity 8 Savethe table Toggle to Datasheet View Scottsdale 9 Click the pull down menu in the first row for CCity The list of Glendale accepted values is displayed Click on any cell to close the list cris Tempe Chandler 10 Click the CCity field box Type Gilbert and tab to the next cell 11 Access 2007 checks the data entry with the list of allowable values The following message is displayed when the value was rejected Microsoft Office Access EJ o The text you entered isn t an item in the list VJ Select an item from the list or enter text that matches one of the listed items 12 Click OK 13 Use the pull down menu to choose a city on the list Type number 9 in the C field 14 Save the Customers table 65 EXERCISE CHANGE A LOOKUP FIELD G
174. ry key the following message appears The primary key is the field that guarantees all records in your table are unique and ensures that there are no duplicate records in your table It is not a good idea to delete the primary key field Microsoft Office Access Deleting field ID requires Microsoft Office Access to delete the primary key Do you want to delete this field anyway Click NO Close the database 36 DESIGNING THE DATABASE Don t Skip You have now examined an Access 2007 database and its This Step components Your database design will depend on the purpose for which it is created and the data it will contain A well designed database will provide current accurate information that is easily maintained The Database Design Process is used to help you create the best design for your needs A good database design follows several guidelines Below are just a few Principle Guidelines Duplicate Information Also referred to as redundant data Keep to a minimum to avoid wasting space and to decrease the likelihood of errors and inconsistencies Subject based Tables Keep table information simple do not include information in a table that does not describe the table This reduces redundant data Uniqueness All records in a table must be unique This is accomplished through the primary key Also reduces redundant data and decrease possible errors when modifying the database The primary k
175. s Associate 2 The subdatasheet opens to display the record for the same employee in the related table Orders Clicking the expand indicator in the Orders table opens to display the records for the same order in the related table Order Details Employees Em City LastName FirstName Title Title Of Birth Date Hire Date Address 1 Seattle Davolio Nancy Sales Associate Ms 08 Dec 1968 01 May 1992 507 20th Ay Order Customer Order Dai Required Dat Shipped Dat Ship Via Freigt 10258 Ernst Handel 17 Jul 1996 14 Aug 1996 23 Jul 1996 Speedy Express 5140 5 Product Unit Pric Quanti Discoui Add New Field Chang x 15 20 50 2096 Chef Anton s Gumbo Mix 17 00 65 20 Mascarpone Fabioli 25 60 6 20 0 00 1 0 3 The record indicator has now turned to a minus sign This is the collapse indicator Clicking the collapse indicator will hide the subdatasheet 4 On the Home tab in the Records group click the More button Point to Subdatasheet at the drop down list and click Expand All 5 On the Home tab in the Records group click the More button Point to Subdatasheet at the drop down list and click Collapse All 6 To change the subdatasheet display on the Home tab in the Records group click the More button Point to Subdatasheet at the drop down list and click Subdatasheet A list of all related tables for Employees ee is displayed
176. s is the specific data we are seeking We need to change PName to reflect the new ownership of the Publisher However there are no field properties that will allow us to make the change By default Access presents us with a query grid for a Select query We need the query grid for an Action query therefore we need to change the query type before we can continue On the Query Tools Design tab in the Query Type group the Select button is active a signifying that we have a Select query grid Click the Update button The Update To Update property now appears on the grid Click the Update To row in the PName column Type Simon amp Schuster Be sure to include the amp symbol within the quotation marks Field PName Table Publishers Update To Simon amp Schuster Criteria Possibility Press or If we simply toggle to Datasheet View we do not see the results of the query as with the Select query What is displayed is the data that will be changed In order to update the values we must Run the query 105 EXERCISE ACTION QUERIES UPDATE On the Query Tools Design tab in the Results group is the Run button Notice that the symbol identifying the Run action is an exclamation mark Also notice that the exclamation mark is included on all Action query buttons All Action queries must be Run before the changes take place in the database EJ ce a Ai LA ES x Run Select Make Append Upd
177. s on tabbed pages To send the report to the printer click the Microsoft Office button and then click Print When the print dialog box displays enter your choices for printer options print range and number of copies Click OK 153 EXERCISE PREPARING AND PRINTING A REPORT Click the Print Preview View button on the Status bar in the lower right corner of the screen 2 The Page Layout group displays the Page Setup Options BONES T Size Portrait Landscape Margins z FE Print Data Only end Page Setup F ES Columns E Page Layout 3 Click the Landscape button to change the page orientation 4 Click the pull down on the Margins buttons to display margin settings The Page Setup button displays the set up options on tabs Options for printing are the same as with all Microsoft applications We will not print this report Click Cancel a T Last Custom Setting Page Setup mem Ex let 025 Right 025 Eri pone Poe haohaoa Orientation v A Portrait Ay Landscape Top 0 75 Bottom 0 75 ee RE Right 0 35 d Sze Letter D Wide Source l Automatically Select D E Top 1 Bottom 1 Printer for Play Report Left 0 75 Right 0 75 Default Printer Use Specific Printer 4 Narrow Top 0 25 Bottom 0 25 Left 0 25 Right 0 25 E L ai When you have made all necessary changes click Print on the Print Preview
178. se 111 CALCULATING AGGREGATES Expressions Aggregates Column Headings Totals in Table Datasheet View Totals in Queries Design View In a previous section we saw that expressions can be used in a query to create calculated fields on a single record The calculated field is derived from other fields and is not stored in the database With aggregate functions such as Sum Avg Min Max or Count Access groups records and then performs calculations on the group When an aggregate function is used Access displays one row in the query result datasheet for each group The aggregate is considered a summary of the group Access assigns column headings in the query results datasheet using the function name the word Of and the field name from which the function has been derived For example adding the data in the Sales column would have a heading SumOfSales A new feature in Access 2007 allows you to include an aggregate function on your database tables using the Totals button on the ribbon The totals row appears beneath the asterisk row The aggregate function on database tables will total selected columns of the entire table You cannot calculate totals based on a specific data item For example you cannot count total sales for a particular book Once you have defined aggregates for your table they can never be removed They can however be hidden from view The Totals button acts as a toggle between hidden aggreg
179. se pointer on the bottom edge of the subform until the pointer changes to a double arrow drag the border down and release the mouse Click in a blank area of the form to deselect the subform ISBN x Title 789736136 How Computers Work 761140670 Never Too Late to Love a Computer 596527608 Access 2007 The Missing Manual 1558747524 The Power of Focus 1557423601 As a Man Thinketh 446530697 Running With the Giants 055327757X The Greatest Salesman in the World Record I4 10f7 4 Search 15 Click the record navigation bar of the main form Employees to locate the fifth record The subform shows that Leadership was sold twice by Bullock To get more information about the sale we need to add the purchase date The purchase date was on the list of required fields to be placed on the Employee form However the purchase date 7T Date is in the Transactions table If we had selected TDate while creating the form it would have required three tables and the form wizard would have created a form with two subforms We need only add 7Date to the current subform To accomplish this task we need the Field List pane 131 EXERCISE SUBFORMS ENHANCING THE APPEARANCE 16 Click the subform to select it 17 In the Controls group of the Format tab click the Add Existing Fields button 18 If the tables are not visible click Show all tables at the bottom of the fields list pane 19 In the Field List pane click the plus sign n
180. sely the Books table uses C as the linking field to the Customers table To retrieve the names of the customers who have purchased a book we take the C7 in the book record and use it to look up the C in the Customers table The challenge created by this design is the large amount of redundant data created For every book that Hugo purchases the entire record of information for Hugo must be duplicated Similarly for every customer who purchases No Excuse the entire record of information for the book No Excuse must be duplicated The possibility of creating errors when duplicating the data or for modifying the data is vast For example a change in Hugo s address would require a change in every record that the address appears If one record is missed the integrity of your data has now been violated 81 CREATING MANY TO MANY RELATIONSHIPS Junction Tables Foreign Keys Reduce Data Redundancy To represent a many to many relationship you must create a third table called a junction table that breaks down the many to many relationship into two one to many relationships The primary key from each of the two tables is inserted into the junction table This eliminates duplicate records and reduces the redundant data The necessary redundancy is found only in the foreign keys of the junction table The C primary key from the Customers table and the ISBN primary key from the Books table are placed in the junction table
181. step is to limit one customer per page of the report 147 EXERCISE MODIFYING THE REPORT PROPERTY SHEET Our Books4U Customer Sales Report is almost finished Examining the report in either Layout or Report view allows us to see the data from the record source and all totals that have been calculated The title date and page numbers have been added to all pages We now will have the report display one customer per page This will ensure that customer or transaction information is not split across multiple pages and that the aggregate totals are not separated from detail information 1 Click the Print Preview button on the Status bar in the lower right corner of the screen to observe how Access will print the report Notice that in some cases parts of customer information is split across multiple pages We want to force Access to display one customer per page 2 Close the Print Preview Click the Design View button on the Status bar in the lower right corner of the screen 3 Customer information first appears in the CLName Header Each time Access detects a customer in the record source that is different from the last we want this section to print on a new page Right click the CLName Header and then click Properties The Property Sheet opens 4 Click the All tab Click the pull down arrow for Property Sheet the Force New Page property We want to force type Section a new page before the customer
182. t use the AutoFormat dialog box in either Layout or Design View l 2i 10 11 12 Click the Layout View button on the Status bar in the lower right corner of the screen On the Format tab in the AutoFormat group click the pull down menu The AutoFormat gallery displays AutoFormat Click the desired style to apply to the report AutoFormat Check the report formatting In some cases the new style will change your original report format If you find that the new style does not work click the Undo button on the Quick Access Tool Bar To apply an AutoFormat style to a report in Design view click the Design View button on the Status bar in the lower right corner of the screen On the Arrange tab in the AutoFormat group click the pull down menu The AutoFormat gallery displays Click the desired style to apply to the report Again check the report formatting When you find a style that you want to keep save the report You can keep the style that you have and add a background fill color to the Page Header section Click the Page Header section to select it On the Design tab in the Font group click the pull down on the Fill Back icon Choose acolor You can click More Colors for a wider selection Click OK Preview your report Save your report 151 NOTES 152 PRINT A REPORT Preview Before Printing Page Setup Options Print the Report Typically
183. t to the Publishers table The fields from the Publishers table are now visible Double click or click and drag the Pname field to the form The Label and Text Box controls appear on the form Close the Field List pane Resize each control by clicking the right and bottom borders of the control and dragging the borders to match the controls already on the form Reposition the controls if necessary Click the Form View button on the Status bar in the lower right corner of the screen to view the changes Click back to Design View Add a reminder message at the bottom of the form To do this we need a Label control Before we add the control expand the Detail section of the form Roll the cursor to the area between the Detail and Form Footer sections When the cursor changes to a double arrow left click and drag the line down to expand the detail section p PNaiae PName EXERCISE CREATE A FORM ADDING CONTROLS 19 To add a Label control click the Label button in the Controls group on the ribbon If you do not see the Controls group make sure you are in Design View 20 Position the mouse pointer below the Pname controls The mouse pointer changes to A Click and drag the mouse pointer to create a control that matches the size of the Pname Text Box control Release the mouse The Label control is created and a blinking cursor is positioned to the top left of the control 2 Type Orders are completed on th
184. ta source by creating a linked table and then click OK Get External Data Access Database SIS Select the source and destination of the data Specify the source of the data File name C iDesktoplAccess 2007 Class Northwind 2007 accdb Browse Specify how and where you want to store the data in the current database Import tables queries forms reports macros and modules into the current database If the specified object does not exist Access will create it If the specified object already exists Access will append a number to the name of the imported object Changes made to source objects including data in tables will not be reflected in the current database Link to the data source by creating a linked table Access will create a table that will maintain a link to the source data Changes made to the data in Access will be reflected in the source and vice versa NOTE If the source database requires a password the password will be stored with the linked table 4 Inthe Link Tables dialog box select the Products table Click OK to finish 5 Access creates the linked tables Open the ink Table Ea table in datasheet view Tables Order Details Cancel E Transaction Details Orders Products select all Shippers A g Transactions Suppliers Deselect all Linked Table TE Products 173 APPENDIX D COMPACT AND REPAIR Database files grow
185. tabase It is located in the Access 07 Class folder on the desktop OO gt A e Deskto p Access 2007 Class X Search Favorite Links E Documents gt Bl Desktop Bl Recent Places p Computer E Pictures B Music Recently Changed BB Searches d Public Folders Wy Organize 17 BB New Folder Name Date modified Employee Info 2007 ManytoMany 2007 E Northwind 2007 Registration2 2007 Registration 07 Type Size Northwind 2007 The Access User Interface opens Me p Is Northwind 2007 Database Access 2007 Microsoft Access DW TIPO Home Create External Data Database Tools e eT a T ig New Totals Y7 Selection i b 32 NEL LII LI EEE EISE da EE i sae 47 Spelling 4l YJ Advanced CS Vi Past IER S sa 287 JI Refresh Filt Find d T B ale l A l a VER le A pus A Delete Gi More 22 a Y Toggle Filter i Views Clipboard Font Ta Rich Text Records Sort amp Filter Find Security Warning Certain content in the database has been disabled Options Tables Categories Customers Employees Order Details Orders Products Shippers Suppliers Ready THE ACCESS USER INTERFACE Office Button Quick Access Toolbar Ribbon Navigation Pane MS Office Window Frame Dialog
186. ted on Eo mn the Create tab in the Forms group Form Wizard The following table describes the form creation tools We will create forms using a number of these tools in the pages that EM nope follow 5 RN 7 EE Modal Dialog Form Creation Tool Description Form Creates a form with one click and uses all the fields in a table The form will display in Layout view Split Form Displays the form in Form view and Datasheet view simultaneously Form view can be used to edit a record while the Datasheet view can be used to navigate to a record Multiple Items Multiple records from the table are displayed in the form as a spreadsheet Pivot Chart Creates a form in Pivot Chart view This allows a three dimensional analysis of data Blank Form Creates a form from scratch The form is developed by adding and positioning controls including a field on a blank form Form Wizard The wizard takes the user through a series of steps to specify the layout and background themes for the form Form Design Helps to create a more detailed structure of your form in Design view With this tool you have a wider variety of controls you can add to your form You can also resize form sections such as the Header or Detail section and you can edit the text box control sources without using the property sheet 117 EXERCISE CREATE A FORM FORM TOOL Books4U has decided to create forms for
187. th caution Itis a good idea to make a copy of the table s first and practice your update query before running the query on the original table Ej mat ai ek View Run Select Make Append Update Y Table ES x Crosstab Delete Results Query Type Action Query Types Query Action Update Add change or delete the data in one or more existing records Queries can accept multiple criteria allows updates to a large number of records at a time and can change data in more than one table at a time Append Adds a set of records from one or more tables source to the bottom of one or more tables destination Make table Creates a new table from data in an existing table or query Delete Deletes records based on specific criteria Data should be backed up prior to the delete as deletes cannot be undone 104 EXERCISE ACTION QUERIES UPDATE Possibility Press is under new ownership Change the name to Simon amp Schuster 1 2 Close all database objects In the ribbon on the Create tab in the Other group click the Query Design button The Show Table dialog box opens Select the source data Publisher name is in the Publishers table Select the Publishers table Click Add Click Close Double Click PName in the Publishers field list The grid displays the field name and table name of the required data Click the Criteria row in the PName column Type Possibility Press Thi
188. the Tables group click the Table Design button Refer to the Transaction Details table design on page 41 for the required fields 2 This table shows the details of what was actually purchased by the customer It was created to reduce unnecessary data duplication see discussion on page 40 fu T Details Field Name Data Type Description Text Transaction number of the purchase Text Product number of the book purchased Number The number of books purchased Will usually be 1 Currency The price paid by the customer Sales tax amp shipping are not included 3 Save the table Name it Transaction Details Click OK 4 Access alerts you to define a primary key Click No 5 Close the Transaction Details table 52 PRIMARY KEY A Unique A Primary key is a field or fields that provide Access 2007 with a Identifier unique identifier for each record Each time the table is opened it is automatically sorted by the primary key When a new record is added to the table Access checks to ensure that there is no other record with the same value in the primary key field If the data is a duplicate Access will display a message indicating that duplicate values exist and will not allow the record to be saved The primary key field can not be left blank when a new record is added to the table as it is the field that is checked and used for sorting Access will not allow the record to be saved When you elect to create a new tab
189. the Validation Text box of the Field Properties pane 6 Enter the number 1 in the Qty field and toggle back to Design view 7 Inthe Validation Text box of the Field Properties pane type A number 0 must be entered Save the table Toggle back to Datasheet view to test 8 Enter a0 in the Qty field and press Enter A more comprehensible message for the user is displayed after Access performs the validation test j Cutomers Transactions __ Transaction Details T E ISBN Qty T Price EN 0 Microsoft Office Access Ee 9 Click OK Type a number gt 0 in the Qty field Bj Anumber gt 0 must be entered Help 10 Save the Transaction Details table 62 LOOKUP FIELD Drop down List Limit To List Lookup Wizard Steps to Create a Lookup Field Another way to validate data is to restrict the exact values that can be entered A drop down list is created for the field and displayed in Datasheet view Allowing the user to choose from a list will also limit and reduce typing errors for data that must be entered repeatedly You can further restrict the field to only those items in the drop down list Setting the Limit To List property to Yes will cause Access 2007 to display an error message and reject the data if any value not in the list is entered The Lookup Wizard is used to create the drop down list Values for the list can be picked from data in a table or query
190. tion bar From the Office button select Save As Save Object As The Save As dialog box opens You can rename the table and click OK Save As Save Products to Copy of Products As Table OK E Cancel Steps to Delete a Table in Access Make a copy of the table At the Navigation Bar select the table to delete Press the delete button in the Records group on the Home tab Pay close attention to the warning message box Click OK to delete the table 35 EXERCISE DELETING A TABLE 1 Close all tables in the database 2 Click the Orders table to select it On the Home tab of the ribbon in the Records group click the Delete button Depending on the relationships of the Orders table you will get one of several warning messages Microsoft Office Access You can t delete the table Orders until its relationships to other tables have been deleted Click NO Always delete relationships manually Microsoft Office Access Ex r o You can t delete the table Orders it is participating in one or more relationships k If you want to delete this table first delete its relationships in the Relationships window Click OK Microsoft Office Access Do you want to permanently delete the selected field s and all the data in the field s To permanently delete the field s dick Yes Click NO 3 When deleting a field that has been specified as the prima
191. tion pane double click the Employees table to open Note the new record that was added through the form In the Navigation pane double click the Transactions table to open Note the new record that was added through the form datasheet Reopen the Employee Form by double clicking it in the Navigation pane under Forms objects Click the last record button on the form Record Navigation bar Close all objects 119 EXERCISE CREATE A FORM SPLIT FORM TOOL A split form is a new feature in Access 2007 that gives two views of the same data at the same time a Form view and a Datasheet view The two views are connected to the same data source and synchronized at all times Selecting a field in one part of the form selects the same field in the other part of the form For example use the datasheet to quickly locate a record and then use the form portion to view or edit the record 1 Left click the Customers table in the Navigation pane to select the table 2 In the ribbon click the Create tab In the Forms group click the Split Form button Customers E Ci 685 23 6547 CCity Phoenix P CFName Fawn CState AZ CZip 85264 n CLName Todd Form View CPhone 480 255 4466 CAddress 602 S Maple Record 26 of 31 CS CFName CLName CaAddress CCity Md CState CZip CPhone 659 98 7556 Isabel Quail 548S Juniper Mesa AZ 85698 602 335 55 664 78 2149 Zeb Curry 75 W Spruce Glendale
192. training on a variety of administrative systems and desktop applications e Design and develop comprehensive training and reference materials e Provide technology training support in a variety of ways including telephone help lines one on one assistance online help troubleshooting consultation and referral services e Support the colleges technology training efforts by delivering on site technology training delivering Train the Trainer sessions and providing training materials e Provide leadership and support to the teams implementing new technologies and administrative systems within the organization e Cultivate positive partnerships with our colleges to meet and exceed their training needs and expectations e Collaborate with organizational teams to develop strategies to meet future technology training needs e Chair and host the Regional Training Committee RTC to collaboratively develop training strategies maintain technology training consistency and overcome the challenging technology training needs throughout the District e Expand and update our knowledge and skills in the areas of technology training and instructional design TABLE OF CONTENTS Access 2007 Eesen pedit eio eg 1 Database Concepts Lco awa Gs oan a ie d eR 2 Stat ACCESS MEE 4 The Access User Interface Lea n a n uae ees 6 Database AT 8 Exploring An Existing Database sese 10 Tables Getting Familiar With Datasheet View
193. ts record source The unbound and calculated controls can be added to complete the design by using the control tools on the Design tab in the Controls group A field can be bound to a control by either typing the field name in the control itself or in the box for the ControlSource value in the control s Property Sheet The property sheet defines the characteristics of the control such as name data source and format To display the property sheet in layout or design view press F4 In design view only click the Property Sheet button in the Tools group on the Design tab Ss 4A c i Add Existing Property Fields Sheet Tools Hi 143 EXERCISE ENHANCING REPORTS IN DESIGN VIEW In the Books4U Customer Sales Report that was created by Access using the Report Wizard some of the control fields are not in the correct place based on our specifications In this exercise we will modify the report to match the requirements given to us by the manager Ana Rivera 1 The Books4U Customer Sales Report is currently open in Print Preview 2 Click the Design View button on the Status bar in the lower right corner of the screen Report Header Page Header CLName Header dut 3 If needed close the Shutter Bar on the Navigation pane for a larger viewing area 4 Notice that the title date and page numbers must be re
194. ture Self Describing Design Control The database table displays the fields that describe the records in the datasheet view In addition each field has a data type which is used to ensure the integrity of the data The field name its data type the field description and the field properties are all used in defining the table structure The table structure is documented and displayed in Design View The fact that the database not only contains data but also includes a description of its data is one of the biggest advantages of database technology You do not need to search for a separate manual when you have questions about the design of your database That information is contained within the database itself Although a new table can be created by simply typing new records into a blank datasheet creating a table in Design view allows you to set data types and design properties In Design view Access does not add the ID field or set data types for the new table automatically Once the data is in the table and the rules are set by Access they may be difficult to change Using Design view gives you total control in defining the table structures Editing a Table in Design View Change Procedure Open table In the Navigation Pane double click a table name to open it in datasheet view Click the Design view button Make changes Modify the Data Type and Field Description by placing your cursor in that field and typin
195. ty is an unnecessary duplication We know that the city will be Chandler as this is the only city we were searching The CCity field can be hidden in the Datasheet View 10 Toggle back to Design View 11 Uncheck the Show box in the CCity column Toggle to Datasheet View Field CFName CLName CPhone CCity Title TDate Price Table Customers Customers Customers Customers Books Transactions Transaction Details Sort Ascending Show v N E EI NI NI v Criteria chandler Think and grow rich or chandler leadership 12 Save the query by clicking the close button on the query window Name the query Late Chandler Order 99 NOTES 100 SELECT QUERIES CALCULATIONS Calculated Field Expressions Naming the Calculated Result Steps to Create a Calculated Field Expressions can be used to create calculated fields in a query The calculated field is a result that derives its values from calculations performed on other fields The calculated field is not entered by the user nor is it stored in the database The value of a calculated field changes each time the table fields are updated and the query is executed Expressions can be combinations of functions field names numbers text and operators Expressions can be created to calculate totals count records join first and last names or perform mathematical projections Field names used by an expression are enclosed in square brackets The result field nam
196. ull content if you are sure the database is virus free Command Tabs on the Ribbon Command Tabs Includes Commands for Home Organize and manipulate data Includes views clipboard font rich text sort amp filter and find Create Creating database objects of tables forms reports queries and macros External Data Importing and exporting data operations Database Tools Defining relationships showing hiding tools analyzing data moving data to a server and accessing database tools Office Button THE ACCESS USER INTERFACE Quick Access Toolbar Dialog Box Launcher Northwind 2007 Database Access 2007 Mj soft Access m x External Data Database Tools eg a8 ei gg New Totals Sl w Y Selection EA SS Gas SI clsave Spelling zi Y Advanced I gt SE ll PES J B z y A E IS IS eye Ji Ke X Delete EJ More 5 SE Y Toggle Filter ES CM Views Clipboard Font RihTet Records Sort amp Filter JL Find jJ 9 Security Warning Certain content in the database has been disabled Options All Access Objects 2 Security Alert VBA Macro Access has disabled potentially harmful content in this database If you trust the contents of this database and would like to enable it for this session only click Enable this content Warning It is
197. un Saguaro Room 1 30 PM William Bellinger 5 07 Dec 06 John Bullock WSC 500 00 No M010 Web Based Education Main Hall 1 30 PM Dr John Harris 6 07 Dec 06 Betty Smith WSC 75 00 Yes C020 Distance Learning Cholla Room 1 30 PM Millicent Trumble T050 Delete Cascade Options Maricopa Room 10 30AM Dr Kathleen Moser Table Workshops Workshops workshopid workshopname C020 Distance Learning M010 Web Based Education S030 Making Learning Fun starttime speaker 1 30 00 PM Millicent Trumble 1 30 00 PM Dr John Harris 1 30 00 PM William Bellinger roomname Cholla Room Main Hall Saguaro Room Yucca Room EEN E Computers in the Classroom 1 30 00 PM Dr Joan Pullman Primary Key Table Employees Foreign Key membership workshopid Y040 Y040 S030 M010 C020 lastname Yee Jones Rivera Bullock Smith college depositamt PVC 150 00 SCC 150 00 RIO 100 00 WSC 500 00 WSC 75 00 dateregister firstname 12 7 2006 Patricia 12 5 2007 Lori 12 7 2006 Ana 12 7 2006 John 12 7 2006 Betty P4 iV Value An allowable value for this field is a number that is at least 50 START ACCESS Getting Started At the Windows Desktop click the start button KX on the Taskbar Choose Microsoft Office Access 2007 from the start menu The Getting Started With Microsoft Of
198. utton The Show Table dialog box opens Show Table Tables Queries Both Select the source data Customer name and phone are in the Customers table Date of purchase is stored in the Transactions table The Transaction Details table stores the price paid To locate the titles of books we need the Books table Transaction Geen Transactions Books 3 Select the Books table press and hold the Ctrl key and select the Customers Transaction Details and Transactions tables Release the Ctrl key Click Add Click Close 4 Double click the CFName CLName CPhone and CCity fields in the Customers field list Double click Title from the Books field list Next double click TDate from the Transactions field list Finally double click Price from the Transaction Details field list The grid displays the field names and table names of the required data We now have all of the required fields We must now tell Access to limit the display of results to Chandler customers 5 Click the Criteria row in the CCity column Type Chandler Not only do we want to see customers from Chandler we want to see specific book titles purchased by those customers Placing criteria statements into more than one field creates an AND condition 6 Click the Criteria row in the Title column Type Think and Grow Rich quotes We are also looking for purchases of the book Leadership Placing more than one criteria statements into one field c
199. were automatically generated to run the query are saved If another book published by Wildside Press was added to the Books table immediately after we ran this query we would want to see that book in the result the next time we requested the data By saving the instructions every time we request this data Access will run the instructions to renew the result thus ensuring that the most current data is displayed 8 Onthe Home tab in the Views group click the drop down list and select SQL View SELECT Books ISBN Books Title Books Author Publishers PName FROM Publishers INNER JOIN Books ON Publishers P Books P WHERE Publishers PNamej Wildside Press ORDER BY Books Title The instructions to create the query are shown above When you save the query it is these instructions that are saved When you click the query name in the Navigation Pane Access retrieves this set of instructions executes them and displays the result 9 Save the query by clicking the close button on the query window Name the query Wildside Press Books 97 EXERCISE SELECT QUERIES CONDITIONAL AND OR A book order is coming in late We need to alert Chandler customers who have purchased copies of Think and Grow Rich and Leadership List the customer s name and phone number as well as the date of purchase and the price paid 1 Close all database objects 2 In the ribbon on the Create tab in the Other group click the Query Design b
200. y 5 Buchanan Jeremy Sales Manager Mr 04 Mar 1955 17 Oct 1993 14 Garrett Hil t 65 Michael Sales A iat M 02 Jul 1963 17 Oct 1993 C try H uyama ichae ales Associate r u oventry Hot 7 King Robert Sales Associate Mr 29 May 1960 02 Jan 1994 Edgeham Hol 8 Callahan Laura Inside Sales Coordinato Ms 09 Jan 1958 05 Mar 1994 4726 11th Ay 9 Dodsworth Anne Sales Associate Ms 02 Jul 1969 15 Nov 1994 7 Houndstoo New Record 4 4 20f9 gt n K Search 4 asl gt mployee Num Lock Emer 2 Press the Tab key several times to move forward several fields Go back using Shift Tab Press the arrow keys to move up down and side to side through the data Click on the Last Record Navigation Button EE to move to the last record Click on the First Record Navigation Button I to move to the first record GN rc dies ED To change column width using the ribbon tools position the cursor at the field to change On the Home tab in the Records group click the More Button 7 At the Column Width dialog box type a value to increase or decrease the column width or use the Best Fit button to set the p width to accommodate the length of the longest entry Press OK Column Width Column Width 0625 Oo 1 8 Tousethe mouse to best fit a column position the mouse pointer on the right column boundary line in the header row between columns 4 and 5 until the pointer changes to a crosshair Double click the left mouse button Emplo
201. yee Last Name FirstName Title Cr Birth Date Hire Date Address H H 1 Davolio Nancy Sales Asso Ni Me 08 Dec 1968 01 May 1992 507 20th Ave E Seat F 2 Fuller Andrew Vice President Dr 19 Feb 1952 14 Aug 1992 908 W Capital Way Tacc E 3 Leverling Janet Sales Associati Ms 30 Aug 1963 01 Apr 1992 722 Moss Bay Blvd Kirkl 4 Peacock Margaret Sales Associati Mrs 19 Sep 1958 03 May 1993 4110 Old Redmond Rd Redi c 5 Buchanan Jeremy Sales Manager Mr 04 Mar 1955 17 Oct 1993 14 Garrett Hill Lonc E 6 Suyama Michael Sales Associat Mr 02 Jul 1963 17 Oct 1993 Coventry House Lonc E 7 King Robert Sales Associat Mr 29 May 1960 02 Jan 1994 Edgeham Hollow Lonc E 8 Callahan Laura Inside Sales Cc Ms 09 Jan 1958 05 Mar 1994 4726 11th Ave N E Seat E 9 Dodsworth Anne Sales Associati Ms 02 Jul 1969 15 Nov 1994 7 Houndstooth Rd Lonc New 13 DISPLAYING RECORDS IN A SUBDATASHEET Related Tables Editing Data Multiple Subdatasheets Layout Changes Expand or Collapse When two tables are linked you can view related data from the primary table within a datasheet by displaying a Subdatasheet A subdatasheet is a datasheet that is nested within another datasheet showing related data items When a record has a subdatasheet it is indicated by a plus sign in the left column called the expand indicator When you click the expand indicator the subdatasheet is displayed below the selected record One of the advantages to

Access 2007-Manual - Human Resources

Contents

Download Pdf Manuals

Related Search

Related Contents