Introduction to Microsoft Access XP

1. Introduction to Microsoft Access XP Westport Continuing Education Taught by Alan Weaver

2. Introductory Note to Students • This handout is a guide and not an instructional manual. There are many good books out there, check your local bookstore or library. “Dummies” books are an excellent resource and answer many advanced questions. • The instructor may act like he knows everything, but he doesn’t! If he doesn’t know, he’ll figure it out (hopefully!) • There are 4 versions of Access available, 97, 2000, XP, and 2003. Although they work similarly, 2000 and up are cross compatible and look quite similar. A newer version will not open in 97 unless it is converted. If you wish to convert, go to Tools, Database Utilities, Convert Database, To Prior Access Database Version (it is menu driven and very easy to figure out). A 97 file will open in the newer versions but will be converted into a new file. • Information is contained in the following website: www.big-pic.net/access

3. Creating a Database Upon opening the program, you get the taskbar at left which gives us choices, to create a new Blank Database (which is what we will do), Templates, or an existing database. The first prompt you get is to give the database a name. Make sure it is saved to the floppy (A) drive. If you wish to save to the hard drive (desktop or C drive), make sure you put it on a blank diskette when done. Note: as Access can take up a lot of memory, it is recommended that files be saved at the office or home on the hard drive. Note: the above image is from Windows XP…notice the rounded corners. Dialogue boxes may appear slightly different depending on your operating system.

4. The major components of the database In the beginning, the database is an empty shell. The first four objects at left will be discussed (click on any of them to open) Tables are where the data is stored. Queries are the questions you ask the database (you are asking for specific information). Forms are where you can do data entry. Reports are what you generate as the results of your query, the data easier to read and more presentable. Note: the above image is from Windows XP…notice the rounded corners. Dialogue boxes may appear slightly different depending on your operating system.

5. Tables This is where all the data is stored such as inventory, members, etc. in your database. On the Table tab, double click on “Create Table in Design View” This allows you to assign properties to the field, its name, etc. Note: the above image (and all subsequent images) are from Windows 2000…notice the square corners. Dialogue boxes may appear slightly different depending on your operating system.

6. Tables – Creation As soon as you type in the first field name, the screen becomes active. You will see spinners (upside down triangles) that allow you to make choices. The example here shows the different types of data you can have: Text – words onlyMemo – has infinite capacity (within reason of course)NumberDate/TimeCurrencyAutoNumber (can assign a unique ID value to each record)Yes/No (good for toggling values) (a check box is created)Ole Object (an image)Hyperlink (takes you to website) This table has not been saved yet, it is called “Table 1”, give it a specific name Additional properties of the field which allow formatting of #, field size, etc.

7. Tables – Set Up Field Name - each field has a unique identifying name Data Type - these are the speci-fications of the field: #, text, etc. Descriptive information or additional commentary can be put in this section (it doesn’t show up in the table) As you select each section to the left, a description shows up on the right side, and the properties such as field width, # of decimal points, etc. can be applied. If all features of Access have been installed, you can also format for zip codes, phone #s When you are ready to save, this dialogue box appears, give it a descriptive file name

8. Make sure you have a primary key before you save! This is a unique identifying field (i.e. social security #, credit card #, etc.) Is a primary key specified here? (no, see next page) Click on the Key (on toolbar, see next page) to assign it. Note the little key next to ID#. If you need to add the row, highlight row and right click. Data entry can be done from either datasheet view or from a form you create. If it isn’t saved, you will be prompted to save the design. Primary Key

9. Clicking on the key above, while in the correct row allows user to set up the primary key. A primary key is not required, but is necessary when working with multiple tables or when you wish to link tables. Primary Key ID# (field name) shown above is the primary key

10. Data Entry Data entry can be done on this screen or in a form which we will further discuss. Press the tab key after keying in each entry. This is the status bar, it allows you to move from one record to the next, from the first to the final record. It also indicates how many records there are, right now we are in Record 1 of a total of 1 fields..

11. Data Sheet View Like Excel, the columns can be narrowed or widened. Holding the mouse over any of the icons shows a “tool tip”, what the function is. Some special features include: A-Z – alphabetizesThe “funnel” allow you to filter by selection or to revert to the original databaseBinoculars allow you to search for information (great if you have a lengthy table)Add/Delete records

12. Importing Data Data can be imported from other sources. For example, you may be able to obtain data from a proprietary program at your organization or may wish to take it from a text or even an Excel file. Begin by clicking on File, Get External Data, Import

13. Importing Data 1. Click on the drop down arrow to open the file you wish to import. We will be working with the floppy drive. 2. You can also look for different types of data by clicking here. 3. Click on the Import button after selecting the file you wish to import.

14. Importing Data The Import Text Wizard appears, follow the steps slowly and carefully. Often, the text will be delimited with tabs in between. Make sure the box First Row Contains Column Headings is checked. Click on the Next button

15. Importing Data The wizard knows how to import the data from the Excel spreadsheet. Make sure you pick the correct table that you are importing the data to. Click on the Next button. Tip: If you are adding the data to a new file that is already existing, make sure you have the field names match exactly! Otherwise, it will not work. In addition, you cannot import dissimilar information - an 8 column spreadsheet cannot be imported into a 5 column Access table. If information does not match, import the information into a NEW table. Note: You can see how the data will appear in the bottom half of this screen.

16. Importing Data This is the final step of the procedure. Click on the Finish button. The name of the file is indicated in the wizard. Error Messages If the data doesn’t match exactly, you will get a prompt like this. Review your data to make sure that data matches up perfectly.

17. Importing Data - Results & Problems • Problems MAY (and most likely) will occur! If any occur after doing an import, you will get a file called “Data Import Errors” - this will show problems that may have occurred. • For example, if the data is improperly formatted, i.e. you have a text field going into a currency field, the data will not come through properly. • It is suggested that the text file be re-saved as an Excel file (with proper formatting) to avoid these problems. • We will use the Excel version of this file - you can put text in a currency defined field (“not disclosed” for price) but it will give you a message when you import the file into the database, eliminating the incorrect data. • It is recommended that the data be imported into a NEW data table and verified against the source data for accuracy. The information can be copied and pasted into another table.

18. Queries A query allows you to ask for information about the data, for example, the age or sex of the patient or the states that had sales of facilities. To create a query, click on the query object in Access, and click on Create Query in Design View. This window will pop up. Select the table(s) you are querying, and click on add. Click on Close to exit from this window.

19. Queries You can size and adjust the boxes/borders so that you can see everything. Work in a maximized view. In this query, we are looking for all facilities in Minnesota that sold for over $1,000,000 Double click on the words at the left to add them to the query. Or click where the field name is and a drop down menu appears. Criteria is the information we are searching off of. Quotes appear around text entries, use the >, <, = symbols to refine your search. Click on the red exclamation to run the query.

20. Query Results Two facilities match the results of this query. As you can see by name, query has NOT been saved! Query will be named Minnesota Facilities – see below dialogue box.

21. Forms Forms are an easier way to perform data entry. They can be based on a table or a query. Click on the Forms Object. Double click on “Create Form by Using Wizard.” Specific or all fields can be requested for the form. Clicking on the double arrow will insert them all. Click on the next button

22. Forms Preview and select the type that you want to use. Click on next. Again, preview the style that you want, click on Next button.

23. Forms A name is suggested, based on the table or query it is derived from. It is recommended that you match the forms, etc. to the data table or query it is based on. Click on finish. A sample appears below. Data entry can be done in this screen. It is much easier to do this work than in a table. Appearance can be modified in design view.

24. Drop down menus • Drop down menus are quick ways to enter data in a consistent fashion, i.e. for state lists, categories, etc. This is set up in the design view of the table. • Click on the “Lookup Wizard” as shown. • You can create your own list manually by typing it or by basing it on data in an existing table. • These can be used in data sheets or forms.

25. Drop down menus • Fill in the blank column, typing the entries in the blank box shown at right. • When finished typing, click on Next • The next prompt wants to know what field to put it into. Click on Finish. • Drop down arrow appears in the Access table or form. (below right)

26. Drop down menus • You can also set this up in the design view of the table. Click in the proper field at the top and click on the Lookup tab for field properties. • This example shows a list being created for cities. • The Lookup Wizard is often a simpler option. • The form below right shows drop down arrows. One can make a selection instead of typing during data entry.

27. Reports Click on Reports object. Double click on “Create Report by Using Wizard.” Note how you can select from various queries or tables by clicking on the spinner. Wizards are an effective method to quickly generate a report. Click on the arrows to select the fields you wish to place in the report, the double arrow allows all records to move over, the arrows at the bottom allows you to move them back Mailing labels can also be generated.

28. Reports This allows you to do grouping in categories, etc. This is a great way to help arrange lengthy reports. In addition, you can sort the information

29. Reports Clicking on the various choices allows you to preview what the report will look like. As you progress, click on the “Next” button You can select a different style for your report and preview by highlighting your choice.

30. Reports All tables, queries, reports, etc. require names. A name is based on the query it is generated from. It is good to have the report names match the query name. Click on the “finish” button and the report is generated. See below. Report can be customized or modified in design view.