Unit 10 - Introduction to Access
Microsoft Access is a database management system (DBMS). In a DBMS you create a database with one or more tables. A table is very similar to a Worksheet in Excel but now each column is called a field. In Access a record is similar to a row in Excel. Access has sophisticated filtering and sorting capabilities beyond the abilities of Excel. Access is a relational DBMS, meaning you can establish relationships between tables. In this unit you will learn how to create an Access database and import data from a comma-delimited (csv) file into a table. With these tables you will then make relationships to manipulate data.
Importing Data into Access
You often need to import data from another source into an Access table. Access imports many file formats including the widely used format csv, Comma Delimited text file. In a comma-delimited file each line is a record. The fields in the line are separated by commas. A line with five commas corresponds to six fields. The number of commas in each line must be the same. Assume you have a file named contacts.txt with the format below. The file corresponds to a table with four records and six fields per record:
Cory,Smith,93429 Surrly St,Seattle,WA,689812
Santa,Claus,888 Igloo St,North Pole,AK,602677
Samantha,Spade,213 Falcon Ave,CA,98345
Georgie, Porgie,3451 Mountain Lane,MT,87631
When designing a table you start by naming the table and the fields. Such labelling creates metadata (data about the data). Table and field names should be simple and descriptive of the field's contents. Good field names for the above data might be: First, Last, Address, City, State, Zip.
Creating an Access Database
To open Access, double-click the folder on the desktop labeled 'Microsoft Office'. Double click the icon that says 'Microsoft Access 2010'. Select the 'File' tab then select a 'Blank database'. In the right pane at the bottom is the file name and where it will be saved. Click the tan file folder; it will bring up the 'File New Database' window. Select your USB drive and name the database. It is important to know where you are saving the file so you can find it later. The name should reflect the contents of the database. The 'Save as type:' field should say 'Microsoft Access 2007 Databases'. Finally click 'OK' then 'Create'. Access assigns the extension .accdb for you. You now have an empty database.
In the homework you will create a table by importing data from a comma-delimited file. When you use the import feature, the number of fields in the table is determined by the format of the imported file. You just need to label the fields.
Import these files into your Access Database
To import the data:
o Open the link above to the data. It should open a comma separated file
o Right click inside the data and select 'Save As' & save it to the 'Desktop'
o In Access on the 'External Data Tab' select 'Text File' this opens the 'Get External Data - Text File' window
o Next to the 'File name' box click the 'Browse' button on the right. Navigate to where you have saved the .txt file
o Select the file you wish to import into Access by clicking on it so it is highlighted. Click 'Open' then click 'OK'
o The 'Import Text Wizard' is opened and it will walk you through setting up the first table
o Leave the default radial button 'Delimited' and Click 'Next'
o On the 'What delimiter separates...' window check the 'First Row Contains Field Names' box. These are the titles of the fields. Then select the type of delimiter it will be either 'Tab' or 'Comma'. You know it is the correct one when the data below has the correct data in the fields. Study the .txt file to know the field names and how the data should look
o Click 'Next'
o Click 'Next'
o Select 'Let Access add primary key.' Click 'Next'. A primary key uniquely identifies a record in case there are two entries with the exact same data. For instance a Joe Smith and his son Joe Smith who both live at the same address. The primary key is usually an 'Auto Number' type. That means when you enter new data it will increment the input by one based on the last record. All of the .txt files used in this class come with 'ID' fields that are primary keys. However when importing them their data type is changed to 'Number' where 'Auto Number' is needed; this is why we 'Let Access add [the] primary key.'. Click 'Next'
o Lastly the 'Import to Table' window is opened. This is where you name the table. Use the default and click 'Finish'
o Click 'Close' on the 'Save Import Steps' page
o The file appears in the left hand window of Access. Double click it to have it appear in the main window
o Open the table in 'Datasheet View'
o Notice how the first two columns contain the same data
o Delete the second one. Its data type is number we need the data type to be 'AutoNumber'. You can do this in either 'Datasheet view' or 'Design View'. Highlight the field to be deleted right click and select 'Delete'
o The data type of 'AutoNumber' allows data from forms to be entered into the table and maintain the primary key of no duplicate values. It will number the field automatically
Linking Tables Together
One of Access' most powerful features is the ability to link tables. Databases are created because the representation of the data in a tabular fashion, like Excel, becomes redundant, hard to filter, and hard to search. Access breaks the data into more manageable chunks. In the data you imported from above you see a table with personnel data, one with sales data, another with data regarding files, and one with countries. Each table has been created with a field that shares the same value of at least one other table so that they can be linked. By creating links a query can access data in each of these tables. A query is a form of sorting, filtering, or modification of data. The image below shows how each table has a value that can be linked to the same value in another table. Here we see Employee_ID in the 'Sales' table links to the 'Employees' table 'id'. Also the 'Countries' table 'ID' field links to the 'Files' 'country_id' field. You can remove a relationship by clicking on the line connecting the tables to highlight it then right clicking and selecting 'Delete'.
To link tables
o Select the 'Home Tab'
o Under 'Table Tools' select 'Table'. Then select 'Relationships'
o On the left side of the screen click and grab the files you imported and drag them into the 'Relationships' window
o Click on the 'Field' you wish to connect and drag it to the field in the other table. Remember that the field in the destination table must match the original field
o The 'Edit Relationships' menu appears. Make sure the link is the correct one. Then click 'Create'
o Repeate this for the rest of the tables so the relationships window looks like the one below
Creating a Query
Lets create a query that will show the employees name, their user name, the usage of a file, and the files creation date.
o Select the 'Create Tab'
o Select the 'Query Wizard'
o Select the 'Simple Query Wizard' and click 'OK'
o Under the 'Tables/Queries' drop down menu select the 'Employees'
o In the 'Available Fields:' box select 'Last', 'First', and 'User Name'
o Go back to the 'Tables/Queries' drop down menu and select 'Files'
o In the 'Available Fields:' box select 'File Name', 'Usage' and 'Creation Date'
o Click 'Next'
o Click 'Next'
o Name the query 'Never Opened Files'
o Click 'Finish'
Sorting and Filtering
A powerful feature of a database system is the ability to sort and filter data. A filter selects records in your table and a sort arranges records in a table. Sorts and filters do not modify the underlying data they simply allow you to organize the data differently.
To apply a filter first place the cursor in the field the filtering is going to act on. Next click the 'Home Tab' and then select the 'Filter' tool. This will bring up a window where all of the unique entries in the field are listed. Uncheck the '(Select All)' check box and then click the 'Never' check box. Only those files that have 'Never' been accessed are selected. At the bottom of the table note the 'Filtered' indicator highlighted in yellow.
This indicates that the data is filtered and by clicking that button you can switch back to the unfiltered data.
Let's sort the table based on 'Creation Date'. Right click on the 'Field' name 'Creation Date'. Select the 'Sort Oldest to Newest' option. The list is now sorted by the date created. To remove the sort click the 'Remove Sort' button under the 'Home Tab' and the table will return to its original order (see below). Now sort the query first by 'Last' name and then by 'Creation Date'
Exporting to an HTML file
You often want to save (export) your filtered and sorted records into another file format for easy viewing. HTML (Hypertext Markup Language) is the format for web pages and is often a good choice. A file in HTML format can be viewed by any web browser and also maintains formatting.
To export all records to HTML:
o Under the 'External Data Tab' go to the 'More' drop down menu
o Select 'HTML Document'
o On the 'Export - HTML Document' menu click 'Browse' and go to your USB drive and click 'Save'
o Click the 'Export data with formatting and layout' check box
o Click the 'Open the destination file after the export operation is complete' check box
o Click 'OK'
o Click 'OK'
And up pops your list of names and addresses in the HTML format.