Using External Data

Quick reference

• 00:04 In this video we're going to look at how to get data from a database.
• 00:07 And there's actually quite a few different ways to do this depending on the database
• 00:10 that you're working with.
• 00:11 We're gonna use an Access database that's provided with your downloads.
• 00:15 And the way we're gonna do it is we're gonna start from a completely blank
• 00:18 workbook.
• 00:19 We're gonna go to Data, and we're gonna choose Firm Access.
• 00:24 Now, I'm gonna browse to my desktop, and I have a shortcut here that I
• 00:29 will just click on that opens up this POS or Point of Sale ACCDB.
• 00:33 And that is our database that we're looking at, and
• 00:36 once we open it up we get this interface here that says, here's all of the tables,
• 00:40 and if there were queries, they would show here too, that are in this view.
• 00:44 What would we like to do with it?
• 00:45 Well, the bulk of the data is in the TblChits table.
• 00:49 So I'm gonna click on that and say, OK.
• 00:51 And what you'll see is we now get the option to
• 00:55 pull this into a few different ways.
• 00:59 The two that I wanna focus on are table and pivot table report.
• 01:03 And there's a difference between these two, for sure.
• 01:06 So the first one is if we grabbed this and pulled it into a table,
• 01:09 it would land all of the data right in the worksheet for us.
• 01:13 And you could see it's getting it, it's running it's background and
• 01:15 boom,there we go.
• 01:16 We have all the data from the table.
• 01:18 Now there's a certain set of benefits to landing your data inside an Excel table,
• 01:23 because one of the things we can do is we can actually see the data.
• 01:27 And that's important, it makes us a little bit more comfortable when we're working
• 01:29 with it if we can see what records are there.
• 01:32 The second thing is, is that if we need to make any changes or add any data to this
• 01:36 table, it's really easy to do when we've actually got it right In front of us.
• 01:39 Like let's say for example I wanted to add a column that said Month and
• 01:44 that was equals month of the POS chit date.
• 01:50 Well I can make this modification, write this formula or
• 01:53 any formula that I want against this set of data and now I've
• 01:56 got something that will be pulled right into the pivot source when I go to use it.
• 02:00 So if I were to go in now, and say insert pivot table, and
• 02:04 we can see we've got a range of data, we'll put in on a new worksheet.
• 02:07 At this point in time, I can go and grab my Units and
• 02:11 I could grab the new field that I've written for Month and
• 02:15 I could throw that down there and maybe throw the POS category code down the side.
• 02:19 So, I could see what the breakdown was that was going there.
• 02:22 So, that's really a nice benefit of having this thing in the grid is that I can
• 02:25 modify that table.
• 02:27 Now the other options is that I don't have to do this.
• 02:31 And one of the problems that comes with this is that if this data set were more
• 02:36 than a million rows.
• 02:37 This one's not, this one's only 348,000.
• 02:41 That was pretty quick for 348,000 records, wasn't it?
• 02:44 But if this was more than a million rows, it wouldn't fit into the worksheet, and
• 02:47 that's a problem.
• 02:48 So how would we go about dealing with that?
• 02:51 This is where we can come back and say, let's go and pull the data instead of,
• 02:56 we need to be in a regular blank cell here.
• 02:58 Instead of pulling this into a table, let's go and grab it from Access this
• 03:02 time, and again, we'll go back to Desktop, Sample, pos, there we go.
• 03:07 We'll grab the Chits table again.
• 03:10 Actually we'll grab the Categories table this time, why not, and say OK.
• 03:13 And this time it allows me to pull this directly into a pivot table.
• 03:17 So if this was more than a million rows of data,
• 03:20 I don't have to worry about overflowing the worksheet grid,
• 03:23 because I'm only gonna report the aggregated information.
• 03:27 So now we can say OK, and it says that it can't overlap
• 03:31 another pivot table report because I'm trying to put it on the same worksheet.
• 03:34 So that's not so good.
• 03:35 Let's go back and say let's put this on a new worksheet.
• 03:39 It's important I tried to on an existing worksheet, it can overlap,
• 03:42 that's why that one threw an error there.
• 03:43 So we'll say OK.
• 03:46 There we go, a new worksheet, it creates me a new pivot table frame and
• 03:50 we can now see that I've got all the fields.
• 03:52 Now this is a very small table.
• 03:54 So it's probably not gonna help me a whole lot here, but if I throw a POS
• 03:59 group on here, it'll count the number of POS groups, and if I threw description
• 04:03 over here it will tell me there is one of each this particular tables.
• 04:06 So again, I can even count text fields.
• 04:08 But the big key in this thing to remember is that there is two different approaches
• 04:12 to work with.
• 04:12 If you need to pull in more than a million rows of data
• 04:15 pull it directly into a pivot table.
• 04:17 If you don't you may wanna land it in the worksheet grid so
• 04:20 that you can manipulate it and see it first.

Lesson notes are only available for subscribers.