Introduction to Access

Please follow the instructions below to complete the assignment.

Open Microsoft Access.

Files to import:

Importing a .csv file or text file into Access

  • Open the above files in a blank Access database by following the steps below
  • Open one of the above files
  • Right click inside the data and select 'Save As' & save it to the 'Desktop' or your USB drive
  • In Access on the 'External Data Tab' on the 'Import & Link' group select 'Text File' which will open the 'Get External Data - Text File' window
  • Next to the 'File name' box click the 'Browse' button on the right
  • Navigate to where you have saved the .txt file
  • Select the file you wish to import by clicking on it so it is highlighted, click 'Open' then click 'OK'
  • The 'Import Text Wizard' is opened and it will walk you through setting up the first table (The 'Sample data from file' window will look different depending on the file being opened)
  • Leave the default radial button 'Delimited' and click 'Next'
  • On the 'What delimiter separates...' window check the 'First Row Contains Field Names' box
  • Click 'Next'
  • Click 'Next'
  • Select 'Let Access add primary key.' 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 that 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 an 'ID' field that is the primary key. 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'
  • In the 'Import to Table' box name the file. This is where you name the table. Use the default
  • Click 'Finish'
  • Click 'Close' on the 'Save Import Steps' window
  • The file appears in the left hand window of Access. Double click it to have it appear in the main window
  • Open the table in 'Datasheet View' the button is located in the bottom right of the window
Repeat the above steps to import all of the above files into Access

Linking tables together

  • Select the 'Home Tab'
  • Under 'Table Tools' select the 'Table Tab' then select 'Relationships'
  • On the 'Show Table' pop-up click 'Close'
  • On the left side under the 'Tables' window click and grab the files you imported and drag them into the 'Relationships' window
  • To link the tables click on the 'Field' you wish to connect and drag it to the field in the other table. Remember that the field data must match
  • The 'Edit Relationships' menu appears. Make sure the link is the correct one then click 'Create'
  • Repeat this for the rest of the tables so the relationships window looks like the one below
  • If you make a mistake on a relationship right click the line and select delete and try again

Creating a Query

  • Create a query that finds the files that have 'Never' been accessed and that are in the 'Spanish' language. The table should contain the following fields: last, first, the file name, language, and usage.
  • To do this click the 'Create Tab'
  • Next select 'Query Wizard'
  • Select 'Simple Query Wizard' and click 'OK'
  • In the 'Tables/Queries' drop down list select 'Table:Employees'
  • In the 'Available Fields:' window highlight 'Last' and click the right facing arrow
  • Repeat this step for 'First'
  • Return to the 'Tables/Queries' dropdown list and select 'Table:Files'
  • Highlight 'File Name' and click the right facing arrow
  • Highlight 'Language' and click the right facing arrow
  • Highlight 'Usage' and click the right facing arrow
  • Click 'Next'
  • Click 'Next'
  • Click 'Finish'
  • You now have data for 1000 records with Last name, first name, File Name, Language, and Usage the next step is to limit the query to just those files 'Never' used and in the 'Spanish' language.
  • View the query in 'Design View'. It is one of the buttons in the lower right hand side of the Access window. Hover over the buttons to see what they do.
  • Under 'Language' in the 'Criteria' box type '=spanish'
  • Under 'Usage' in the 'Criteria' box type '=never'
  • Return to the 'Data Sheet View' by clicking the button in the lower right of the Access window. Hover over the buttons to see what they do.
  • Now there should be 9 files listed
  • Sort the data and export the data
  • Sort the query by last name ascending by clicking the 'Home Tab'
  • Then click the field heading for 'Last' to highlight the field
  • Then click the 'Ascending' button
  • Export the results to a HTML file named hw11.html by clicking on the 'External Data' tab and selecting 'More' on the 'Export' group and finally click the 'HTML Document'
  • On the 'Select the destination for the data you want to export' window save your document where you can find it either to your USB drive or the desktop
  • Under 'Specify export options.' select the 'Export data with formatting and layout' check box as well as the 'Open the destination file after the export operation is complete' check box and click 'OK'
  • On the "HTML Output Options' pop-up click 'OK'
  • Open a PowerPoint presentation name it 'Homework 11'
  • On slide one make it a title only slide
  • Copy and paste the report onto slide one
  • Title the slide as "Homework 11 Report 1"

Adding data to tables using forms

  • Return to Access
  • Highlight the 'Sales' table in the left window under 'Tables'
  • Click the 'Create Tab'
  • Click 'Form Wizard'
  • On the 'Form Wizard' window under 'Tables/Queries' select 'Table: Sales'
  • Select the fields 'ID1', 'Sales' and 'Company', by highlighting them then clicking the arrow facing right
  • Click 'Next'
  • Click 'Next'
  • Click 'Finish'
  • You will see a form with the first entry filled in.
  • To get to a blank entry form press CTRL + End to jump to the end of the sales list
  • press 'Tab' to get a blank form
  • A blank form appears. Press 'Tab' again so the cursor is in the 'Sales' Box
  • Enter a random value into the 'Sales' box and then press 'Tab'
  • In the 'Company' box enter 'Tavu'
  • Press 'Tab'
  • You are back at an blank form press 'Tab' to enter 'Sales' data and note how the 'ID1' field automatically increments
  • Enter a total of 4 random sales figures for 'Tavu' using the form
  • What you just did was to enter new data into the 'Sales' table
  • Create a new query on the 'Sales' table for all sales for the company 'Tavu'
  • Click the 'Create Tab' and select 'Query Wizard'
  • Select the 'Simple Query Wizard' and press 'OK'
  • Under the 'Tables/Queries' drop down list select 'Table:Sales'
  • Select all of the 'Available Fields' by pressing the double headed right facing arrows
  • Click 'Next'
  • Click 'Next'
  • Name it 'Sales Query'
  • Click 'Finish'
  • Make sure the query you just created opened and view it in 'Design View' the button in the lower right corner of the Access window hover over the icons to see their names
  • Under the 'Field' 'Company' in the 'Criteria' box type '=tavu'
  • Click 'Datasheet View' to see just the 'Tavu' entries
  • Now export the document to a HTML format
  • Click the 'External Data Tab' and under the 'Export' group select 'More'
  • Then select 'HTML Document' on the drop down menu
  • On the 'Export - HTML Document' window save it to the location of your choice and select the 'Export data with formatting and layout' check box as well as the 'Open the destination file...' check box
  • Click 'OK'
  • On the 'HTML Output Options' pop-up click 'OK'
  • Copy and paste the sales figures for 'Tavu' onto a new slide in the PowerPoint presentation
  • Attach the PowerPoint presentation to an email to turn in

Save the PowerPoint presentation as 'hw11.pptx' in the 'PowerPoint Presentation' format (it should be default). Send hw11.pptx as an email attachment to your instructor.

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 11, 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.