Access 2003 in Pictures

Access 2003 In Pictures by Tony Fowlie www.inpics.net Access 2003 In Pictures Copyright This book is provided under a Creative Commons license at: creativecommons.org/licenses/by-nc-nd/2.5/ You are free to download, copy, and share this electronic book with others. However, it is illegal to sell this book, or change it in any way. If you’d like to sell or change it, just contact us at contact@inpics.net. Trademarks and Disclaimer Visibooks™ is a trademark of Visibooks, LLC. All brand and product names in this book are trademarks or registered trademarks of their respective companies. Visibooks™ makes every effort to ensure that the information in this book is accurate. However, Visibooks™ makes no warranty, expressed or implied, with respect to the accuracy, quality, reliability, or freedom from error of this document or the products described in it. Visibooks™ makes no representation or warranty with respect to this book’s contents, and specifically disclaims any implied warranties or fitness for any particular purpose. Visibooks™ disclaims all liability for any direct, indirect, consequential, incidental, exemplary, or special damages resulting from the use of the information in this document or from the use of any products described in it. Mention of any product does not constitute an endorsement of that product by Visibooks™. Data used in examples are intended to be fictional. Any resemblance to real companies, people, or organizations is entirely coincidental. ISBN 1597061018 TABLE OF CONTENTS i Table of Contents Database Basics .............................................1 Create a new database ..........................................................................2 Create tables ........................................................................................13 Create records......................................................................................21 Create forms.........................................................................................28 Create queries ......................................................................................40 Create reports ......................................................................................47 Working with Tables .....................................59 Modify tables ........................................................................................60 Create new tables.................................................................................78 Specify data types................................................................................82 Specify field properties .......................................................................97 Edit records ........................................................................................100 Find records .......................................................................................104 Sort and filter records........................................................................109 Create table relationships .................................................................115 TABLE OF CONTENTS ii Working with Forms ....................................123 Modify forms ...................................................................................... 124 Add/delete records ............................................................................ 138 Edit records........................................................................................ 142 Find records....................................................................................... 145 Filter records...................................................................................... 148 Working with Queries..................................151 Create queries.................................................................................... 152 Sort results......................................................................................... 159 Add criteria......................................................................................... 162 Employ Boolean operators ............................................................... 165 Find duplicate records ...................................................................... 171 Create Update queries....................................................................... 179 Create Delete queries ........................................................................ 187 Working with Reports..................................195 Format reports ................................................................................... 196 Create mailing labels......................................................................... 213 DATABASE BASICS 1 Database Basics In this section, you’ll learn how to: • Create a new database • Create tables • Create records • Create forms • Create queries • Create reports DATABASE BASICS 2 Create a new database 1. Start Microsoft Access 2003. Your screen should look like this: DATABASE BASICS 3 2. In the Getting Started pane, click Create a new file. DATABASE BASICS 4 3. When the New File pane appears, click Blank Database. DATABASE BASICS 5 4. When the File New Database window appears, create a new folder in the My Documents folder called Practice Access Files. Tip: To create a new folder, double-click the My Documents folder so it appears in the Save in drop-down list. Then click the icon. DATABASE BASICS 6 5. Double-click the Practice Access Files folder. It should appear in the Save in box. DATABASE BASICS 7 6. In the File name box, type: Friends.mdb Tip: The file extension for Access databases is .mdb. Just like Word files are something.doc, and Web pages are somethingelse.html, Access databases are database.mdb. MDB stands for “Microsoft DataBase.” DATABASE BASICS 8 7. Click the button. The window for the Friends database should open: DATABASE BASICS 9 Identify database elements Elements of databases A database stores information in an organized way, and makes it easy to get information in and out. Tables store data within the database. Forms make it easy to put data into tables. Queries pull out specific data. Reports put data in an easily-read format. Table Query Report Form Table DATABASE BASICS 10 1. In the Objects list, click Tables. 2. Click Queries. DATABASE BASICS 11 3. Click Forms. 4. Click Reports. DATABASE BASICS 12 5. Click Tables. 6. Click the button. The Friends database window should expand to fill the screen: DATABASE BASICS 13 Create tables 1. Double-click Create table by entering data. A blank table should open: DATABASE BASICS 14 Name fields 1. Double-click the Field 1 column header. 2. Type: First Name 3. Press the ENTER key on your keyboard. The column header should look like this: DATABASE BASICS 15 4. Double-click the Field2 column header. 5. Type: Last Name It should look like this: 6. Press the ENTER key on your keyboard. 7. Double-click the Field3 column header, type: City then press ENTER. DATABASE BASICS 16 8. Double-click the Field4 column header, type: Zip then press ENTER. 9. Double-click the Field5 column header, type: Phone Number then press ENTER. The table should now look like this: DATABASE BASICS 17 Delete unused fields 1. Right-click the Field6 column header. 2. When the menu appears, click Delete Column. DATABASE BASICS 18 3. When the alert window appears, click the button. 4. Right-click the Field7 column header. When the menu appears, click Delete Column. When the alert window appears, click the button. 5. Delete the Field8, Field9, and Field10 columns the same way. The table should now look like this: DATABASE BASICS 19 6. On the Menu Bar, click File, then Save. 7. When the Save As window appears, type: Friends of Mine in the Table Name box. 8. Click the button. DATABASE BASICS 20 9. When the alert window that reads There is no primary key defined appears, click the button. Access will insert an ID field—the Key field—in the table: 10. The key field When the alert window popped up, and you clicked the Yes button, Access added the ID field to the table. The ID field is now the table’s primary key, or key field. That means it can’t contain any duplicates. Every table should have a key field. For example, if a hospital keeps a database, each patient can have a unique ID number in the key field. That way, if it has more than one patient named John Baker, it can easily distinguish John Baker, ID #326 in for a checkup, from John Baker, ID #298 who needs his gall bladder removed. DATABASE BASICS 21 Create records 1. Click in the box under the First Name column header. 2. Type: Elvis 3. Press the TAB key on your keyboard. The table should now look like this: 4. Type: Presley then press the TAB key. 5. Type: Baltimore then press TAB. DATABASE BASICS 22 6. Type: 21212 then press TAB. 7. Type: 4105551212 then press TAB. The table should now look like this: Tip: Notice how the cursor in the row selector has moved down to the second (new) record. When you move on to a new record, Access automatically saves the previous record. DATABASE BASICS 23 Add new fields 1. Right-click the Zip column heading. 2. When the menu appears, click Insert Column. The table should now look like this, with a new blank field: 3. Double-click the column heading and type: State 4. Press the ENTER key. DATABASE BASICS 24 5. Click inside the new State field for the first record. 6. Type: MD 7. Press TAB until the cursor moves down to a new record. Record number 1 is saved and complete. DATABASE BASICS 25 Move fields 1. Click the Phone Number column heading. The entire column should be selected. 2. Place the cursor on the Phone Number column heading. Then drag the column so the cursor rests between the Last Name and City columns. 3. When you see a thick black line between the two columns, release the mouse button. The Phone Number column should now rest between the Last Name and City columns: DATABASE BASICS 26 4. On the Menu Bar, click File, then Close. 5. When prompted to save the changes to the table layout, click the button. DATABASE BASICS 27 The Friends database window should now look like this: DATABASE BASICS 28 Create forms 1. In the Objects list, click Forms. 2. Double-click Create form by using wizard. DATABASE BASICS 29 3. When the Form Wizard window appears, click the button. DATABASE BASICS 30 All the table fields should be added to the form: 4. Click the button. DATABASE BASICS 31 5. When the next screen appears, leave Columnar selected, then click the button. DATABASE BASICS 32 6. When the next screen appears, make sure Standard is selected, then click the button. DATABASE BASICS 33 7. When the last screen appears, type: Friends of Mine Data Input Form in the box. DATABASE BASICS 34 8. Click the button. The form should open and look like this: DATABASE BASICS 35 Add a new record 1. In the form window, click the button. A blank record should appear: DATABASE BASICS 36 2. Press TAB to advance to the First Name box. 3. Type: Bo then press the TAB key. 4. Type: Diddley then press the TAB key. 5. Type: Richmond then press the TAB key. 6. Type: VA then press the TAB key. DATABASE BASICS 37 7. Type: 23220 then press the TAB key. 8. Type: 8005557890 The form should now look like this: DATABASE BASICS 38 9. Press the TAB key again. The form should progress to a new, blank record: The old record has been saved. DATABASE BASICS 39 10. On the Menu Bar, click File, then Close to return to the database window. DATABASE BASICS 40 Create queries 1. In the Objects list, click Queries. What’s a query? A query is a way to get specific information from the database. Essentially, it’s a question. You use queries to ask the database things like, “Who are my customers in Montana?”, or “How many pipe fittings have I sold this month?” DATABASE BASICS 41 2. Double-click Create query by using wizard. 3. When the Simple Query Wizard opens, double-click First Name in the Available Fields list. DATABASE BASICS 42 First Name should appear in the Selected Fields column: DATABASE BASICS 43 4. Click the button. This should add the Last Name field to the Selected Fields list: DATABASE BASICS 44 5. Double-click Phone Number. DATABASE BASICS 45 This should add the Phone Number field to the Selected Fields list: 6. Click the button. DATABASE BASICS 46 7. When the final screen appears, type: Names and Numbers 8. Click the button. The query is automatically saved and executed. It should look like this: 9. On the Menu Bar, click File, then Close to return to the database window. DATABASE BASICS 47 Create reports 1. In the Objects list, click Reports. 2. Double-click Create report by using wizard. DATABASE BASICS 48 3. When the Report Wizard window appears, click the Tables/Queries drop-down arrow. When the list appears, click Table: Friends of Mine. DATABASE BASICS 49 4. Click the button to move all the fields into the Selected Fields list. DATABASE BASICS 50 5. In the Selected Fields list box, click ID, then click the button. The ID field should be removed. The report wizard should now look like this: 6. Click the button. DATABASE BASICS 51 7. When the next screen appears, click the button. DATABASE BASICS 52 8. When the next screen appears, click the first drop-down arrow, then click State in the list. 9. Click the button. DATABASE BASICS 53 Choose layout and style 1. When the next screen appears, make sure Tabular is checked in the Layout section. Then click the button. DATABASE BASICS 54 2. When the next screen appears, click Soft Gray in the list of available styles. Then click the button. DATABASE BASICS 55 View the report 1. When the final screen appears, type: My Friends in the box. Then click the Preview the report radio button. DATABASE BASICS 56 2. Click the button. The report is automatically created, saved and opened. It should look like this: 3. On the Menu Bar, click File, and then Close to return to the Friends database window. DATABASE BASICS 57 4. On the Menu Bar, click File, then Exit to close Access. DATABASE BASICS 58 WORKING WITH TABLES 59 Working with Tables In this section, you’ll learn how to: • Modify tables • Create new tables • Specify data types • Specify field properties • Edit records • Find records • Sort and filter records • Create table relationships WORKING WITH TABLES 60 Modify tables 1. Open a web browser and go to: www.inpics.net/books/acc2003 2. Right-click the FoodStore1.mdb link. When the menu appears, click Save Target As. 3. When the Save As window appears, open the Practice Access Files folder on your hard drive. Then click the button. WORKING WITH TABLES 61 4. Wait for the database to download completely, then close the web browser. WORKING WITH TABLES 62 Open an existing database 1. Start Microsoft Access. 2. In the Getting Started pane, click More. WORKING WITH TABLES 63 3. Open the Practice Access Files folder, then double-click FoodStore1.mdb. WORKING WITH TABLES 64 4. When the database opens, click the button in its window. The FoodStore1 database window should now look like this: WORKING WITH TABLES 65 Employ an input mask 1. Make sure Tables is selected, then right-click the Customers table. WORKING WITH TABLES 66 2. When the menu appears, click Design View. WORKING WITH TABLES 67 The Customers table should open in Design View. 3. Click in the Phone Number row. WORKING WITH TABLES 68 4. In the Field Properties area of the window, click in the Input Mask property. 5. Click the button beside the Input Mask property. WORKING WITH TABLES 69 6. When the Input Mask Wizard window appears… …make sure the Phone Number input mask is selected. Then click in the Try It: box. WORKING WITH TABLES 70 7. In the Try It box, type: 2125551515 8. Click the button. WORKING WITH TABLES 71 9. When the next screen appears, click the button. WORKING WITH TABLES 72 10. When the next screen appears, click the radio button in front of With the symbols in the mask, like this. Then click the button. WORKING WITH TABLES 73 11. When the final screen appears, click the button. The Input Mask for the Phone Number field should now look like this: WORKING WITH TABLES 74 12. On the Menu Bar, click View, then Datasheet View. 13. When the alert window appears, click the button to save the Customers table. 14. Use the TAB key to move across the first record until you are in the Phone Number field. WORKING WITH TABLES 75 15. Type: 3015551212 16. Press the TAB key four times to move to the next record. The new phone number has been saved. 17. On the Menu Bar, click File, then Close. You should return to the FoodStore1 database window. WORKING WITH TABLES 76 Adjust table layout 1. Double-click the Customers table to open it. 2. Place your cursor on the divider between the Company Name and Address 1 column headings. WORKING WITH TABLES 77 The cursor should turn into a double-headed arrow: 3. Double-click. The Company Name column should resize to fit the widest piece of data in that field: Tip: Instead of double-clicking, you can also drag column heading dividers to set column widths manually. 4. Double-click the divider between the Address 1 and Address 2 column headings. The Address 1 field should resize to fit the data in it. 5. Click the window’s button to close the Customers table. 6. When the alert window appears, click the button to save changes. WORKING WITH TABLES 78 Create new tables 1. Double-click Create table in Design view. WORKING WITH TABLES 79 A blank table will open in Design View: WORKING WITH TABLES 80 2. In the first box in the Field Name column, type: Order ID then press the TAB key. It should look like this: 3. On the Toolbar, click the icon. WORKING WITH TABLES 81 4. When the Save As window appears, type: Orders in the Table Name box. 5. Click the button. 6. When the alert window appears, click the button. Tip: You’ll assign a Primary Key later. WORKING WITH TABLES 82 Specify data types Assign the AutoNumber data type 1. In the Data Type column beside the Order ID field, click the drop-down arrow. When the list appears, click AutoNumber. 2. In the Description column, type: This is the Generic Order ID Number then press TAB. Tip: Filling in a Description is optional, but it helps you to remember what sort of information is supposed to be stored in a field. WORKING WITH TABLES 83 Assign the Date/Time data type 1. In the Field Name column, in the second row, type: Order Date then press TAB. 2. In the Data Type column, click the drop-down arrow. 3. When the menu appears, click Date/Time, then press TAB. WORKING WITH TABLES 84 4. In the Description column, type: Date the order was placed then press TAB. WORKING WITH TABLES 85 Employ the Lookup Wizard 1. In the Field Name column, type: Customer then press TAB. 2. In the Data Type column, click the down arrow, then Lookup Wizard … What does the Lookup Wizard do? The Lookup Wizard allows you to create a field where database users can choose from a list of things. This list ca