Access all areas

Windows helps you figure out when to dump Excel and employ Access instead to best meet your data management needs...

  • E-Mail
By  Matthew Wade Published  December 3, 2006

|~|access---m.gif|~||~|A great many people use Excel to manage their data, but in some cases Microsoft Access can do this job quicker and more efficiently. To explain, Excel is not in essence a database management system. It is a spreadsheet program, and as such it stores units of info (cells) in rows and columns to make up work-sheets. The most common task performed in Microsoft Excel is used for is arguably the management of lists such as telephone numbers as well as personnel data. In comparison, Access stores data in tables. These look much the same as worksheets but are designed for complex querying; asking questions of data stored in other tables and locations, and even in fields in other tables. Thus, databases are much a more powerful tool than spreadsheets. Here are a few of the actions that you can perform on a database that would be difficult, if not impossible, to perform using Microsoft Excel: * Retrieve all records that match certain criteria * Update records in bulk * Cross-reference records in different tables * Perform complex aggregate calculations. More importantly still, databases keep your information safer. Microsoft Excel saves all your data in memory, which means that any unsaved info is likely to be lost if your PC crashes. Databases on the other hand write data to your hard disk right away. Also, Access is designed to handle huge amounts of data. Excel can technically handle more than 60,000 rows of data, but it does so to the detriment of your PC’s performance. Last but not least, one final advan-tage of choosing Access is that it can easily link tables of related data together, such as customers and their orders for instance, or employees and their expense details. If you’re in any doubt regarding which Microsoft application to employ, simply check out the box-out to the right. Getting started with Access 2003 The following tutorial will show you how to create a simple two-table database in Access 2003, link the two and then design queries to return the info you need. Starting off Before you begin, figure out what data you want to store and how you intend to store it. For this workshop we’ll walk you through to creating a database of university courses. Here’s a list of the data types that the database will include: • Course ID: This is an integer ( or number, e.g. 101) • Course department: A short string (e.g. MATH) • Professor name: Short string (e.g. Johnson) • Content description: Paragraph of text (e.g. Math 101 will cover…) • Course location (i.e. room number): Short string (e.g. East 315) • Can the course be taken pass/fail? Yes/No. Creating the database First off open Access, navigate to File menu and hit File/New/Blank Database. Enter a name for your database and save it to the appropriate directory. Access will now open a window for the database, but no tables will yet be shown. To create a new table, double-click ‘Create Table in Design View’ and a blank table will appear. You’ll notice that there are three main areas here: the field name, the type of data in that particular field and a description of the data. Note: The latter area is used to enter comments. The first thing you have to do is create an entry that will serve as the primary key for this table of data. A primary key must be unique to each piece of data in the table. It basically helps the database to keep everything organised. We recommend that you create an entry just for this purpose and not use one of the pieces of data you actually want to store. To do this, enter ‘ID’ into the field name in the first row. Next, click the ‘Data Type’ field. You'll now see a pull-down list of the various data types Access supports. Now select ‘Autonumber’. This will create a field that starts at the number one and automatically increments by one every time you enter a new piece of data. For example 101,102 and so forth. Next, make the ‘ID’ field the primary key by right-clicking it and selecting 'Primary key' from the pop-up menu. A small key will now appear next to this field. (Once you assign a primary key you cannot change it. This means you’ll have to start your table from scratch if you want to change your key.) Now it’s time to enter your database’s first data field. It doesn’t matter what order the various pieces of data are in. You can list course number or the department next; Access won’t care. We’ll start with the course number. Enter a field name of ‘Course_Number’ and select a data type of 'Number' from the pop-up. Note: the Field Properties dialog at the bottom of the window changes to the settings needed for a number. These include: • Field Size: Set this to ‘Long Integer’ if you are storing an integer or ‘Double’ if you need to store a number with a decimal point. • Default Value: Are most of the data entries going to be the same? If so, enter that particular value. • Required: Is this field absolutely necessary? If this is set to Yes, Access will not accept entries that lack this field. In our case, the course number will always be an integer so a Field Size of ‘Long Integer’ is fine. All courses must have a course number, so well set Required to ‘Yes’. Next, enter the course department. Create a new row with Field Name ‘Department’ and of type ‘Text’. We now have a new set of properties specific to text fields at the bottom of the page. For our purposes, we're using a short department code for this field (BIO, CHEM, MATH, etc) so we can set the length to ‘5’. All courses have to relate to a department, so set the 'Required' field to Yes and ‘Allow Zero Length’ to ‘No’. The Professor item is similar. First, create a required field with name ‘Professor’ and type ‘Text’. Be exact For the course description, a simple text field won’t suffice, so create ‘Description’ and choose ‘Memo’. Next, create a field called Course_Room use a data type of text. Finally, create a field called ‘Allow_ pass_fail’ and choose ‘Yes/No’. We’ve now finished the design of our database and we can save the table. Assign it a name such as ‘Courses’, save it and close it. Access will now display a window showing the table. (Note: if you want to go back and edit the design of the database, click once on the table in question and click ‘Design’ button at the top of the table list). To enter data, double-click the name of the table you want to enter it into. Don’t enter anything in the ID field as Access will take care of that. Enter a course number into the Course_Number slot and continue entering data for the rest of the fields. Note: as soon as you start filling the row, Access puts a number in the ID block and creates a new entry below the one you are editing. If you leave a cell blank that has the ‘Required’ flag set, Access will not let you continue to enter data until you fill it in. Try this out by leaving Department blank and moving on. Now enter at least four lines of mock/sample data so that we can design a query for the information. Querying data Databases are designed to allow the rapid searching of large amounts of data, so let’s create a query that will let us find the pieces of data that we are interested in. Click the ‘Query’ button on the side of the tables list and then select ‘Create Query in Design View’. Access will ask you what tables you want to draw data from, which in our case is the Courses table. Select this and click ‘Add’. A small window will appear in the background. Click close to close the box. You will now see the query design table, in which you have to enter data to create a query here. Let’s say that we want a print-out of all of the english courses along with the relevant professor name and room number. Begin by putting the name of the field you want to search into the ‘Field’ block of the first column. You should get a pop-up menu listing all of the fields in your database. Select Department. The ‘Table’ field will be automatically filled in by Access since we only have one table present. You can choose to sort in ascending or descending order in the next box. The ‘Show’ option determines whether the data appears in the query’s results. (Note: It should be checked by default once you enter a field.) For the second column, enter ‘Course_Number’ for the field, click the ‘Show’ box and leave the ‘Criteria’ option blank. This tells Access that we want it to show this field but that we don’t want perform a search on it. Repeat the same process for professor and the room number. Now close the query design box and enter a name for your query. The query will now be listed in the main Access window. Double-click it to run and see the results. How about if you wanted to search for multiple terms? In this case, simply fill in the criteria field for each term you want to use in your search. For example, if we wanted to list all the english courses taught by Sarah, we’d fill in Sarah in the criteria field for the professor column. This would return only english courses assigned to Sarah. Linking tables You’ll notice we’ve used the building name plus the room number for the location of each course. For instance, for Course 102 we’ve used Kent 659. If you think about it, a room should really be an entity of its own; it has a building, number, and a number of other possible stats such as the number of seats and the type of equipment located in it (e.g. projectors, PCs). Currently our database doesn’t deal with these variables very well. Let’s fix this then by creating a second table and linking it to the first one. Open the ‘Courses’ table in ‘Design View’ and delete the row for room. Now create a new row called ‘Room’ and make it of type text. Don’t worry about entering the data for now. Save your changes to the table and close it. Now, create a new table in design view and create rows for the following items: • ID: text, primary key • Building: text • Room: long integer • Seats: long integer Close the design view and name this table ‘Room’. Go ahead and enter some sample data into it. For the ID, enter MR315 or SM300. Remember that this is a primary key. Now re-open the ‘Courses’ table and enter the IDs you chose for the rooms in the ‘Room’ field in this table. Close the table when you’re done. The next step is now linking the ‘Courses’ and Room tables together. The Course table needs to have a link relating the room data such as MR315 to the data in the table of rooms. To do this, close all of the open tables and click the button on the upper toolbar. You’ll now be asked to choose the tables you want to include. Include both the courses and rooms tables. Both will appear as boxes in the Relationships window. Select the ‘Room number’ entry in the courses table list, double-click it and the ‘Edit Relationship’ dialog opens. To start, click on ‘Create New’. Select ‘Courses’ for the left table, ‘Room’ for the right table, ‘Room number’ for the left column and ‘ID’ for the right column. This tells Access that when you want information about the room MR315 to look in the entry with ID ‘MR315’ in the rooms table. Click OK followed by ‘Create’ to form the link. You will now see a black line connecting the two tables. Next, click the ‘Query’ button on the main window and select ‘Create Query in Design View’. This time, add both the course and the room tables to the query view. Let’s say we want to make sure that we don’t over-book a mathematics course. Therefore we need to know the number of seats available in each one. Start by choosing ‘Department’ again as the first field. Note that now when we look in the field list, it provides the name of the table first, so we have to select ‘courses.Department’ as the field to search. Set the criteria to ‘MATH’ again, then add fields for course number and professor. Now, we’ll enter fields on the other table. Choose ‘room.building’, ‘room.number’ and ‘room.seats’ into the next three fields in the query. Close the query and give it a name. Now run the query by double-clicking it. And you’re done. ||**||

Add a Comment

Your display name This field is mandatory

Your e-mail address This field is mandatory (Your e-mail address won't be published)

Security code