Creating Relationships in Access

Please follow the instructions below




Students Last & First Names Combined
Majors
GPA

Splitting a column in Excel to prepare the data for use in Access

  • Download and open the file 'Students Last & First Names Combined'
  • The Excel data has the first and last names together in the same cell, you will now separate them
  • Click the 'Data Tab'
  • Highlight the column with the last and first name together
  • Click the 'Text to Columns' button and the 'Convert Text to Columns Wizard' appears
  • On Step 1 leave the defaults and click 'Next'
  • On step 2 uncheck the 'Tab' check box and check the 'Space' check box
  • Click 'Next'
  • Click 'Finish'
  • On the 'Do you want to replace the contents...' pop-up window click 'OK'
  • Change the columns headings to 'First' and 'Last' as they correspond to the columns
  • Open PowerPoint and copy and paste into a blank slide the first 20 lines of this spread sheet
  • Save the newly formatted Excel spreadsheet by going to 'File' then selecting 'Save As'
  • Save it to your USB drive or to the Desktop and name it 'Students'
  • Import this file into Access by following the steps below
  • In a new blank Access database click the 'External Data Tab'
  • On the 'Import & Link' group select 'Excel'
  • On the 'Select the source and destination of the data' window click 'Browse'
  • Navigate to where you saved the 'Students' Excel file
  • Highlight the file you wish to import and click 'Open' then click 'OK'
  • The 'Import Spreadsheet Wizard' is opened
  • Click 'Next'
  • Click 'Next'
  • Click 'Next'
  • Click 'Finish'
  • Click 'Close'
  • Import the Majors and GPA Excel Spreadsheets Into Access

  • Download the Excel files Majors and GPA by clicking on the links
  • Open the Excel spreadsheets
  • Save them locally to your USB device or the Desktop
  • To save them click 'File' then 'Save As' and navigate to your USB drive or the desktop
  • Now import the files into Access by going to the 'External Data Tab'
  • On the 'Import & Link' group select 'Excel'
  • On the 'Select the source and destination of the data' window click 'Browse'
  • Navigate to where you saved the Excel files Majors and GPA
  • Highlight the file you wish to import and click 'Open' then click 'OK'
  • The 'Import Spreadsheet Wizard' is opened
  • Click 'Next'
  • Click 'Next'
  • Click 'Next'
  • On the 'Import to Table' box name the table GPA or Majors as appropriate
  • Click 'Finish'
  • Click 'Close'

Create relationships between all tables
  • Click on the 'Table' tab
  • Select the 'Relationships' button
  • Add each table to the 'Relationships' window
  • Create the relationships by clicking and holding on the 'ID1' in the GPA table and dragging it to the GPA in the 'students_names_together' table
  • Create another relationship by clicking and holding on 'Major' in the 'students_names_together' table and dragging it to the 'ID1' in the 'Majors' table
  • The relationships should look like the image below
  • Create a query that displays last name, first name, major, and GPA
  • Click the 'Create' tab and select the 'Query Wizard' tool
  • Select 'Simple Query Wizard' and click 'OK'
  • In the 'Tables/Queries' drop down menu select 'students_names_together'
  • Highlight the 'Last' name and click the right facing arrow the 'Last' name should appear under the 'Selected Fields:' box
  • Highlight the 'First' name field and click the right facing arrow
  • Return to the 'Tables/Queries' drop down menu and select 'Table:Major'
  • Highlight 'Major' and click the right facing arrow so it appears in the 'Selected Fields:' list
  • Return again to the 'Tables/Queries' drop down menu and select 'Table:GPA'
  • Highlight 'GPA' and click the right facing arrow so it appears in the 'Selected Fields:' box
  • Click 'Next'
  • Click 'Next'
  • Name the query 'GPA Query'
  • Click 'Finish'
  • Copy and paste the first 10 lines of this query into a new blank slide of your PowerPoint presentation
  • Next you will find the History majors with a GPA greater than or equal to 3.5
  • View the query in 'Design View'
  • Under the 'Major' heading in the 'Criteria' box type "=history" without the ""
  • Under the 'GPA' heading in the 'Criteria' box type ">=3.5" again without the ""
  • View the query in 'Datasheet View'
  • Copy and paste the query results into PowerPoint onto a blank slide using the 'Use Destination Theme' as the paste option
  • Hover over the paste options to see their names
  • Create a report containing the data from above
  • Highlight the 'GPA Query' you just created
  • Click the 'Create Tab'
  • Select 'Report'
  • View the report in 'Design View'
  • Make the report fit on one page by shrinking the 'Report Header' labels and the 'Page Header' labels by highlighting the box and moving the cursor so it forms a two headed arrow on the side of the box click and move the box to shrink it
  • Remove the '=Time()' box by highlighting it and right clicking and selecting 'Delete'
  • Delete the 'Report Footer' by highlighting it and right clicking and selecting 'Delete'
  • Export the report by selecting the 'External Data' tab and then selecting 'More' on the 'Export' group
  • Select 'HTML' document
  • Name the report 'Homework 12' and save it to the 'Desktop' or your USB drive
  • Check the 'Open the destination file after...' check box
  • Click 'OK'
  • Save the report by clicking on the 'GPA Query' tab that has the report on it and selecting 'Save'
  • Turn in only the PowerPoint presentation as an attachment for your homework

  • In the subject line for your email, include the assignment number, your name and the last 4 digits of your RunnerID in this form:

    Homework 12, 1234, Samantha Spade

    (Replace Samantha Spade with your name and 1234 with the last four digits of your student ID). The subject line must be exactly in this format (including capital letters and commas) in order for my grading script to download your assignment correctly, so please follow these instructions carefully.

    Errors in your homework grade must be worked out with your instructor within one week of grades being posted. You will be asked to show the assignment in the sent email box with the attachment as proof that you submitted your work on time.

    Remove your USB drive and take the drive with you when you leave. This should be the last action when you leave the lab every time. The lab computers are wiped clean every night and all data stored on them is deleted. USB drives left plugged into the computer usually find a new owner.