Excel and access play well together. In this article, you will learn how to use excel and access together effectively. Read to find out more
Start Files:
Excel and Access interact perfectly with each other. It’s common to have data spread between the two applications, but it’s easy to bring the data from one application to the other.
In this project, we bring all the data together in Access, create relationships between tables, create a query with calculated fields then send the data back to Excel for visualization by a chart
Step 1
Importing 2 Excel Tables
Open the “Student Loans” access Database.
On the External Data Tab Import & Link group New Data Source From File Excel
Browse to the location of the file “Student Accounts.xlsx” and select the first option “Import in a New Table” OK
Follow the Wizard:
In the first window, make sure you check the box for “First Row Contains Column Headings Next Next (No Change in Field data Type) Select “Choose My Own Primary Key” Next Finish
The first table is imported into Access. We do not need to Save the import steps so hit NO for saving
Repeat the same exact steps for the second Excel file “Students.xlsx”.
Now we have 3 tables in Access.
Step 2:
Creating Relationships
On the Database Tools Tab click on Relationships.
The Show Table dialog box pops up (If not, click on Show Table on the Design Tab)
From the Show Table dialog box add the 3 tables one by one , either by double clicking or by selecting then hit Add.
Close the Show Table dialog box
Drag “StudentID” field from the Students Table over the “StudentID” field in the Accounts Table Edit Relationships dialog box pops up Check “Enforce Referential Integrity Ok
Drag “AgentID” field from the Agent Table over the “AgentID” field in the Accounts Table Edit Relationships dialog box pops up Check “Enforce Referential Integrity Ok
You created the necessary relationships Save then close the relationships window.
Step 3:
Creating a Query in Design View
On the create Tab Click on Query Design Show Table dialog box pops up Double click on each one of the tables to add them to the design grid then close the Show Table dialog box. Reposition and resize the tables.
Double click on the following fields to add them to the design grid:
- StudentID from Accounts Table
- FirstName from Students Table
- LastName from Students Table
- RESP Balance from Accounts Table
Step 4:
Creating Calculated Fields
Students with RESP Balance greater than $12000 are Not eligible to an OSAP loan. Other students will get an OSAP Loan equal to $25000 minus their RESP Balance.
In this step we calculate the amount for OSAP Loan and find out who is eligible.
In the first empty cell in the Fields Row, type the new Field name “Eligible”, followed by a colon then create the conditional formula using the IIF (Immediate IF Function). Note when you use a field name it should be included in square brackets.
Eligible: IIf ([RESP Balance]>12000, 0, 25000 – [RESP Balance])
Click on Property Sheet on the Design Tab and set the Format to Currency.
Run your query to test and then switch back to design view to create our second calculated field. We need to calculate the Monthly payment for those who received the loan, based on a 4 Years term and an Annual interest rate of 3%.
We’ll be using the popular financial function PMT. However, we need to convert all the input values from Year to Month.
We shall name the new Field “Payment”. And create the following calculated field in the first empty cell in the Fields row:
Payment: -Pmt(0.03/12, 4*12, [Eligible])
The minus sign before the PMT function is to convert the result to a positive value.
We could either format the new field in Access or when we send the table back to Excel.
I just want to display records for Eligible students by adding a condition for RESP Balance, In the Criteria Row type: >12000
Run the query to test it, then Save the query (right click on its tab and save as “OSAP Payments”.
Step 5:
Create an Action Query
Open the above query in design view (if it is not already open).
Click on “Make Table” on the design Tab to create a new table from the result of the query and save it as “OSAP” in the current Database Ok
Watch the new table in the Navigation Pane.
Close the query window and hit NO when prompted to save.
Step 6:
Exporting the New Table to Excel
Select the new table “OSAP” in the Navigation Pane then, on the Database Tools Tab of the ribbon Export Group Click on Excel
The Export wizard starts. The exported table will have the same name as in Access. Check the box for “Export data with formatting and layout”.
If you want to open the Excel file, check the Box for “Open the destination file when the export operation is complete” Ok
The exported table opens in Excel.
Step: 7
Visualizing data in Excel
Insert a row on top of row 1
Add a title in A1: Monthly Payments for OSAP Loans
Centre align the title across the Table.
Format the Payment values (in column F) as Currency.
Select FirstName values, press CTRL and select Payment values.
On the Insert Tab Charts Group select 2-D Bar Chart
Click on one of the bars (All of them are selected) Right Click Select Format Data Series… Opens a pane on the right side Drag the slider for Gap Width to 60%
In the same pane click on the Fill (Bucket) icon to the left side Under Fill Check the box for: Vary Color by Point Each bar will have a different color.
Resize and Reposition your Chart.
Select the Chart Title hit F2 (Edit mode) in the Formula bar type: =A1 Enter
Format the Chart Title by using tools on the Home Tab.
Select the Names in the vertical axis (Category Axis) Delete.
Click on the Chart Element button (the green Plus sign in the upper right corner of the chart) Data Labels More Options…
The Format Data Labels Pane opens on the Right side:
Check the boxes for “Category Name” & “Values”.
Select the Label Position “Inside End”, then close the pane.
Click once on one of the Data labels to select All of them format their font on the Home Tab.
Some of the labels extend on 2 lines so, select them individually by clicking twice and resize them by dragging the sizing handle.
Finally click on the outer frame of the Chart (to select the Chart Area) on the Format Tab Shape Fill Picture… Browse to the location of the Student picture and select it.
Right click on the Chart Area Opens the Format Chart Area pane Drag the Transparency Slider to 70%
If you still not clear about the use cases of Excel and Access, read this article to learn more
