From the course: Building Solutions Using Excel and Access 2019 Together

Calculate in Excel versus Access

From the course: Building Solutions Using Excel and Access 2019 Together

Start my 1-month free trial

Calculate in Excel versus Access

“

- [Instructor] Excel has an amazing calculation engine. It's one of the most comprehensive libraries of calculations that people will ever use in their life and they barely scratch the surface. With that being said, if I can build it once in Access and use it over and over again, then I'll build my calculations in Access. The best solutions reduce the manual processes that we have to perform. So, adding your calculations in a place where you can reuse them is always best. I know throughout my career, I've seen a lot of VLOOKUPs. This, to me, is a sign that you might need to introduce Access into your solution. VLOOKUPs can easily be replaced by having the appropriate tables and establishing joins in Access to give you the same, repeatable result. Let's take a look at VLOOKUPs. I'm in the VLOOKUP file in our exercise files, in chapter three. I want to create a VLOOKUP that looks up the department name. This would be a common scenario if I have this department file and the department list names is on another sheet. So, I'll go over to my department list. And in H1, I'll go ahead and name this "department name". And then I'll build the VLOOKUP. So, every time I generate this file, I have to go look up the department. Look it up from the department list names. Tell it to give me the department name. And I do want it to be an exact match. I need to absolutely reference, so I'm going to go ahead and click after that A2 and B7. I could've done that as I was building it. Alright, now, I'll go ahead and close it. That way A2 and B7 never update. Alright, then I'll press enter. I'm going to go ahead and auto fill that. Since I have data to the left, I can double click the auto fill handle and it'll build that formula all the way down. And then I want to best fit. So, I'll double click in between the H and the I. So, every time I work with this file, I actually have to do the same process. And I'm not quite done yet. I'm going to go add a new sheet. And I'll name that sheet "shortlist". And then I'll go grab my department list, and I'll copy A, B and H. Go ahead and copy those. Go to my short list. And then I'm going to paste them as values. I'm going to do that one, two, three here. And then I have to move the department to the first position. Go ahead and bolden the headers. And then I'll go to my data tab, and do a multiple sort, and then I'll sort by department name, add level, sort by last name, add another level, sort by first name, and then click okay. Alright, then I'll go ahead and double click to size those appropriately. And then, I can repeat this every time I get a new updated department list. Let me show you how we would do this in a database. Go ahead and minimize that file, there. I'll go to create. I'll choose Query Design. I'll bring in my department list and my department list names. Go ahead and close my show table there. They're linked by the department code, so I'll drag department to department code. They don't have to be the equivalent name but they do have to have equivalent data. And just like before, I'll do department, I'll do last name. Then I'll do first name. Then I want to sort by department. Sort by last name. And sort by first name. I'm just keying the letter "A" for ascending. Of course, you know you can hit your drop down to sort ascending. And then I can run it. Now, when I update my department list, automatically, this query, if I save it, will udpate. So I'll go ahead and save this as "short list." Again, just for the mere fact that I can now go run this query any time my department list updates, bypasses me having to do all of those steps. This absolutely replaces the VLOOKUP process. Let's go take a look at that excel file one more time. I'm going to drag my screen over so you can see. Oh, let me go ahead and cut my last name and move it. Again, there's an error. Didn't have it laid out correctly. So now I have my department name, my last name, and first name again that I have to repeat those steps every single time I run the process. Or I can just go run my short list query. A simple way, in the way, that you mean as your data can turn those multiple look ups that are repetitive into a single run. I'll go ahead and save my VLOOKUP file and I can close it. Make sure my short list is saved and go ahead and close it. This is just one simple to leverage Access over Excel for calculations.

Contents