In this video, learn how to convert raw data in Excel into valuable and more meaningful data in Access that can easily be updated with new data.
- [Instructor] Systems we work with often export to Excel. You may find that you spend time making those raw exports into something meaningful, like other datasets or reports. Let's take a look at an example of something that I've seen come out of systems. This file literally tells me everything I need, like the Order ID, the SalesPerson, and the Production Person. It's just not meaningful data. So I take my time to link this data with other Excel files to make the information meaningful for other users.
OK, let's take a look at this in Access. We're going to import all the raw data exports, we're going to bring in things like customers, orders, employees, and that order assignment file we just looked at. We do that by going to the External Data tab, going to Excel, we Browse to our Exercise Files, and we'll bring in Customer. I do want a live link to that Excel file that way when the Excel file updates so does my database. I'll go ahead and click OK.
The first row does contain column headings. I'll choose Next and Finish. I'll go ahead and click OK. I'm going to do this again for our orders. I'll go to Excel, Browse to my Orders, I'll link, click OK, I'll accept the defaults, I'll choose Next, the first row does contain column headings, I'll choose Next, and then I'll choose Finish.
OK, let's go grab a couple more. I'll go to Excel, Browse, I'll bring in my Employees. I want to link, click OK. I'll choose Next, Next, and Finish. One more file. I'll go to Excel, Browse to my Exercise File, and I'll bring in that OrdersAssignment. I do want a live link, I'll click OK.
I'll do Next and Finish. These four Excel files have all of the information I need to make a very meaningful report. Let's get started by building the customer orders query. I'll go to Create, Query Design. I'll bring in my Customer, my Orders, and my OrderAssignment. I'll link these by Customer ID to Customer ID, and the OrderList, I'll link it by Order ID to OrderAssignments.
And then I'll add my fields. For this example I'm going to add the Customer ID and the Customer_Name, I'll do a Shift + click and Customer_Name and drag that to the grid. I'll bring in all of my fields for my OrderList and I'll drag those down. And then I'll grab my SalesPerson through my ship received person, actually for this example we just need the SalesPerson and the ProductionPerson. We'll drag that down.
I'll go ahead and run this query. It's already more meaningful, except for I really would like to see the SalesPerson name and the ProductionPerson's name, so I'm going to go ahead and save this as CustomerOrders and click OK. Let's create two queries on the Employee data. I'll go ahead and save and close my CustomerOrders. I'll go to Create, Query Design, I'll bring in Employees, and then I'll choose Close.
All right, I'm going to grab the EmployeeID, I'll grab the First Name and the Last Name and the Departments. I want the actual department name to show. OK, I want this to be filtered to just show the business development employees, so I'm going to add a wild card type bus, B-U-S, and I'm going to run it just to make sure, perfect. I'm going to double-click the Departments to size that column heading. And I'm going to name this query BusinessDev department.
I'll click OK. I need the same structure for my production department, so I'm going to change my View to my Design View and I'm going to hit function key F12, this gives me a Save As. And then I'm going to name this the DevProduction department and change my criteria to production, shorten that up. Let's run it, perfect. Now I see just my Development & Production Department with their names.
I'll go ahead and save it and I'll choose Close. OK, so I still have CustomerOrders and now I have my filtered business development and my production departments. So let's go and tie those together with the CustomerOrders and this will give us a super meaningful dataset. Now that we have our two queries created that have our business development employees and our production employees let's go ahead and create an even more meaningful dataset. I'll go to Create, Query Design.
Again, I'm querying on my Queries, so I'm going to add BusinessDev, hold my Shift key, and click my DevProduction, and then I'll choose Add. OK, now I need to link these tables together. I'm going to bring my bus dev EmployeeID and bring it to my SalesPersonID. I'll take the EmployeeID from my production department and drag it to my ProductionPersonID. I'll go ahead and double-click on Customer ID and Customer Name, Order ID, I'll bring in the Order Amount, and then I'll bring in my Order Date, Required Date, and my Ship Date.
OK, let's go ahead and bring in Departments from bus dev, and then bring in the First Name and Last Name of the employee. Let's go to our DevProduction department and bring in Departments, and then I'll bring in First Name and Last Name of that employee. All right, let's run it. Now I see a dataset that uses the OrderAssignments, again, that was just key fields, gives me my Customer Name, gives me my department name, gives me who's responsible for that order in the bus dev department, and who's responsible for it in the production department.
Notice that I have 1,452 records. Let's go ahead and save this. Let's go ahead and name this OrderAssignmentsWithNames. OK, and we'll go ahead and click OK. Every time I get new orders I would really like to just be able to go run this OrderAssignmentWithNames and have no other work to do and that's exactly what this solution will give us. Let's take a look at it. OK, I'll go ahead and close that. I'm going to navigate to my Exercise Files. I have a new order list and the new order assignment list.
I'll go ahead and open up NewOrders. All I need to do is save over the linked file that Access uses to read the orders. I'll do File, Save As, navigate to my Exercise Files, and replace the Orders. I'll choose Yes. I can close that file. I have my new order assignment, which is also necessary for my solution. I'll do File, Save As, and since I have that live link to the OrderAssignment I'll just replace it.
I'll do my Save As order OrderAssignments, and then I'll choose Save, it'll prompt me to replace, and I'll say Yes. And then I can close that file. Because it's a live link to that location and that file name when I run my query I will have picked up all of the new orders. Let me double-click on OrderAssignmentWithNames. We've now added more orders. We started with 1,452, now we're up to 1,506. I hope you can see how easy it is to link in Excel files and keep those files up to date without having to rebuild your same queries over and over and over again.
- Name three reasons for businesses to utilize Excel.
- Recall the uses of the screenshot option.
- Identify the steps necessary to create a table in Access.
- List three items you can add to a query.
- Determine how to maintain linked tables.
- Recognize the report object that includes step-by-step instructions and totals.
- Apply the appropriate action to run a query in macro.