Introduction to Microsoft Access 2010 v. 2.0 October 2012 nikos dimitrakas SU/DSV Introduction to MS Access 2010 October 2012 KTH/ICT/SCS v 2.0 nikos dimitrakas Table of contents 1 Introduction ............................................................................................................................ 4  1.1 Microsoft Access .......................................................................................................................... 4  1.2 Prerequisites ................................................................................................................................ 4  1.2.1 Literature .............................................................................................................................................. 4  1.3 Structure ...................................................................................................................................... 5  2 The Case ................................................................................................................................. 5  3 The Access Environment ........................................................................................................ 8  3.1 Configuration ............................................................................................................................ 10  3.2 SQL ............................................................................................................................................. 10  4 Creating A Database ............................................................................................................ 11  4.1 Creating Tables ......................................................................................................................... 11  4.1.1 Design ................................................................................................................................................. 11  4.1.2 DDL .................................................................................................................................................... 18  4.1.3 Defining Other Restrictions ................................................................................................................ 20  4.2 Working With Relationships .................................................................................................... 24  4.2.1 Simple Foreign Keys .......................................................................................................................... 27  4.2.2 ISA Inheritance ................................................................................................................................... 30  4.2.3 Composite Foreign Keys .................................................................................................................... 33  4.2.4 Multiple Relationships Between The Same Two Tables .................................................................... 35  4.2.5 Recursive Relationships ..................................................................................................................... 35  5 Querying A Database - Working With Data ........................................................................ 37  5.1 Preparing The Database With Data ........................................................................................ 37  5.1.1 Using SQL .......................................................................................................................................... 37  5.1.2 Using Datasheets ................................................................................................................................ 39  5.1.3 Using Forms ....................................................................................................................................... 41  5.2 Writing SQL .............................................................................................................................. 41  5.3 Reusing Queries ......................................................................................................................... 43  6 Forms .................................................................................................................................... 45  6.1 Simple Forms ............................................................................................................................. 45  6.2 Lookups ...................................................................................................................................... 52  6.3 Master-Detail Constructs ......................................................................................................... 65  6.4 Forms Based On Queries .......................................................................................................... 77  6.5 Non-Data Forms ........................................................................................................................ 83  7 Reports .................................................................................................................................. 85  7.1 Simple Reports .......................................................................................................................... 85  7.2 Reports That Combine Many Tables ...................................................................................... 86  2 SU/DSV Introduction to MS Access 2010 October 2012 KTH/ICT/SCS v 2.0 nikos dimitrakas 7.3 Reports Based On Queries ....................................................................................................... 90  7.4 Grouping And Sorting .............................................................................................................. 91  7.5 Subreports ................................................................................................................................. 97  8 Macros ................................................................................................................................ 103  9 Other Useful Tips ............................................................................................................... 107  9.1 Tip 1 - Lookups For Tables .................................................................................................... 107  9.2 Tip 2 - Viewing Subtables ....................................................................................................... 108  9.3 Tip 3 - Sorting And Filtering ................................................................................................. 112  9.4 Tip 4 - SQL Parameters .......................................................................................................... 112  9.5 Tip 5 - Nesting SELECT Statements – COUNT(DISTINCT) ............................................. 113  9.6 Tip 6 - Application Start-Up .................................................................................................. 114  9.7 Tip 7 - Concatenating Columns ............................................................................................. 115  9.8 Tip 8 - Using Forms To Find Records ................................................................................... 116  9.9 Tip 9 - Keys And Indexes ....................................................................................................... 116  9.10 Tip 10 - Multiple Subforms .................................................................................................. 117  9.11 Tip 11 - Division In Access ................................................................................................... 118  9.12 Tip 12 - Object Dependencies............................................................................................... 119  9.13 Tip 13 - Copying Objects Between Databases .................................................................... 119  9.14 Tip 14 - Handling NULL ...................................................................................................... 120  9.15 Tip 15 - Business Rules ......................................................................................................... 120  9.16 Tip 16 - Set Operators .......................................................................................................... 122  9.17 Tip 17 - Multimedia .............................................................................................................. 122  9.17.1 Storage Outside The Database ........................................................................................................ 128  9.18 Tip 18 - Compacting And Repairing A Database .............................................................. 129  9.19 Tip 19 - Linking External Data ............................................................................................ 130  9.19.1 Creating An ODBC Alias ............................................................................................................... 131  9.19.2 Linking To The MySQL Tables From Access ............................................................................... 133  9.19.3 Working With Linked Tables ......................................................................................................... 135  9.20 Tip 20 - Working With Dates And Times ........................................................................... 138  10 Other Resources ............................................................................................................... 140  10.1 Web Sites ................................................................................................................................ 140  10.2 Books ...................................................................................................................................... 140  11 Epilogue ............................................................................................................................ 141  3 SU/DSV Introduction to MS Access 2010 October 2012 KTH/ICT/SCS v 2.0 nikos dimitrakas 1 Introduction This compendium contains an introduction to the most commonly used functionalities found in Microsoft Access 2010, from how to create a database and define referential integrity to how to create forms and reports. As an added bonus, most of the information in this compendium also applies to earlier versions of Microsoft Access. It is strongly recommended that you read through (or at least look through) the entire compendium before you start working with it in front of a computer. There are many references back and forth in this compendium, and therefore you should find it quite helpful to have acquired an idea beforehand of what is coming in later chapters. Any comments or feedback that you may have about this compendium are greatly appreciated. Send any such comments or feedback to the author at [email protected]. The latest version of this compendium, all the files needed to complete the tutorial, relevant links and other information are available at http://coursematerial.nikosdimitrakas.com/access/. 1.1 Microsoft Access Microsoft Access integrates a database management system and a rapid application development environment in the same package. It provides almost all basic relational database functionalities, and it extends this with facilities for rapid application development. Advanced development can also be done in Microsoft Access by using the also integrated Visual Basic environment. Microsoft Access 2010 is included in the list of software offered by Microsoft within the MSDN Academic Alliance agreement. This means that any student at KTH/ICT or SU/DSV is entitled to one free licence for MS Access 2010. If you want to download Microsoft Access 2010 (or any other Microsoft software covered by Microsoft Dreamspark), go to https://msdn60.e-academy.com/kgth_ite/ or https://msdn60.e-academy.com/su_ids/. From now on in this compendium we will refer to Microsoft Access 2010 as Access. 1.2 Prerequisites This is a tutorial about Access, so the reader must already be familiar with conceptual modeling, relational database theory and some basic programming. Later in this compendium we will start working with a small case. We will skip to having a ready conceptual model, so we will assume that some conceptual modeling of our case was already done. The translation of the conceptual schema into a relational database schema will be shown, but not in any detail. 1.2.1 Literature While working with this compendium it is recommended that you have some sort of reference literature on relational databases and SQL. Here are some recommended books:  Connolly, Begg: Database Systems A Practical Approach to Design, Implementation and Management, Addison Wesley  Elmasri, Navathe, Fundamentals of Database Systems, Addison-Wesley 4 SU/DSV Introduction to MS Access 2010 October 2012 KTH/ICT/SCS v 2.0 nikos dimitrakas There are many more books that will do just fine, but these two are mentioned here since they are the ones used for courses at SU/DSV and KTH/ICT. 1.3 Structure This compendium has the following simple structure: 1. A short introduction (which you are reading right now) 2. A description of the case used throughout the compendium (chapter 2) 3. Creating a database for our case in Access (chapter 4) a. Defining and creating the tables (section 4.1) b. Defining relationships and referential integrity (section 4.2) 4. Querying the database (chapter 5) a. Populating (putting some data in) the database so that we have something to query about (section 5.1) b. Writing SQL statements to query the database (section 5.2) c. Creating and using views (section 5.3) 5. Creating forms for input and for working with the data in the database (in a more user- friendly way) (chapter 6) 6. Creating reports for presenting data from the database (chapter 7) 7. Creating macros to do things that can't be done with just queries, forms and reports (chapter 8) 8. Finally there are some more tips and links to more information (chapters 9, 10 and 11) 2 The Case The case used in this compendium has been specifically designed in order to be both small and cover all the things to be discussed in the chapters to follow. The same case will be used for all the exercises in the rest of the compendium. The system we are going to build will manage the following information:  There are many artists, and for each of them we know their name and age. No two artists have the same name.  Some of the artists are also composers.  Composers compose songs. A composer never composes two songs with the same name.  For each composed song we know its name and length (in seconds).  Each song can be performed on particular dates. Each song performance can involve many different artists. The same song cannot be performed twice on the same date.  A particular performance of a song can be included on a CD. A CD can contain many different songs performed by different artists.  For each CD we know the order of the songs.  Each CD has a title and a year (when it was released). No two CDs released the same year have the same title.  A CD can only contain songs performed the same year or earlier (for obvious reasons).  Many artists have a mentor, who is another artist. The mentor must be older than the artist.  An artist can have a favorite composer. 5 SU/DSV Introduction to MS Access 2010 October 2012 KTH/ICT/SCS v 2.0 nikos dimitrakas The information above has been modeled into the following conceptual model: Figure 1 Conceptual model of the case The arrows on the conceptual model are only there to help read the associations, for example “A song is composed by a composer” instead of “A composer is composed by a song”. The only two things not modeled are the facts that “A CD can only contain songs performed the same year or earlier” and that "The mentor must be older than the artist". These will be handled as business rules, and we will see how we can add such restrictions in our database system later. Before we can implement our database, the conceptual model has to be translated into a logical relational database schema. In this schema we will still not specify any Access specific information. We will specify primary keys, foreign keys, data types, and other restrictions. The following figure shows the logical database schema created from the conceptual model. Primary keys are shown as underlined columns, while an asterisk (*) indicates columns that constitute foreign keys. The columns CD.Title, Song.Name and Artist.Name are of data type STRING (or VARCHAR). The columns CD.ID, CD.Year, Artist.Age, Song.ID and Song.Length are of data type INTEGER. The column SongPerfrormance.Date is of data type DATE. All foreign key columns are automatically of the same data type as the referenced columns. Remember that keys can be composite, i.e. consist of more than one column, and that there can be several foreign keys in a table, possibly even sharing some column(s). 6 SU/DSV Introduction to MS Access 2010 October 2012 KTH/ICT/SCS v 2.0 nikos dimitrakas Figure 2 Logical relational database schema of the case The schema of Figure 2 can also be shown in the following textual notation. The advantage of this textual notation is that the foreign keys are specified explicitly and there is no room for confusion. Tables (primary keys are underlined): Artist (Name, Age, FavouriteComposer, Mentor) Composer (Name) Song (ID, Name, Length, Composer) SongPerformance (Date, Song) ArtistPerformance (Name, Date, Song) CD (ID, Title, Year) CDSongPosition (Position, CDID, Date, Song) It is also possible to include the data types in this notation. The table Artist could be written instead as follows: Artist (Name STRING, Age INTEGER, FavouriteComposer STRING, Mentor STRING) Foreign keys (foreign key on the left, referenced primary (or alternate) key on the right): Artist.FavouriteComposer << Composer.Name Artist.Mentor << Artist.Name Composer.Name << Artist.Name Song.Composer << Composer.Name SongPerformance.Song << Song.ID ArtistPerformance.Name << Artist.Name ArtistPerformance.(Date, Song) << SongPerformance.(Date, Song) CDSongPosition.CDID << CD.ID CDSongPosition.(Date, Song) << SongPerformance.(Date, Song) 7 SU/DSV Introduction to MS Access 2010 October 2012 KTH/ICT/SCS v 2.0 nikos dimitrakas The first row says that the column FavouriteComposer of the table Artist is a foreign key to the column Name of the table Composer. For composite keys there is no difference: The last row, for example, says that the columns Date and Song of the table CDSongPosition constitute together a foreign key to the primary key of the table SongPerformance, namely the columns Date and Song. Other constraints: UNIQUE (CD.Title, CD.Year) UNIQUE (Song.Name, Song.Composer) In the next chapter we will see how we can create an Access database based on the relational database schema that we acquired earlier. Our case also includes the following information needs and user interface: 1. Show all CD titles produced in 1999! 2. Show all songs in a particular CD! 3. Which CDs include songs written by Jerry Goldsmith? 4. Which song has been performed the most times? 5. How many distinct songs has each artist performed in? 6. Which artist has performed in at least one song of each CD? 7. Which artist has performed in at least one song of each composer? 8. Which songs has each composer composed? 9. A form for registering a new CD in the database. 10. A form for registering a new Artist in the database. 11. A form for registering song performances and artists performing them. 12. A report that shows the content of each CD (back cover style). 13. A report that shows information about each CD including which artists and composers that are related to the CD. 14. A report that shows for each composer the songs that they have composed and which performances of them exist and in which CDs these performances are included. 3 The Access Environment As we mentioned in section 1.1, Access is both a database management system and an application development environment. Access uses the basic philosophy of the other products in Microsoft Office, which means that a database (and accompanying application) is stored in a file (similar to Word, Excel and PowerPoint). Access files use the extension "accdb". Creating such a file is the equivalent to creating a database (done in other products with the SQL command CREATE DATABASE). Starting Access without opening a particular file shows a welcome menu for creating a new file (a new database): 8 SU/DSV Introduction to MS Access 2010 October 2012 KTH/ICT/SCS v 2.0 nikos dimitrakas On the left side of the window there is the main menu and under "New" we have the Blank database template (and some other templates) which will create a new file. The file name can be specified on the right side before pressing "Create". Once a new database has been created, Access will automatically suggest that we create a table: On the top of the window we have the menu (File, Home, Create, etc.). Each option has its own toolbar and options will be active or inactive based on the current selection. The menu and correspondning toolbars is called "Ribbon". On the left side we have the object browser. Objects are not to be confused with objects in object-oriented programming. In Access we have six types of objects: Tables, Queries (views), Forms, Reports, Macros and Modules. All objects of these types will be shown in the object browser. The object browser is the control center of our database and is also known as the "Navigation Pane". From here we can open any table, query or other object in order to use it or modify it. The main area of the window (to the right of the object browser) is where we work with any objects we may open. 9 SU/DSV Introduction to MS Access 2010 October 2012 KTH/ICT/SCS v 2.0 nikos dimitrakas 3.1 Configuration Access and the current database can be configured from File > Options. Some interesting settings are available under Current Database and under Object Designers. 3.2 SQL Access is a relational database management system and thus supports SQL. But Access seems to encourage users to use wizards and graphical tools, so SQL is not really up, front and center. In order to write and execute SQL, we have to first create a query and then switch to the SQL view. A query can be created from Create > Query Design. Access will immediately suggest that we add tables to a graphical design of our query: After ignoring the "Show Table" pop-up window, we will have the option of switching to the SQL view either by pressing "SQL" on the ribbon (under Design) or by right-clicking on the query's tab selector and selecting "SQL View": Querys can be saved as database objects and each query can contain one SQL statement (SELECT, UPDATE, INSERT, DELETE, CREATE TABLE, etc). Saving SELECT statements as queries is equivalent to creating views using CREATE VIEW, which is not supported in Access. 10