In this session we're going to create a database from scratch and we are going to learn about some
of the various components of a Microsoft Access 2016 so we can kind of understand
how the application works and where the different components are. So once we open Microsoft Access
we click here on the blank database and like we learned earlier the first thing that we need to
do after we click on the blank database we need to give it a name and take note where you're saving
this database. Now as soon as you open the Access database here what we have is very similar to
Microsoft Word and Excel and other applications in office 2016. On the very top we have the
quick access toolbar with a bunch of commonly used options we have the file tab here and then we have
these different tabs at home the most commonly used functions very similar to other applications.
Then we have here the create tab this is to create different components related to databases. For
example creating a table creating queries creating forms and reports. Then we have the external data
tab. This is basically for us to get data from other systems and import it and link it into
a database here in Microsoft Access and then database tools this is another tab where we can
design the database and define the relationships or define basically any tools related to the
database here for whether to repair the database to create macros or other components. Then we have
here this new tab called table tools. So table tools here this is very similar to the contextual
tools in Microsoft Word or Excel basically a new tab that shows up in the context of what
we are doing. So right now we are creating a new table and it's giving us options here for this new
table. Next to it here you have also tell me what you want to do or that tell me feature. This is
new in office 2016 so basically if you wanted to learn how to use query wizard or how to create a
new form or anything like that you simply type in there how to do that. So for example query wizard
and you just basically takes you directly to that option in order to learn how to perform a specific
task in Microsoft Access. And then notice on the bottom here you have a couple of other options
and I'm not going to take the time to tinker with those too much. It's basically the design view and
the normal view and we'll cover those shortly. Now creating our first database here. Databases
as I mentioned earlier they are designed using tables. Typically you have at least one or more
table. So now this is our first table that we are working with and we'll give it a name shortly so
it will actually ask us to save and give a name to this table in a moment. Typically the way tables
and databases work is that one of the fields by the way these are referred to as that field so
you'd have the ID field the first name field and a last name field and so on so the columns which
we refer to them where you'll hear the term field. Then you'll also hear the term record so we'd say
this is record one or record two or record. The record is think of it as the row here. So you
have more than one piece of information related to a record so you have for example first name last
name address and so on relate it to that specific customer. Here this would be the field name so
right now it says ID so you get to change that to say customer ID. The type here for customer ID it
typically it needs to be a number so notice under the data type this is an auto number that means
that when it goes to the next customer it'll go so from customer one to number two number three
number four automatically. The data type for each one of those fields typically has to be specified.
The next one it's asking us to what type do we want to make this next field here. So the next
field here we are going to make it text and this will be first name then the next one. We're going
to make this field type as well as short text here and then we're going to call this last name and
then the third field we are going to make this let's say the state address and this would be
text as well. The next field here it will be city and then the next, state and then the next one zip
and the zip code we want that probably as a number field. And then the next one you can pick whatever
other field that you're going to utilize but take note here that it can be various other fields so
it could be for example a date field when they signed up to be your customer and such or you can
have an attachment for this customer or you could be able to post a hyperlink field as well. So in
this case we are going to create a field here for attachment and that would be for example for
the picture for that customer or various other fields. Basically this step it's referred to as
the designing this table so we are defining how the fields are going to be formatted. If we are
done with the design at this point because simply we could do a couple things at this stage. We
could either enter the data directly from this table that we and by the way the data that you
enter from now on from this point on it has to be matching the type that you defined a moment
ago for example the zip code has to be a number it can't have letters in there and things of that
nature. The other thing to keep in mind is is that data typically we for now we are going to enter
it here directly into table but typically it's not entered from the table itself it's typically
entered from the form of the database and we'll learn about this shortly as well. Let's enter just
one record here for the sake of testing and then if we go to the next record you notice it entered
the customer ID automatically. So the concepts so far that we covered in this session: keep in mind
when you define those fields you need to specify the data type and it's very important to think
it through as to all the fields that you want in a table when you're designing your database.
So then you want to make sure that all the fields that you'd want in that particular table they are
included in there. You can add them later as well but it creates it causes complications it's best
to think it over initially. The other thing is is that you need to consider categorizing the data
accordingly in various categories and then these categories they become your tables. So for example
you want to make sure that let's say customer information anything related to the customer such
as the address the preferences and mailing address and that type of thing you want to keep it in one
table. Then anything related to orders you want to keep it on the order table. Anything related
to payments you would keep it in the payments table. Anything that you want to keep related to
inventory you want to keep it in an inventory table and even the inventory could have all
kinds of sub tables as well. The key there is to categorize information in major categories. Those
categories become at least a table of some sort and then you define the data type for each field
here and then you have to make sure that whatever you enter in that field you want to make sure that
it matches that type of data. Also remember as you design your database you need to have
kind of a key differentiator between the records in your table. So for example if you had two
customers named as Hubert Sims and such you want to make sure that how do they differ and the way
to differ from one customer from one record to another is by assigning them something unique.
For example a customer idea, a unique customer ID and those are typically referred to that becomes
your primary key. The primary key again that is what will differentiate between two records. Once
we are done with designing our first table here you'll click on close here on the top right of
this table and now it will ask us to give the name to save the design for this table. It will
actually save the design along with the data that we just entered. And now notice here under the
tables list here on the left hand side we have customers information. Now to open this up you
simply double click on it and you'll be able to view it and enter new records in there as well.
If you need it to change the design you could simply click here to add additional fields or
another method to change the data and change the design for this table is also by using the design
view. So notice here under the home tab we have view and there are a couple of views. There's
a datasheet view what we currently are seeing and utilizing and then you also have the design
view. Let's click here on design view and this is a more sophisticated way. It's a little bit
more complicated if you're not used to working with databases but yet it's actually a lot more
powerful and a lot more useful. So here what you can do is basically you can modify the structure
of this table. On the left hand side you have the field name which was the column for each column
in that table that we saw earlier so we could change the names here we could change the data
type as well. If you have a lot of data in your table and you go and tinker and manipulate the
data type you might the most likely get an error message. So keep that in mind as you design your
tables to try to do it as best as possible in the beginning whether it's the data type the layout of
the fields and such now. From here from the design view like I mentioned earlier you can change the
data type so you can say OK under the state I want that instead of 255 characters I want that to be
only the two-digit abbreviated version of it or you can make it 40 characters long or whatever
the length of the field is you can define the field. You can also change the format in the
mask and all kinds of default values and you can control all kinds of additional settings here so
I'm not going to go into the more fancier options here but for the big picture you can change the
steps from the datasheet view or you can change it from the design view from here. Notice as well
that you can change the order of those fields by holding down the mouse and you can move one field
above the other one as well and change the order of those fields. To add new ones you can add them
here in the bottom and then you have to define the data type as well. So for example this is a
field for comments so this would be a long text so you want to make sure here that the user can
enter enough text. I believe that'll be 64,000 characters that it will accept in that field
when you start typing on it. Once you are done with any of the design changes now we click on
close here and it's going to save the structure. Typically this save option it takes place only
when we change the structure of it so one of the concepts here is is that if you change the
structure of your table and the design of it then it's going to ask you and prompt you to to save
it. However if you are simply entering data the data is saved automatically into your database.
So that's another concept to understand when using databases. The other thing is that databases are
designed to be used by multiple users at the same time so once you have finalized your design you
can have 10 users, 15 users, 50 users or however many users access and update the table at the
same time and work on the same file the same database at the same time. So those are some
of the very basic concepts on getting started with an Access database and access table. Now
this is not all. Next we are going to enter some more data into this table and then we're going
to create a query we're going to create a form and then we're going to create a quick report
with just one table. And then furthermore so stay tuned we're going to create multiple tables and
we're going to link those tables together and then we're going to utilize the more
intermediate functions in Access databases and using Microsoft Access so stay tuned.