Introduction to Access

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

Open Microsoft Access.

Files to import:

Importing a .csv file into Access

  • Open the above files in a blank Access database by following the steps below
  • Open the above comma separated files
  • Right click inside the data and select 'Save As' & save it to the 'Desktop'
  • In Access on the 'External Data Tab' 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
  • 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'
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 of the screen click and grab the files you imported and drag them into the 'Relationships' window
  • 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

Creating a Query

  • Create a query that selects for 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 box select 'Employees'
  • In the 'Available Fields:' window highlight 'First' and click the right facing arrow
  • Repeat this step for 'Last'
  • Return to the 'Tables/Queries' dropdown box and select '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 '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
  • Sort the query by last name ascending by clicking the 'Home Tab'
  • Then click the field heading 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
  • On the 'Export - HTML Document' 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
  • Open a PowerPoint presentation name it 'Homework 11'
  • On slide one make it a title only slide & paste the report on slide one
  • Title the slide as "Homework 11 Report 1"

Finding Subtotals and Switching Programs

  • Create a query to find all of the sales by country. With 'Sales', and 'Countries' as the fields
  • To do this click the 'Create Tab' and select 'Query Wizard'
  • On the 'New Query' pop-up select 'Simple Query Wizard' and click 'OK'
  • In the 'Tables/Queries' drop down menu select 'Table: Countries'
  • Under the 'Available Fields' box select 'Country' and click the single right facing arrow
  • Return to the 'Tables/Queries' drop down menu and select 'Sales'
  • Click 'Next'
  • Click 'Next'
  • Click 'Finish'
  • Take this query and export it to Excel. Click the 'External Data' tab and under the 'Export' group select the 'Excel' option
  • On the 'Export - Excel Spreadsheet' window save it to your USB drive or to the desktop
  • Sort the excel list by country. You already know how to do this see lab 8 if you need help
  • Highlight the 'Sales' and 'Country' columns
  • Click the 'Data Tab'
  • Click 'Subtotals' under the 'Outline' group
  • On the 'Subtotal' window in the drop down box labeled 'At each change in:' select 'Country'
  • For the 'Use function:' dropdown box select 'Sum'
  • In the 'Add subtotal to:' check box select the 'Sales' check box.
  • Leave the other defaults and click 'OK'
  • Three levels appear to the left side of the Excel working area
  • Click the number '2' to collapse the data
  • What should be shown are the totals for each country. Copy and paste this into a new blank slide in the PowerPoint presentation name the slide 'Total Sales by Country'

Adding data to tables using forms

  • Highlight the 'Sales' table
  • Click the 'Create Tab'
  • Click 'Form Wizard'
  • On the 'Form Wizard' window under 'Tables/Queries' select 'Table: Sales'
  • Select all of the available fields by clicking the double headed arrow facing right
  • Click 'Next'
  • Click 'Next'
  • Click 'Finish'
  • You will see a form with the first entry filled in. Press CTRL + 'End' to jump to the end of the sales list
  • To get to a blank entry form press 'Tab'
  • 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 put 'Tavu'
  • Enter a total of 4 random sales figures for 'Tavu' using the form
  • Note how the ID1 field is automatically populated when data is entered into the sales box
  • 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 box select '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'
  • Copy and paste the sales figures for 'Tavu' onto a new slide in the PowerPoint presentation

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.