Learn Access Table Schema | C S Hayes | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Learn Access Table Schema

teacher avatar C S Hayes

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

11 Lessons (44m)
    • 1. Introduction

    • 2. What is MS Access?

    • 3. Access Vs Excel

    • 4. Table Basics

    • 5. Understanding Schema

    • 6. Field Properties

    • 7. Text Data Type

    • 8. Number Data Type

    • 9. Other Data Types

    • 10. Nulls

    • 11. Summary

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

In this class you will learn about what Access tables are and what 'schema' is. Understanding schema will help you better understand how to manage your data and prepare you for a deeper understanding of application development

Meet Your Teacher

Teacher Profile Image

C S Hayes


Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Introduction: What is the database? The bottom line is that it's just a way of storing structured data. Some are fancy, some are basic, some work across networks, and some do not. At the end of the day, a database is for storing and managing data. Do you have data? How do you currently store it in an Excel worksheet? Documents? Paper form. How do you currently share it? Do you make copies? Do you share a file across the network drive? Do you email it? How do you currently control it? Do you share the rules with others? Do you validate everyone's changes? The database helps you solve these problems. Give an example of something you might keep track of in a spreadsheet. Explain how this data might be a candidate for a database. 2. What is MS Access?: What is Microsoft access? Well, without getting involved in a flame war, there are many opinions about this question. I believe it's just a really good database program that attempts to provide users with some intuitive tools for creating local and robust database solutions. Access provides basic database functionality, but it also provides a large stack of features that intend to make creating a data centric application faster and easier. Access is what some may call a rad software application, not read as in the Old Valley slang of Hey, dude, that's Rad Rat is an acronym that stands for rapid application development. You get a lot of bang for your buck. With Microsoft access, you get quick table creation right when you start the application were right. When you create a brand new database, you have wizards for creating forms, inquiries and reports directly from tables inquiries Using wizards, they have something called Mac Rose, which allow you to create step by step processes, toe automate certain functionality without writing code. They also have a programming model, which uses a special language for Microsoft called Visual Basic for applications, and you can do some amazing things with V B A. And last but not least, there's a very large big online community available to help answer questions. They have questions and answer forums. They have blog's. There's many MVP's Microsoft valuable players that are out there that can help you solve an answer. Questions about Microsoft Access There's books. There's just a lot of support out there for this application. Access does have some of own failings. One is it's not the best solution for a distributed system. It has a lot of expensive tradeoffs. There are ways to do it, but they're not easy. Another situation with Microsoft access is it is limited in how much data you can store in single database, which is two gigabytes now. You may think two gigabytes is not a lot in today's situation with big data. But if you structured your database correctly and you're not a very large entity that's working with big data, you should be able to use the Microsoft Access database for managing quite a bit of things at your local level. There is the ability to link sequel server tables to Microsoft access, but Microsoft Sequel server. It's not an inexpensive option, but this may be a good solution for some users who are able to pay for the Microsoft sequel server but still use Microsoft access as a front end for their users In there. Companies, businesses, many people have stretched Microsoft access to its limits, and this speaks volumes about its adoption and flexibility. But at the end of the day, it was really never intended for large scale use. Go online and do some Internet searching for distributing and access to out of A's and explain some of the pros and cons involved in doing that. 3. Access Vs Excel: spreadsheets are awesome. Excel has become more powerful than ever before, almost covering everything you would need in a database. You can remember the good old days when Excel was limited to only 65,000 maximum rose. But those days are long gone. One of the biggest cons of a spreadsheet was, is and was one of its biggest pros, and that issue can put any piece of information in any cell. You had total flexibility of what you placed into a spreadsheet. There is somewhat of gray area when deciding whether to use a spreadsheet or a database. That line is very slim. Here's some suggestions on when to use a spreadsheet when the data you were working with convey visualized on one or two screens when you are doing many calculations on the data directly for creating pivot tables to summarize data. If the amount of data you are managing a small and when you want to summarize data, these air just some suggestions. Databases solve similar problems by going the opposite direction. More control, less freedom, more control gives clear answers. It brings higher confidence in tabulating. What is ABC +123 What is the date difference between July 4th 2019 and, uh, String Gary Oldman. When you stretch this over millions of records, databases work better with larger segments of data. Here's some more suggestions. I want to use a database. Other people will Inter data. You plan to have lots of data about very narrowly defined information you want to control who can see the data, and you can edit it. You plan on adding to this data over an extended period of time. Create a spreadsheet, add a header row for a list of items. Add values to some of the roads below at all values that don't really match the inferred type of each row or column. Tried to do some calculations. Explain how a database may be able to solve these problems. 4. Table Basics: right. When you open a Microsoft access program, you get this offering of database creation. A lot of these are templates. They have a lot of great things inside of them. But what I'm going to do is I'm just gonna create a blank database and we'll give our database of name like Lesson one, which is not a very good name for a database should be something matching the type of information that you are storing. So what's changes to maybe, like contacts? And then we'll create that. And the first thing that you get when you open up Microsoft access is this table. This is the first thing to access offers you, and this is really important because tables are the building blocks of a database. They are the very core essence of the database. They are the data, and so access is prompting you or trying to nudge you in the direction of starting to create some tables. And so you get this, this this something that looks like a spreadsheet, and as you could see, there's a column here says I D. And then you have another column that says Click to add So what? I'm going to do is I'm gonna create a few a few columns here. You call this first name now? I'm not using any spaces, and I'll explain this later, not create another one called last name. Right? Then maybe I will create one of these. Yes, Snowfields, as in, uh, requires follower, because I'm keeping a list of contacts. Right? So I've got a column for first name. Scoot this over so you can see what's written her. Have a column for first name. I have a column for last name. And then I have this check mark for whether or not this contact requires follower. So let's put a name in here like Laurie Twitter, and we'll leave her as no follow up needed. Now you'll notice that this value over here was filled in for me. This number one this is called an honor number. We'll talk about that a little bit later. I'm going to create another road here. Call this Gary old man because he's one of the actors I really like, and we'll click and say We need to follow up with Gary. And then Mitch had Berg probably space spelling the comedian's name, but that's not important. I've automatically created the table, and now I'm going to go ahead and save this table. I'm going to click on the save button in the upper left corner. It's gonna ask me, Name this table. I'm going to call it Contacts. Press center. So now I have a table named Contacts, and I have some records in it. You remember that? This number for I D gets filled in. We can't change this number. I'm trying to press a button here, and it won't let me change the number. Whenever you create a new item, it adds another Another number two there, etcetera. Now I can put numbers inside these boxes, right, because that is text, but it doesn't really make sense for a last name and first name. It just isn't really what makes sense for this type of table. So I could go ahead and just delete this record, and I get this morning that says, Hey, do you want to delete this record? This another thing to access does to help you out. I want to also show you something else here. Now you'll notice that in the left hand corner there's a pencil a pencil emblem in the row . That means that this record is not saved. Okay, this is something you have to really understand about databases and how they're different from other things, like word, documents, spreadsheets. A database file in and of itself is one database, and every time you change a document or a spreadsheet when you click, save your saving the entire document. But when you're working with a database, you're always adding small little pieces of information into your database and saving it directly. This piece of information right now is not safe because of that pencil there. But as soon as I click off of that row, that record becomes saved. Do not do not confuse the save button with saving records. This safe button in the upper left hand corner is primarily for saving entities that you create like tables, inquiries and forms, which will get into later. But as far as saving records, you must either exit the record or you may have the option in this home button to click on this safe button here, which will save the record without leaving the record. Now it's your turn. Gwynne created new blank database. Given an appropriate name for the type of data that you expect to be saving on the new table that you're given when the database opens, add to warm or short tax fields to the table, add some records to the table and then save the table with an appropriate name, making sure that all of the records that you've enter are saved. 5. Understanding Schema: Let's talk about Schemo. Remember our spreadsheet scenario? We have a first row with headers, each one describing what each column contains. Imagine we have a spreadsheet with table of information on it. First row Contains the following column for friend name. Next birthday. Favorite color agent. Next birthday. What will be type in the first column for our friend Jim? Probably simply Jim about next birthday. Let's say June 21st 2021. What happens? Excel interprets that as a date. That was handy because that's what we want to keep track of a date. Favorite color. Blue Age Next birthday. 25. Pretty simple, but lo and behold, we have schema. It's not enforced, but it's there. The friend name. It's just plain text. Obviously, we don't want to put numbers in there Next birthday. Well, that's a date. Favorite color that again is just plain text. But it's possible for a limited list of items to choose from a list of colors say, age next birthday, and that's a whole number. The problem is that we can add any text. I want any of these cells. I'm not restricted to the inferred schema. If I later on want to do some calculations on some things? Like how many days between the next birthday? What age will someone be the next year? Or how many people like the color red? What happens if I have mismatched at it in those cells? Think of something you want to record something at work or school or home or club. Write down each piece of information that you would probably place into a spreadsheet. Identify what each type of information is. Is it a date? Is it just a piece of text? Is it a number? Inter schema tables you schema to enforce data type safety. Every date will always be a date. Every number will always be a number. Values that come from a select list of values will always be one of those values. From that select list tables use something called schema. This is a way of defining what kind of data the table will contain. Think of schema like the first row on a spreadsheet. It kind of defines what each column should contain. Each table has a schema. Each schema defines the columns or fields. You'll see these terms used interchangeably columns or fields each column has a type. Each type has a set of properties, some changeable, some not. Some of these properties are shared between other types. Some are very specific to a certain type that you're choosing. 6. Field Properties: Before we discuss types, let me preface with short explanation about properties. Every field has properties, two of which are primary, the field name and the data type. You see those properties right in the main body of the table designer field name. This is how the database keeps track of this field. You should name your fields columns Meaningful E. Despite the ability to name fields using spaces, you should avoid doing so. Let me repeat that despite the ability to name fields using spaces, you should avoid doing that Spaces make identifying fields harder later on in your database creation Building process Data type is the distinct type of data you will be storing in this field. You are limited in what types you can choose when designing a database. It is important to select your data types wisely, considering each type and which ones would best suit current and future needs of the data that you plan on story. Go ahead and make a few fields in a new table, set their field names without spaces and randomly or intuitively select their data type properties. Give each type special behavior size restrictions. Databases are about control, not flexibility. Properties normally start out less restrictive and become mawr restrictive as you set new properties on them. Some properties are shared like the caption. Every data type has a caption. Property. This gives you the ability to use that space that you wanted to use before. Use this property for giving your fields a user friendly, readable name. This is what will show up in the queries and forms, which we'll discuss later. Set the caption property of one or more of your fields to b'more, user friendly with spaces or even special characters required. Do you want to force you? Uses toe? Always put a value here. That's what this property is about. Be careful using this property. Some values are not as required as you would think they are. Be very deliberate about what fields are and are not required. Fields that are not required will contain no values. We'll cover that shortly. Make one of your fields required. See what happens after you save and return to the data sheet and try not filling the value it See what happens when you make a field required when you already have records that have that empty values indexed. You will be searching on this value often. Do you plan on having a very large amount of information in this table, and this value will be used to filter on it? Use this property to index or create special, fast, pre sorted lookups on your table. Good examples of this are like city, state area or anything that you used to categorize your information, like colors of a product or sizes of a product or general availability, etcetera. Create a field that you think should be indexed. Set the index property on this field. Explain why you would indexes field default value. Say you know the value that will go in this field most of the time. Imagine that your business does 99% of its business in one state. The state field could have a default value of that particular state, making it easier on the person inserting the data. Add a default value setting toe one of your properties. Go to the data sheet view and see how this value gets populated on new records. Try saving a record with or and without the default value. Some properties air not shared, and we'll talk about those as we continue discussing types 7. Text Data Type: Let's talk about data types. Data types of the main way databases control data data types can be organized into a hierarchy text. The first type most people are familiar with is text text. Is any old text a name Street address a color on email address. If you don't know what kind of data your type should be, you probably wouldn't go wrong. Selecting text tax comes in two flavors with access big and small. We'll start with the small short text short taxes for small pieces of text. If you didn't know in a database size matters, and restricting size is very important in a database. It helps optimize space because of the way a database optimizes storage size of a data type is important, especially when dealing with text. If you know what kind of texture storing and you should, this is part of database planning. Then you should be prepared to select the appropriate size of the field. Short text is the most common type of text field. Most pieces of text data do not exceed 255 characters, and that's the limit size for short text. Bigger is not better in a database most pieces of data are smaller and should be put in smaller containers. Could you imagine a refrigerator filled with large containers, each containing just a small amount of something your refrigerator would fill up very fast ? And this is the same for a database. Short taxes. Good for most anything. A person's name, a street address and email website. Address phone number of favorite color. You've made a few short text field so far. Explain why short tax. It's a good choice for one or two of the fields you've already made. Try making a short text field and giving an explanation as to why it should be a short text field. Long text Long tax is for longer pieces of text. Use this for things like messages or document taxed, or something that gives a description any information that may be paragraph size. Another cool feature of the long text type is the ability to save rich text formatting. You can change the font size. You can make text bold, italicized or underlined. You could even change the text colors and add highlights. If you're dealing with documents. There are alternative ways of storing that kind of information we can discuss this and more advanced topics. But consider keeping large documents of information and external files and then keeping pointers to those external files. Make a long text field. Add some data to the field. Try copying and pasting a large amount of text into one of those fields. See if there is a limit or on air on pasting. Set the text format property to rich text. Play around with the taxed with the built in rich text editor. Explain a possible solution to cases where text you want to say is too big for even a long text field. 8. Number Data Type: The thing about numbers is that there's all kinds of them, one to infinity and everywhere in between. Because of this, it's important to let your schema know what kind of number you are storing. The purpose of the number will let you know which one to pick the number type in access Access has its first type number, which covers different types of numbers in its properties. The most common kind of number in data is what's called an integer whole numbers keeping track of counts, ordering items. Imagers also come in different types basically two types, which is the imager and long imager. Why were there, too? It's about size again. Databases are very particular about the size of the data that they're storing. In the old days, 16 bit numbers were the thing Remember me mentioning the Excel limitation of 65,000 rows? This was due to a 16 bit limitation. The top upper number for a unsigned integer, which means a positive integer, was 65,535. That was the biggest number you get with a 16 bit integer. These numbers are good for counts. Use this number when you're confident that the number of items in your database will not exceed plus or minus 32,766 or positive 65,535. If you're using positive numbers, only trying to think of a value that would fit this description of a number explain whether the hazards of using this type of number large emitters, large imagers, or 32 bit imagers are the most common number type in the database. Without getting into a big discussion about computer science and binary. Just suffice it to say that computers were built to handle numbers efficiently up to a specific size. The largest number computers could handle efficiently for a very long time has been 32 bits approximately plus or minus two billion 32 bit and injures serve many purposes on their usually the safest bet. When you need a whole number, try to think of a value that would fit this description. Explain what the cons are to using this type of data bite. This is a very small number, plus or minus 128 or just positive 0 to 255. If you have a very small number of things. Use this number, but be very careful picking this type. There's just a few instances where an energy just won't do. These types were used when computer to space was at a very big premium. That really isn't the case anymore. And what you think would only take up 255 of something quickly turns out to not be true. Try to think of a value that would fit this description. Internet search Minecraft to 56 Significance. Explain how this might have something to do with this type in access. Fractional numbers. Outside of counting things, another mathematical need is keeping track of fractions of numbers. Access has three primary decimal fraction number types you can use. The biggest need of fractional numbers is accuracy. Just how accurate do your numbers need to be? Most will say I need absolute accuracy, but the reality is you're not computing rocket science. Or maybe you are. But suffice it to say most of the time, you don't need a decimal for anything past four or five decimal places. Numbers that can hold more accurate fractions also cost more space on disk, although dis space of process or speed are much more advanced now, considering your need in advance can be helpful. Some people have opted for using imagers for storing decimal numbers, which have no decimal information in them. And basically, what they just do is they format the death they form at the interview number, which is a whole number non fractional to four decimal places by dividing the number by 10,000. Because folding place of arithmetic can get confusing, there's another number type called currency, which will get into later, which is designed for storing monetary values. If you plan on using numbers for fractional needs other than currency it in a very general manner, you need to do some cursory research on which type best suits your needs. At the end of the day, the size and accuracy are what primary differentiates. Each kind of number type the single, which is the least accurate, smallest storage of fastest speed. The double, which is moderate accuracy, moderate storage, an average speed, and the decimal, which is the most accurate, takes up the most storage and is the least fastest. Create a field other than a money type field that would use a desk, a large number. These air 64 bit imagers. 64 bit imagers are when you realize that 32 bit imagers, not enough plus or minus nine quintillion of something. Use this type. Because the size of calculable numbers is ever increasing, you will find these enough. These types of numbers and higher use access isn't intended for large scale database work. Access is more suitable for small businesses and school needs. It works well with small teams. It's unlikely small teams will be creating something of this magnitude, but you never know, and you may have a need for number this big in small quantity. Explain the use for this data type in the age of big data. Explain it possible need for this type of information, maybe in reference to data from some outside source. I think social media etcetera. Here's a post note on the number type. When considering what type of number to use, consider what it's used for. If you need to store lodge itude and latitude, you're probably going to need a decimal type, especially if you're doing math on those values. Do some online research to find which type will give you the best bang for your buck. Someone has already encountered your problem and posted a blogger form. Answer about it. 9. Other Data Types: other data types. The date time type access has a way of storing date and time. The only way for storing a date would be to store the date time. You can't just store a single date. What people normally do is ignore the time value. They usually said it 20 which is basically midnight of the day. Beginning date Time fields have an automatic date selector for picking your values for data entry, which is very cool. It was only a few years ago that people were rolling their own date selectors to do this task. Using the date selector makes entering dates very easy, but not time. Time is usually more difficult to enter. The most straightforward way to enter time would be to use some type of input mask, and we'll discuss those in another course or rolling your own custom control made to meet their specific needs. Give five examples of use case for using the date time type. Explain why you would need the date value. Only currency Currency is a field that just as its name implies, it's designed to store numbers used in calculating money. Up to 15 digits to the left of the decimal and down to 44 digits to the right of the decimal accuracy. Give three examples of a field that would use this type. The auto number. The auto number is a special Energia used for creating a unique key for each record. Unique keys are very helpful in the database. They help identify specific records quickly. Imagine if you have a list of contacts like Jim Smith. 123 Main Street and then another. Jim Smith at 334 1st ST. Each of the same name using a key helps distinguish between each record record. One. Jim Smith, 123 Main Street record to Jim Smith, 334 1st ST. Using an auto number field not only enforces that each record has a unique number for its key, but it also assists that, automatically inserting an incriminating number for each record as they're created. Explain how auto number fields help the table integrity in general. Yes, no fields. Yes, no fields are basically bits. They're basically on or off. And one of the cool things about a yes, no value is that you could select how it's displayed. Yes, no, on off true falls. Checked or unchecked, it's a switch value. You can only have one of two distinct values and coding. We usually call these billions true of balls values only and are usually initialized as false. The default value. Be careful doing math with this value. It is not stored as a normal bit. One in zero, like most Boolean values are if the value underneath the yes no field is always the same. Explain why access gives alternatives for how to display this field without getting too detailed. There's a few last, more complex data types. The O L E Object is a link to a specific type of file, like an Excel file. I prefer the attach it field to this type. This data type is really only for legacy purposes. Hyperlink is text that has some special baked in properties and special tools for using hyperlinks like validation. If you're storing website links, this is the right type to use attachment stores a link to a file. You should not store binary files and data base, especially databases like access, which have database file size limitations calculated. These fields take multiple other fields from your row and calculate a value from them. They are automatically filled in with the calculated value. The value is stored in a database and re calculate each time the record is altered. Look up, Wizard is the control that points to another table of data. We could discuss how this works in another course that I'm putting together on relationships. Nevertheless, this field gives a special drop down look up tool for entering the value and displaying what selected. Imagine some other types that access might, including future versions, explain how these types could help or hurt the product. 10. Nulls: NOS. Access databases, along with many other databases, have a concept of missing or empty data called Knoll. It's not a value, it has no value, and there could be discussion on equality and etcetera. When a fielder column property is set required, then these fields cannot be no or empty. Knowles do not get counted when a field or columns property is set to required equals. No than these fields can be empty or no knows do not get counted. They do not get calculated. If they end up in a function, they could create errors when adding numbers. That could be no. The resulting calculation will be no null is not zero. But if you want to handle it as a zero, Access provides a function called NZ, which will turn mills into zeros because knows exist in data access has created numerous functions for handling their values. There's the is no function which will return true or false and the values? No. There's the NZ function that you just mentioned, which will turn Knowles into zeros inside of queries. You can declare the is no in a where statement and come up as true or false, Knowles in text. No is not the same as what's called an empty string, and this is really important. A piece of string or tax that's just empty is called an empty string. You may want to have a field that has no value in it, but it's not, know more likely. You want to ensure that if a value get set and then unset to an empty value, that you don't story an empty string or an empty value, but a null. Instead, these air decide design decisions. Most of the default properties and settings are already safely set for your common use. There are many cases working cat, meaning text values can have funny effects. What a field value is. No, but was actually filled with empty text. Try to explain why Knowles exist. Try to explain how knows could be eliminated. 11. Summary: in this course, we briefly one over table creation. What a databases. It's just a way of electronically storing data. What is Microsoft access? It's just Microsoft user friendly local database software. We talked about how it compares with Excel. Excel in access are both great programs, but they have different uses. We talked about table basics. How do quickly get started with making tables. We talked about schema. Databases have tables and tables have fields, fields have properties. We talked about the different types of data, like the text type, which comes in different flavors, primarily shortened long types. Short types of the most commonly used long type is for paragraph size pieces of information . Then there were number types. There's two primary number types. The whole numbers of imagers, which come in different sizes, and then the fractional numbers, which come in a variety of flavors. Also, we talked about other data types, primarily the date time, which is for storing date values and time values. Storing date values is pretty straightforward. Story in time values can get kind of complicated, that there's the currency type, which is primarily used for dealing with money, and this is the type you should select if you're storing a monetary value. There's the auto number, which is for creating a unique key in the table, which gets used later on in relationships. There's the yes no field, which is a Boolean value used for binary decision making. And then we talked about a few other types, like links to files, calculate herbal types, links to other tables with Lookups Knowles. We spoke about Knowles, how that they're not really a value, their non values, how they interact or, better said, misbehave with other pieces of data and how to work with them with built in functions. I hope you've enjoyed this short, brief introductory course to building tables of Microsoft access, and I look forward to seeing you in future courses.