Getting Data from Databases

Quick reference

• 00:05 >> In this video we're gonna look at how to get data from databases because they're
• 00:09 a little different then files.
• 00:12 We're gonna use a Microsoft access database but
• 00:15 to be completely honest with you this is very similar to any
• 00:18 other database that you might actually try and start with.
• 00:21 The workbook that I'm starting with here is just a blank workbook with a few lines
• 00:24 of text, the real key that I want is a starting point so that I can essentially
• 00:28 go and say, let's go create a new query from database, from Microsoft Access.
• 00:35 We're gonna drill into the GoSkills sample files folder and
• 00:39 we're gonna grab the data from database folder and we're gonna grab the POS
• 00:44 database which stands for point of sale and we'll choose import.
• 00:48 Now this will bring us to a preview window and you can see the tables enumerate.
• 00:53 You'll notice that we have a table for table categories, we've got one for
• 00:57 table Chits which is the food and beverage invoice vernacular and
• 01:02 we've got table items which leaves us our items.
• 01:05 If I wanted to work with multiple tables at the same time I could check
• 01:08 the box which will allow me to bring in multiple tables here.
• 01:12 And if I wanted to get a connection to the root of the database itself without
• 01:17 drilling into a table, I would grab the POS here, and just click Edit.
• 01:22 But for right now, the Chits table is the one that has the most detail,
• 01:26 that's the one that I'm most interested in.
• 01:29 So I'm gonna select that and say, Edit, this will create a connection to the Chits
• 01:34 table and it's gonna bring me in a preview of the data that's actually there.
• 01:39 So I can go and rename this guy now and call this one something like Transactions,
• 01:46 you'll notice that there's a source which was the actual Access database.
• 01:49 This was what you would get if you chose the raw folder and said edit instead of
• 01:53 the rest and then it navigated into the table that we actually were looking at.
• 01:58 Now there's a few things in here that I probably don't need, so
• 02:01 let's get rid of those first.
• 02:03 This ID column is just a row number there's no need to have that,
• 02:06 doesn't add any logic, so I'll right click and remove that.
• 02:10 In food and beverage is very rare that I would ever do a sales analysis down to
• 02:14 the minute, so I'll right click and I'll remove that as well.
• 02:18 And maybe I'm not happy with the column names, maybe I wanna call the first column
• 02:22 here something like dates, so I'll just double click it and
• 02:25 rename that one and hour, so I'll make the change there for that one as well.
• 02:31 The chit number, I could go and actually just call it chit and
• 02:36 our item code, we'll call item code and
• 02:40 our POS category we'll rename to category code.
• 02:47 So that all looks fine at this point what I'm gonna do is I'm gonna change
• 02:51 the primary column here to just a date because it's giving me this date time
• 02:55 which I don't really need.
• 02:56 And now I'm gonna scroll across and look at the rest of columns and
• 03:00 partway across I come to this weird thing with TBL categories and
• 03:03 TBL items that are showing me all of this values.
• 03:07 So what are those about?
• 03:08 Well these are actually tables that are linked together so
• 03:12 when I look at this inside the database,
• 03:14 the categories table will be linked by the category code to the chits table.
• 03:19 That's what a relational database is all about and I don't wanna
• 03:23 click onto the green word with little hand here cuz that would drill into it.
• 03:26 But if I click on the white space, you will notice that it actually brings me
• 03:30 back the records that are associated with this particular table.
• 03:34 So, it gives me a POS category code again, that's the field that is actually linked
• 03:38 to this guy here, it tells me the category description, what group it's from and
• 03:43 it also gives me a table chits table.
• 03:45 Well this happens to be the table chits table, so it could link back on itself.
• 03:50 What about table items?
• 03:52 Well, table items gives me the POS item code from here, that's what it used to
• 03:56 make the relationship, it tells me the description for the actual sales items,
• 04:01 and then again links back to this particular table.
• 04:03 Now the reason why this is so important is because of these little arrows
• 04:08 at the top here which will allow me to actually expand these values.
• 04:12 So if I go to table categories and click on the expansion arrow I can say
• 04:17 I don't want to use the original column name as prefix cuz this would expand them
• 04:21 as TBL categories dot, whatever these titles are here, so you don't want that.
• 04:26 I also don't need the POS category code because I've already got the category
• 04:31 code, I don't need table chits because I'm already in table chits but
• 04:36 the category description and group, I'll take those, why not.
• 04:39 We'll say, OK, and what you'll see is that it expands it for
• 04:43 the related records from the database for my sandwiches, for my description.
• 04:47 So I can go and call this one, category name.
• 04:54 And I can go and shortcut this guy down to, group.
• 04:59 Now what about the items, I can expand these guys as well
• 05:03 I don't really need the item code because I've all ready got it over here,
• 05:06 I don't need the chits table.
• 05:08 I'm really just gonna bring across the item description, we'll say, OK, so
• 05:13 now we've got something that I can rename to, item name.
• 05:18 And the final step with every query is to set our data types so
• 05:20 let's do those as quickly as we can.
• 05:22 We'll reset date, make it a date, power can be a whole number,
• 05:26 the chits, I'm gonna hold down my control key and grab location,
• 05:31 item code, category code, category name group and item name.
• 05:36 These guys are all text based because they're serial numbers that we're not
• 05:40 going to be adding, multiplying, subtracting, dividing, or their text.
• 05:43 So we'll just right-click and
• 05:44 change type to text by right-clicking on any of the headers that I've selected.
• 05:48 Again, holding down control allows me to grab the non-contiguous
• 05:52 blocks like that and set them all at once.
• 05:55 Units, I'm gonna change to a decimal number and my amount column,
• 05:59 I will change to currency.
• 06:01 And with all of that done, I can now hit home, close and load, and
• 06:05 this is gonna go and create me a new table from my database that will go and
• 06:09 land me a whole bunch of records.
• 06:11 Rather than wait I'm gonna speed up the video here just a little bit,
• 06:15 this takes about 30 seconds in real time to retrieve about 350,000 records.
• 06:20 And the nice thing here is, when I need to update it,
• 06:23 because the data in the database is changed, I just hit the refresh button and
• 06:26 shortly thereafter all of its there for me to use.

Lesson notes are only available for subscribers.