Creating Relationships in Access

Please click on the 'Lab Manual' for detailed instructions.


Import these files into a new Access Database:
Students Last & First Names Combined
Major
GPA

Preparing the data for use in Access

  • Open the file 'Students Last & First Names Combined' with Excel
  • This is a text file saved as a comma separated file a .csv file so you will have to import it into Excel
  • Open Excel
  • Go to the 'File Tab' and click open
  • Navigate to where you saved the .csv file and open it
  • Two warning messages will appear click 'Yes' then click 'OK'
  • Now you should see the 'Text Import Wizard - Step 1 of 3' window
  • Leave the defaults and click 'Next'
  • In the 'Delimiters' check boxes uncheck 'Tab' and check 'Comma'
  • Click 'Next'
  • Click 'Finish'
  • The Excel data should populate a worksheet but the first and last names are together 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 'Last' and 'First' as the correspond to the columns
  • Copy and paste this data into Access
  • YOU WILL USE THIS NEW TABLE IN THE NEXT EXERCISE

Creating a query

Create a query that selects for all students, their major and GPA
  • Open Microsoft Access
  • Paste the above Excel data into a table in Access
  • Create relationships between all tables on the ID
  • Click the 'Create Tat'
  • Select the 'Query Wizard'
  • Select 'Simple Query Wizard' and click 'OK'
  • In the 'Tables/Queries' drop down menu select the names table 'Table 1'
  • Highlight the 'Last' name and click the right facing arrow the 'Last' name should appear under the 'Selected Fields:' box
  • 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'
  • 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'
  • There should be 7 entries
  • Create a report containing the data from above
  • Click the 'Create Tab'
  • Select 'Report'
  • Make the report look nice
  • View the report in 'Design View'
  • Make the report fit on one page by shrinking the 'Header' labels
  • 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 'More' on the 'Export' group
  • Select 'HTML' document
  • Leave the name the report 'Homework 12'
  • Check the 'Open the destination file after...' check box
  • Click 'OK'
  • Turn in this HTML document 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.