Unit 12 - Creating Relationships in Access

Creating a student and GPA database

Import these files into your Access Database
Students Names Together

Working with Data

Not all data is presented in the format desired. Open the 'Students Names Together' table from above and note how the first and last name of the students are in one cell. Too sort by last name we have to split the data into two fields.

o Import the 'Students Names Together' file into Access. Note how you cannot separate the data where the first and last names are in their own field. The best option is 'Comma' but the 'Full Name' field has both the first and last names. We can fix that. Sometimes it is better to perform different tasks in different office products. Access does not have a simple way to separate the 'Full Name' field so we will transfer it to Excel where it is much easier to separate the names. See 'Lab Manual 11' for directions on how to import a file into Access
o Transfer the data in the Access database into Excel. Be sure to paste it into cell A1
o Click the 'Data Tab'
o Highlight the column to be split
o Click 'Text to Columns' button

o Leave the defaults on the 'Delimited' option & click next
o On the screen that lets you set delimiters check the 'Space' box and uncheck the 'Tab' box. Set 'Text qualifier' as none. Click 'Next'
o Under the 'Column data format' choose 'Text'. To do this click the title field and select 'Text' for both the first and last names. Click 'Finish'
o Click 'Yes' on the popup window asking if you want to replace the data already there
o The column should be split into first and last name
o Remove any rows added by Excel there should only be field headings and data below
o Select the ENTIRE spreadsheet and copy it
o Return to Access and paste the information
o Change the field names to 'First Name' and 'Last Name'
o Below you can see the freshly pasted data. Note the total number of records is 1000 if it is not then there were issues with the first two rows of the Excel table. Also note how the names are separated but the field headings need to be changed to 'First' and 'Last'. Go to 'Design View' to do this

Finding the students on the honor roll

o Import the GPA.txt file into your Access database
o Click the 'Data Tab'
o Click the 'External DATA Tab'
o Click the 'Text File'
o Navigate to where you saved the GPA.txt file. Highlight it and click 'Open'
o Click 'OK'
o Leave the 'Sample data...' window with the defaults & click 'Next'
o Check the 'First Row Contains Field Names' check box & click 'Next'
o Click 'Next' on the following page
o Click the 'Let Access add primary key' radial button and leave 'ID' in the option box as the primary key
o Click 'Finish' & then 'Close'

You now want to establish relationships. As a quick review 'Relationships' connect a field name in one table to the field name in another table. To do this first click the 'Database Tools Tab' and then click the 'Relationships' tool. This will create a new tab called 'Relationships'. The initial creation of the 'Relationships' uses a pop-up window for you to create relationships. Add all the tables from above. Or you can grab the table from the side bar and drag it to the 'Relationships' window. Now click on Student ID in GPA and drag it to Student ID in the Students table. Next drag Student ID from the Major table to the Student ID in the Students table.

Creating a Query to see students GPA and Major
o Make relationships between each of the tables. All of the 'ID' should be connected
o Select 'Query Wizard' from the ribbon
o Select 'Simple Query Wizard'
o Under the 'Students' table add 'Last Name' and 'First Name'
o From the 'Major' table select 'Major'
o From the 'GPA' table select 'GPA'
o Click 'Next'
o Save it as 'Students Query'
o Click 'Finish'

Finding the average GPA for History majors

o Click on the 'Create Tab'
o Then select 'Query Design'
o On the 'Show Table' pop-up window select 'Queries'
o Select the query you created in the above example 'Students Query'
o In the 'Field:' box dropdown menu first column select 'Student ID'
o In the second column 'Field:' box select 'Major'
o In the third column 'Field:' box select 'GPA'
o In the second column under 'Major' place your cursor in the 'Criteria' row and type "=history" (don't include the " ")
o Click 'Run'
o A table with only history majors is created
o Create a new query using 'Query Design'
o Add the 'Students' query
o Select the 'Design Tab'
o Place your cursor in the 'Sort:' box
o Go to the ribbon and select the summation symbol and click it
o A new row is created titled 'Total:' select the dropdown box in the 'Total' cell and select 'AVG'
o Click 'Run'

o A single cell contains the average

Switching between Microsoft Office programs is common. Different programs are better than others for certain tasks. You could work with the data here in Access, move it to Word to add to a document, work with it in Excel, or return to Access to create a report. Don't forget PowerPoint too.

Finding the average 'GPA' by 'Major': Method 2 Using Excel

o Create a query to select for GPA and Major
o Copy and paste the results into Excel. Excel adds a title to the first row delete it
o Sort the data on 'Major' in ascending order
o Format the numbers to two decimal places
o In excel select the 'Data Tab'
o Then select 'Subtotal' it is on the right side.

o The 'Subtotal' popup box appears (see below)
o In the 'At each change in:' box select 'Major'
o In the 'Use function' box select 'Average'
o In the 'Add subtotal to: box select 'GPA'
o Click 'OK'
o Initially all 1000 rows will be displayed. Press ctrl + end to jump to row 1000 and ctrl + home to jump back to cell A1.
o Look on the left side of the window and click the number '2'
o This will show just the averages for each 'Major' (see image below).
o Highlight the cells for 'GPA' and 'Major'
o Press F5 this will bring up the 'Special' dialogue box and click 'Special'
o Select 'Visible only'
o Click 'OK'
o Press ctrl + c (the keyboard short cut for copy)
o Open a new 'Sheet' in Excel
o Click in cell A1 and right click and under paste select 'Values'
o The Major column looks bad with all the 'Average' after the major. Eventually a title can be added to communicate the tables content. This is one option to make a slide look neat
o Press ctrl + h the keyboard shortcut for the 'Find and Replace' dialogue box (see below)
o Enter 'Average' in the 'Find what: box'
o Leave 'Replace with:' empty
o Press 'Replace' two or three times to see that it only replaces 'Average' then click 'Replace All'
o Format GPA to two decimal places
o Save the file as 'Average GPA' with a file type of 'Unicode Text' (*.txt)
o Click 'Yes' to save the active worksheet
o Click 'Yes' to keep the format
o Import this file back into Access as a table

Creating a form to enter data into the database

o Import the 'Students' table using the method from 'Lab Manual 11' being sure to let Access define a primary key. It is necessary here to have that key. It is the same one as the original students table but Access does not recognize the original one that came with the .txt file. For simplicity now delete the 'Student ID' column. Accss will give you warning just click yes
o Click 'Create'
o Select the 'Form Wizard'
o Select the table 'Students'
o Click the '>>' to import all the fields into the 'Selected Fields:' box
o Click 'Next'
o Select the 'Columnar' layout
o Click 'Next'
o Click 'Finish'
o The first record is shown. You can view all the records in this fashion but there are 1000 of them
o Press Ctrl + end to jump to the end of the list
o The cursor should be at the end of the 'Zip' field. Press tab
o A blank form is presented with '(New)' in the 'Student ID' box
o Press the 'Tab' key the as soon as you enter some data into the 'Full' field the 'ID' changes to the next unique value
o The cursor is now in the 'Name' field. Enter 4 new students who are from Bakersfield
o Close the 'Students' table and reopen it to see the added students
o Notice the increase in 'Records' at the bottom of the image

Creating a report

o Use the 'Students' table from above where you added Bakersfield students
o Filter for all of the students from Bakersfield
o Click the 'Create Tab'
o Click 'Report Wizard'
o Add all the fields from the 'History' query
o Click 'Next'
o Click 'Next'
o Click 'Next'
o Click 'Next'
o Click 'Finish'
o If you see #### the box is too small for the values be sure to make it bigger
o Right click the report and select 'Design View'
o Make the report look nicer by making the data fields closer together
o Highlight a box and use the arrow keys to move it around
o Click and grab a side of the text box to increase or decrease the size of the text box
o Change the title
o Change the date and time format to mm/dd/yyyy and remove it from the bottom and place it in the report header
o Play around with the form in 'Design View' to make the report look nice and represent the data it contains

Remember that you can export the report to add to a PowerPoint presentation, Word document, or other file.