Hello. My name is Sali Kaceli .The following is a comprehensive tutorial
on using Microsoft access
2016
during this tutorial
I will cover basic concepts related to databases and how to design and use a basic database
using access
2016 we will start with the very basics of the
Application and of databases [overall] and then we'll get moving into working with Data in a database
the tutorial is designed to be concepts based and
Simple to understand the best way to learn is to follow the video hands-on on your computer
So let's get started first
Some general concepts related to databases and how databases work?
first we're going to start with the definition of a database a
database is a collection of data that is stored in a computer system a
Database allows their users to enter access and [update] and analyze their data quickly and easily
They are a powerful tool that you see them all the time
when you go to the doctor's office
when you go to the grocery store
When you go online to purchase something all that
information is usually stored in what's called a database the easiest way to
Understand the database is to think of it as a collection of lists
So think of it for example if you're running a business you have [a] list of customers
you have [a] list of orders from those customers you have a list of items that you are selling to your customers or
services that you are providing to your customers, and then you might have a list of invoices and
other
Purchases from vendors and things alike but when you put all those lists together in a database
those lists are linked together and
information is related from
One table to another and that's partially what is referred to as a relational database
[so] where does access fit into this?
Microsoft access is a relational database
software program or database management system dBMs
That runs on windows operating system
It is used to manage data that is organized into lists such as information about
customers Products vendors Employees
Projects, so basically Microsoft access is a relational database management system
created by Microsoft to store organize [and]
Manipulate Data as well as to select and report on it
So some of the components [just] [to] summarize them briefly are tables
queries forms
and reports
Tables that's where the data is stored within the database then the queries are three specific
data from the Tables or other queries and
Displays only the Data that you specify
Queries allow you to ask questions about the data in your tables for example
I want just the first name last
name and
Email address and the form is used to enter new records in a table
That's the data entry component of it and to edit or delete existing records
Into the table usually the Data is not entered directly into the table
manually, but it is done through the forms and then the reports or the
fourth component here
And those are the [very] [symbols] of the output they summarize the fields or records
From a table or a query in an easy-To-read format for example that will be
Report that you print out something that you print out to give to your manager for the sales of that day now
What does all of this look like in the national database?
Here's a really simple
Database that we have we have a database called the customer sales here, and we have a bunch of tables in this case
We have the customer table
We have the contact table
And then we have invoices so think again about these as separate lists
So the customer table of course it will have the list of customers
We have here the customer Id
company first name last name telephone number
State address City state and so on so those will be [very] similar to what we have seen in excel
You can sort this [data]. You filter it [and] things of that nature
I think of it just as a list now
[besides] the customers if you're running a business, [then] you probably have different
contacts or different sales
So in this case. This is another example of a table of contacts table if we double [click] on it
We have a contact number customer Id
Contact amount a date and in the type and then a notice we have another table here called
invoices and the invoice table has the date number and the item what it was for the amounts and
What is your [spAdes] or not? So those are the tables again? This is where the data is stored?
Into a database think of it here tables storage
The next [components] in the database I have mentioned earlier
It is the queries, so if we go here the queries for example are like we have the table customers
those who have a whole bunch of fields here the fields are those names of the columns here and
let's say [that] we wanted to generate a list of
only the first name last
Name, and then they say email address and some other pieces of information here
We create what's called a query and we learn how to create queries momentarily here
So notice I have another entity here under the customer table
And this is a query that we have run that I defined earlier if I double-click on
Notice I have only the first name last name telephone [number] you [lanice]
So that think of it the processing of a data based on a criteria
And this is an example of a form the form is basically instead of you going to the customer table
scrolling all the way to the bottom and
Entering the Data manually here
Which is not recommended [just] [go] under the customer data entry form for example
and form similar or nicer than this and
Enter the records right here
and
You just basically fill in the data in real business. This [is] what?
the Assistant was utilize for entering the data into the into the database and
Then the final components as I mentioned earlier it is the reports and reports are [very] similar to
queries, but they are just
Designed so they can be printed out and they look nicer
So these were some of the basic components of a database the tables are where the data is stored?
The queries are how the data is?
manipulated
Then the forms are how the data is updated and added on to and then that?
Reports how the Data is printed out or output
Once you are in Microsoft access
[you] will know this is very similar to Microsoft word excel and other applications in office 2016 on the left hand side
You'll have the recent documents or the recent
Databases are files that you have been working with then below you have here the option to go ahead and open a pre-existing
Database and then on the right hand side here we have the option to create a blank database that will be used
utilizing in a moment, and then further down here we have different templates that
Are [available] in Microsoft access?
2016 these are pre-configured
[access] databases that you can utilize
We're going to utilize one of those templates very briefly just to learn a couple of the concepts
But we are actually in the next session here
We're going to learn how to design a database from scratch
So that [we] understand how databases work how to use Microsoft access
So in our case [here]. I'm going to very briefly
Utilize here this database called students. This is new in access
2016 now here is a concept that you might want to keep in mind when dealing with Microsoft access databases
Unlike word documents, and excel where you open a [document], and you create the document
And then [you] manipulate in a lot type of thing in
Microsoft access
[or] as soon as you choose to create a database
You have to give it a name and your the first thing that you do is you save that database?
So in our case [here]
We have to give it a name note the location where it's going to be saved and then you click on create
In this case we are utilizing a template
So, it's pretty defined with all the components and such
So it's slightly different or creating a database from scratch
so first thing you are presented here is a
Tutorial on how to use this not everyone [of] them is [going] to give you this option so we are going to simply
Get started here to utilize it now a couple of things here
[just] briefly so that we get a couple of the concepts and I will demonstrate these
Much more as we get started in the next module
Typically a database has four components
Just like in a computer that you have the four basic functions of the computer where you have the input?
that the computer accepts input it stores the data by storing it in the hard drive it processes the [data] and
Then it outputs the data in access databases
You have a similar concept as well you have the tables which serve to store the data. That's where the data is actually stored
you have the queries which are very similar to the
Processing of the data on the [computer] and for now just keep those in the back of your mind
And then you have the forms in an access database as well
That serve as a mechanism to enter the data into that database
And then finally you have the fourth component here the reports that
Serve for displaying the data in your database. It's very similar to the output in your computer, so in our case here
We have this form so if we want to add and [your] students
We simply click on your student and then we fill in the information there you can add the picture you can add [additional] information
And click on save and then new and then you add the next student and so on now this window
what we are using right now, this is referred [to] as
the forms
so this serves as the input for this database now that data is not really stored in the form the
Data is actually stored in the table here on the
Students table, and we'll learn more about this in the next session so now if we look here
This is how it will be stored. So it's very similar to like an excel spreadsheet
but it's just a bunch of tables [and]
Typically in a microsoft access database or in any database you can have two tables three tables
Hundreds of tables or even thousands of tables and those tables are typically linked together Via
What's called primary keys and foreign keys and learn about those as well?
They are kind of related one table is related [to] the other table via those keys
And that's where you hear about the term
Relational databases because the tables are linked the other [component] here is the queries we don't have any of those for now that we could
Utilize the next one would be the forms that we just used a moment ago
And then if you need to create a report of all the students, you simply click on all
Students, and it will display
More visually pleasant report for you to print it out
So those are some of the basic concepts using a template so now stay tuned for the next session that
[will] learn how to design our database from scratch?
We'll create the first stable and then start building a couple additional tables
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 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 a quick access toolbar with a bunch of commonly used options. We have a file tab here
And then we have these different tabs of 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 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 the 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 lab 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 so 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 the field so you 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 also hear the term
Records so we'd say this is records one or record two or records
So 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 personal 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 it could change that to say
customer
Id there's a 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
[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 to say the [state] address and this will 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 is you can pick whatever other field is your [go] you eyes, 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 if it 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 free zone for the picture for that customer or various other fields
Basically this step is 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 [at] this point on it has to [be]
matching the
Type that you defined a moment ago for some of this the zip code has to be a number
It can have letters in there and things of that nature
The other thing to keep in mind is is that the data typically we for now
We are going to enter it here directly into the table, but typically it's not entered from the table itself
It's typically enters from the form of the database and we'll learn [about] this shortly as well
Let's enter just one [record] here for a sake of testing
and
then if we go to the next record
You notice it enters [a] customer id
Automatically so the concepts so far that we covered in this session
Keep in mind when you define those fields as 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 use 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 day
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 preference [asian]
Mailing address and that type of thing you want to keep it in one table
Then anything related to order you want to keep it on the order table
Anything related [to] payments you keep it in the payments table
Anything that you want to keep related to inventory you want to keep it 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 some sort and then
You define the data type for each field here
And then you have to make sure that whatever your 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 some kind of a key differentiator between the records in your table
so for example if you had two customers
Name the super 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 id 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 you'll 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
summers 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
[Left-hand] side you have the field name which is the column for each
column in that table that we saw earlier
So we could change the names here
Or we could change the data type as well. If you have a lot of data [in] your table and you go [into]
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 okay under the state. I want that [instead] [of] 255 characters
I want that to be only the two digits
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 stops 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 one 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 you 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're simply entering data the data 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 a way they'll 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
In this brief video we are going to learn how to add additional fields to a table in
access database and then we're going to learn how to create queries how to create forms and
how to create a report based on a table in our database
Stay tuned we are going to also learn later how to create multiple tables in a database
and how to link those tables together and
utilize additional functions in Microsoft access
2016
so we have our table that we designed earlier and
Now we want to add let's say to make a small change as I had mentioned earlier is don't want to make too many changes
After Data has been entered in the table
But this is just for [demonstration] purposes [so] to enter a new field because do it a couple of ways here
We could either click [here] on click to add and then choose a type of field that we want to add
or we can go here under view and
Then choose the design view [so] design view it's this next one now
We scroll all the way to the bottom here
and then
Let's say we want to state whether this is a new customer or not so it's going to be a yes or no field
So we say new customer, and then we have to change this on the drop down here to be yes
Or no, well the system is going to do there is going to allow us to put a check mark basically have a check box
Once we have to tweak that you can add additional fields in there by the way you have here
Under description Design view you have an area where you can actually?
Put notes design notes for you as a design [out] of this database you could say
additionally you can go up further for any of those fields and
Such as we cover the very briefly in the last session
Each table needs to have a primary key and the primary key can [be] a number field it can be anything
but it has to be something typically it's a number in an other number something that increases or
Some kind of code and it is what differentiates?
One record from another so if you have two people the same name in the same address
Then in order to differentiate them the best way would be to simply
Assign them a new Id or to have different ids
So the primary key typically in the database design here since we are in this view it's represented with this
Little Golden key if you wanted to change it to [a] different field and make that the primary key you can
However, it has to be the right kind of field if you change it to this one
Then that means that there can be no
two people with [the] same name
[onto] that table
So you want to make sure that you set the primary key on the correct field and that's why?
It's an Id
Or something a number that is
Generated by either the system with us showing it manually, but that number has to be unique
So [I'm] going to make this back as a primary key and now let's suppose that we [are] done with a design here
We can click on close save the design changes and now let's learn [how] to create a form
forms typically are utilized for
entering Data you could enter the data in here, but if you have a lot of
Fields here, or those columns and you have a lot of data first
It would be very [dangerous] that to delete records that you're not supposed to delete
But secondly it's not the most use friendly interface to enter your data
So therefore what we can do here is we can go under the create tab and you want to create?
What's called a form the forms you can create them from scratch?
You can design them from here, or you can use
The Form Wizard and as recommend that you utilize the form Wizard
it's much simpler much easier to do this go under Form Wizard here and
Basically the way, it works is if you tell the system. Which table you want to use in our case
we have [only] one table here, so
Then you pick here
What fields you want to include in that Form by the way you can design?
Multiple forms based on the same table. You're basically saying I want to feed data only for first name last name and
address and you give access to a specific individual to just utilize that form
While other individuals may have access to update more
areas of your database so here we are just picking the
Fields that we [want] you can pick them one at a time
Or you [can] simply add all the fields in one shot here by clicking on this double arrow icon
And then click on next then you can choose
How you want your form to be displayed and organized and you can play with this on your own but typically columnar?
Basically go up and down in the sequence basically
then click on next and then you give it a name and then
[it] says open the form to view or enter the information or open to modify the design even further
So in our case here. We are simply going to click on finish
Notice it has designed our form for us to enter the data. So notice it has
Displaying the first record that we have entered already in our table
now
you might say well [that] this field file1 File2 File 3 this is because we chose one of the fields to have attachments and
These are just the attachment fields here
Also, keep in mind that you can design this form and altering about between the design of forms in that or later as well
But for now, we are just learning about the basic concept of how forms work in a database
And how they relate the tables so stay tuned for the other
Functionality there so in our case here the form here serves to display information
That is already stored in the tables or it also serves
For us to enter new records and create add new customers here
So if I click here add a new blank record, we leave the customer. Id alone here and
Then we just fill in the information and notice that this it's it's not quite
lined up properly and all that type of thing I'll show that the moment how to readjust the size of those and
Then [you] basically just fill in the information if we go to the next record. It's basically it's stored
What we just entered?
And then you can just create a new one and keep on doing this so typically
It's the forms that your assistant is going to use to enter data and lookup information
typically your users
Do not really need to touch the tables and entering Data and such
Because the system will enter the data using the forms
So hopefully you get the idea there now if I have to go here to my table [by] the way you need to close any
of those other things that you might have [open] here in the main area and
We open the table here notice [that] I have now Jim. Smith my new customer that I entered
manually, what did I enter through the form so again the forms are very similar to air the input function in the
Computer and you utilize them to view what's stored in the table and to store new Data in the table?
Now to modify [this] form just very briefly here. You can click here under view
And then choose layout view and you can kind of Spin. It. Just simply resize this stuff if you need it to
So this is one way to resize it or to adjust the design it
It's somewhat simpler
The other [methods] to modify the view of this form is
By clicking here [on] view just like we use the design view to modify
The design of the table we can use a design view to modify the design of our form here
so if we click on the design of view now it becomes a little bit more complicated and such that's why I said that the
other
Method was slightly simpler here is where you can change
in
a more
precise way
The design of this so here you could actually go and say instead of first name without the space there you could
simply go ahead and change it put the space in there you can format that to be in gold and
You could change the design of those fields and such
Additionally from this view and it's not time [for] us to learn about it
But if you needed to its these second fields here the ones in white that are basically
Connecting so you don't want to change this word in here to put it a space. That's the code
That [it's] links to the table because basically it's saying it's going to pull
from that first name field on the actual table
so you don't want to change these if you [right-click] in [here] and
Unfortunately not displayed here for you to view it
but if you go to properties in the bottom because of my recording the way I have the recording of the screen and
Notice is the control source. It says here, so it's pulling the data
From the customer information table, and it's pulling this from the field
First name, so this is more complicated
Just for your information, you don't want to change this second area here, then once you're all set here
We can close this save the changes to it and if we go back [to] our form
Know this first name here. It's in Bold and such
So hopefully this makes sense on how the forms relate to the tables in an access database
now let's create a quick query here and
let's see how queries work in a very basic way, so if we go here to our table, and [we] click on create and
now we
go here under the
Query of [Wizard] and the way queries work is basically you can connect to a table and you are saying
List all the customers from that table or list only the customers from a specific zip code
Or a specific city or a specific this state, [we] are putting criteria within
That query or that question so bizzy's are simply asking a question
Give me a limited information based on this specific Criteria. So here
Let's say we want the [first] name last name and then the street address in the city and zip code
then we click on next click on next and
then it says customer information query click on finish and notice we got only what we
Requested so we have first name
It's listing all the customers by first name by Last name Street, address City
And zip the concept to keep in mind here is is that there is no data stored?
within this query itself [as]
the Data is still stored in the table the query just stores the parameters of
What our criteria?
Is so to run this query all you have to do is double [click] here [on] the query and it pulls the data right away
If we were to go and change specific data here for a specific customer
Let's say we had a fourth customer here
And I'm entering this using the form
now if I go and run this query
Notice I have customer [4] listed here
So the idea is is that you can create as many queries as you want whatever
Options that you want. You know the manager might say I need a query I need to know how many sales
We did today, so you could say okay. Give me all the customers
Give me all the items that were sold, but based on a specific day
The week or and such
so that's very briefly the queries if you need to change and we learn how to modify the query so just
check next
tutorial on customizing and working with queries in a database
Now next in this session
[we're] going to learn how to create a report a
reports in a database are a key element as well as very similar to
outputting information you want to print it out particular leads to
Organize the Data in a certain way
it's very similar to queries, but the queries are displaying the data in a very excel like
worksheet here
But the reports are designed to look nicer so you can print them out and hand them over to somebody
so to create a report again you under the create option here or the create tab and then you want to go on the
Reports and my suggestion would be that you utilize the reports wizard. So here you're picking
What table you want to pull the report from notice that you can pull a report from queries as well such
Reports are built from tables or from queries, so whenever you build a report on the query if the report is actually going
To that query and pulling the data so for now, we're going to use it directly from the table
And then we say I want the customer. Id let's say the first name last name and the [state] address and
Whatever else that you want here zip code and such and then we click on next now
[this] stage we have the option
Next and we can choose how to sort those customers. Let's say by first name
I by his last name or by customer Id and you can put different
Searching options here or sorting options click on next click on finish and now
Notice here. We have the report called customer information notice
it's slightly more visually pleasing here and
You can also adjust the design of this very similar to how we did earlier for other components here
so we can go to just the design by going to the layout view and
Then resize those fields accordingly
Now typically the numbers here this number of sign that means that it does not fit in that field so [you] need to
still resize this or the other method here to
Adjust the design of this is by going here under view and I go under design view [so] [again] the concept is
whether you want to change the design of the tables the forms or
the queries or reports
You have the different views that you can change the design of them and particularly you want to use the design view
For designing any of those components of the database now when we are done with this report
We close it since we [tinker] through the design here
Just click on save to save it close the other elements as well, and then go here under customer information
this is our report and
that's
the Data that it's pulling from the table here, so
hopefully all this makes sense in how a database are a very basic database with one table works and
How the various elements of the database particularly those key?
Four components there are other components that you add as well such as macros and things of that nature
but these are the main
Key components of a database let's use utilize
typically
Now in this session. We have not learned how to create
additional tables in our Microsoft access database
Typically an access database or any database how they will have more than one table
And you can have hundreds and thousands of tables depending on how big your database is for this tutorial
And you should have access to a link to download the working files
the working file is a [sip] file here and what you need to do is you need to
Double click on it after you have downloaded it and then you want to click on extract all
once you accept them all take note where it's going to extract this stuff click on extract here and
Then you'll see three files in there
So we'll utilize those three files to build an access database or to basically build three
Additional tables by importing the data from those files in order for us to get a better understanding
[of] how microsoft access works if we have to enter the data manually it will take us a very long time
So we're going to build three tables one of them is going to be based on customers like the contact
information for our customers the next one is listing of the
Contracts for those customers and then and a third table is going to be the invoices so stay tuned how we do this
With the next session here keep in mind that this data. It's two of those species of Data are in Microsoft excel
So it looks like this, and you can have that data in [Microsoft] excel
already in a system of some sort so basically have the customer the company first name last name and the
Information related to the customer notice a customer. Id here is a unique number
Then the next one the contract list notice you have a contact number the customer Id
You have [a] contact amount. It's been our excel spreadsheet [and]
Then you also have this invoices list, and I have this on purpose here as a text file
because you can import data from a CSV file or at a comma
separated values file and this is what that would look like so you have the different values here the invoice and
All the Fields are separated by commas
That's why CSV because of the comma separated values and then notice that each value here is in quotes
so we'll learn about how [to]
Import this into Microsoft access database and create three tables from these
so in this session, we're going to learn how [to]
Create tables in an access database by importing the [data] from another system will import the actual data and also the design
For the tables in one shop from those systems to save us time you can
Import the Data into an existing table in Microsoft access as long as the
fields these crowns and also the datatype
Matches from the external file with your current design in Microsoft access
Since we are starting here understanding the concepts here using microsoft access
We're going to bring both the design because I hope you understand how to design the table and the fields and such
By what we have covered so far in our tutorial if you wanted to do this on your own and such
What you'd have to do?
Basically design the table if you need to bring it into an existing design
You'd have to take [let's] say the customer is and when you design your table in Microsoft access
[when] you go to Those Fields here in the design view
Those fields have to match so you have customer. Id here from your excel file or external system
It needs to match along with the data type
And you'd have to have another field in here called company, and you have to create that then first name
Last name telephone number state address all those have to be in exactly the same order if you [are] going to use an existing
Table in Microsoft access in our case. We are not going to use the existing table
So we are going to create a new table by importing the data from an external data file
So if you how it works we go here under
External Data, and then since we know that our data file was an excel file. We can go here under
Excel and then [we] go and locate that file that we extracted earlier from the working files
so we go here under browse go under the working file and
Notice we have here customer list click on open
And I here we want to import the source data
Into a new table in our current database if we wanted to add [just] the data
Then we'll choose the append option here to just simply add it to the existing table. You can also link it to an external
Table or external data there as well, but that's beyond the scope of this tutorial for now
So we are going to click [on] import Data to create a new table click ok here, [and] then it says show the worksheet
So this is our worksheet. This is what what's in [that]? Table, then we click on next [and] then this is very important
This is where a lot [of] users get lost so we also choose here the first row contains
The headings so the first row in our [excel] spreadsheet
Has personal the field names customer Id?
Company field name last name, so this is just that label
That's what we are telling [the] system that that's what that is
Then we click on next and then here we say you can now specify
about each item that your import is select the fields below and
If you want to allow duplicates, yes, or no
Typically in a database for example for the customer Id we don't want to allow duplicates
So [you] say no duplicates in our case
We're [just] bringing this to play with so minimizing the issues, and we'll just leave it [alone] [now] [the] data type here
It said it's going to be short text. Typically you want to make sure that your custom
Id it's actually a number field so we can change that at this point and say a number to be
Typically, it'll be a double here that you choose the data type and such for sake keeping it simple
I'm going [to] give this a short text then we click on the next now
It says do you want to choose the primary key notice it's starting to create a new one for you
You could create this manually or automatically here the system will create it for you, or you can go and say no
I'll use my own key because I already have the customer
Id however keep in mind it cannot have two records with the same customer ids
You need to be sure that the data that you are bringing in from an external system to create this new database
It actually does not have any duplicates in it, then we click on next here
So our [name] for this table [record] customers, and then simply click on the finish
Notice we can save the steps if we want it, but we don't really need to save at this point
We click on close and now if you have another table here
And if you double click [on] it you have all of those customers there are 38 of them that it brought from that
table notice that the table now it has the field names in the very top here [and]
It also has the various fields such as the email field and a lot type of thing if you needed to create additional
Fields, then you simply can go here under the home tab and you could go and add in your field
So if you wanted for example a field called common, it's going to add it and then you choose the type here
so we are going to make it a comments field to be a long text here, and then we close the design and
now if we go back to customers
You'll notice that you [have] another field called comments
So that's how you create a table by importing the [data]
From an excel file let's create another table of content at table so again
We go here under external data. We go under excel, and then we choose the file that we want to import
We're actually going to get the contact list
We're going to create in your table along with the structure for that table
From your external data file from our excel file, and then we click ok go next here
We tell is the first row has the actual information
next
Then the contact number right now we are choosing to allow duplicates
But typically you don't want to because this is going to be our primary key the unique identifier for each record here
And we're going to leave it alone for now, then we click on next
Then you want to make sure that you're choosing your own primary key
And you're making a contact number as the primary key for here
Then we click on next and then we just say this is going to be called a [contact] table click on finish
click on close and now if we close this
[we'll] have contracts and customers and notice there are [sixty-five] contracts at this point
And then we also have the customers now this next session
We're going [to] learn how to import data how to create a table by importing data from the CSV file
this session
We're going to learn how to create a third table including the structure from a CSV file in Microsoft access
So we have here this invoices list we want to create a table called
invoices and that data we import it from some kind of system out there that comma
Separated values, so the creates a new table you. Go here under external data and well to import the data from
A text file, so [we] click on next here
Locate wherever our file is and notice
It's under my working files, and this will be also in your working files if you expanded it
We choose invoices list we click on open
Then click [ok] and then we want to tell the system here that this is a delimited type of data file
which the values are
Separated by a comma and you'll know that when you receive the file from whatever system there
It will typically be [a] CSV file
And if you click on next and then we want to tell the system that this is a comma separated
values so the values here for each column
They are separated by [a] comma I'll go next again so choose comma and notice here
It says first row
Containing the field names so make [sure] you select this line if you skipped it go back and try to follow it
so in comma first row contains the field names click on next and then
We leave this the way they are for now
Click on next then make [sure] you choose your own primary key and the invoice [number] is going to be the primary Key
Next and then this is going to be invoices and then click on finish
So now we have an additional table here
And this no table is called invoices notice one key aspect here if we go to customers. We have your customer
Id this will be our primary key for this table?
So even if we went over here on the design view though this customer
Id has this golden key here?
So that's how our unique identifier for this table now if you go to another table here
So if we go for example [to] contrast and open this up and go under design view notice that contract number is
The unique identifier for this table anytime you are designing a new table that
Table has to have some kind [of] unique identifier or primary Key
now notice as well here that this table also has
Another field called customer Id and the customer Id here
We saw it that it was a primary key for the customers table that we saw earlier
This was on this one what that means is [that] for the table contracts the primary key is contract number
That's the main differentiator and now
customer Id is
What's called a foreign key basically it will give us the option to link customer
Id from this table to the customers table with customer
Id field [so] the concept there is is that in a database?
In a relational database the tables are linked together
One to another and such using primary and foreign keys
The foreign Key is the common field
Between two tables [if] that field that is the secondary it's not the primary
Key on that second table, so for example here
We are saying we have a contract with this number
But then this contact is associated with a customer of a specific number
so if we go here under for example customer
1105 and We go here under contracts and
we see
7W5 that's saying that this contact
[3033] Belongs to
Customer 11 double V the concept to remember is when you're designing this you need to design
also for foreign keys the common field that connect one table to the other and we'll learn how to
Connect those tables in a moment here now even if you went here under invoices you'll notice that the invoices has an invoice number
That's [the] primary Key
But then you have a contract number which will eventually connect to the contact
number here from the contacts table and in the invoices table
So the primary key is this one, but then this is the foreign key for this table
so primary keys and foreign keys are
two very important concepts that play an important [role] when you're designing the
Database because they'll need to be linked together, and that's what we'll learn in the next session here
We've learned how to link the tables using the primary keys and foreign keys
In this session, I [will] demonstrate how [to] link tables in Microsoft access database in access
2016 or how to define the relationships between tables in access?
2016 so a relational database like Microsoft access
2016 it requires that the tables are linked and
Typically the key component [for] linking tables in a database is
The proper design of the tables to start with and typically what you want to do
Is that you want to have the primary keys and the foreign keys?
Properly defined in the tables before [I] further explain this I'm going to clean up our database here
So I'm going to first delete this table that has nothing to do with what we are planning to do in the next
couple of minutes here
I'm going to delete these queries as well by the way you don't want to do this on a real database
And this you're sure that [you're] the delete is and at this point. We have big tables. So we have the customers table
We have invoices table in the contracts table to learn how we did this please refer to the previous tutorial ously
We have the primary key for customer. Id this is the primary key for this table and
then under
contracts we have the primary key being the contract number and
Then the foreign key is the customer id which is supposed to match with the customer?
Id which is the primary key on the customers table, and then the same way under invoices we have the invoice number?
Which is the primary key for the invoices table?
but then the contract number which is a foreign key in this case goes to match with a primary key of
[the]
Contracts table so as it designed that there's a database
You need to factor in a plan on what the Florida keys are going to be and how the tables are going to be linking?
With one another when it comes time to link them and this has to be done during the design process now at this stage
We need to close the tables and then the next thing that we need to do here is we need to go here under
Table tools, and then we are going to define the relationships, so we go here on the relations
And then we choose to add the customers table
Which is to add [a] contacts table, and then they invoice this table and then close this
Now notice you can even organize them any way you want here you want to organize them
Fairly logically if you can so as you see the structure depending on how you organize your database
to link those
Tables together notice we have here customer
Id from the customers table, and we have [the] foreign key here [on] the contracts for the contracts table
So what you do is you click on customer Id from the main table?
And you drag it and you hold the mouse directly on customer
Id on the contacts table here and then you let it go now this point
You know this is saying it's going to use the customer stable
Linking to customer Id in the contacts table
It's going to be a relationship of one-to-many what that means is that you can have one customer?
With multiple contacts and that's hopefully what you want if you have a small business you want multiple contracts from the same customer
You could also
Enable here, what's called a referential integrity?
I'm going to check it for now to keep this simple, but that means that if you deleted the customer
you're probably never going to have that customer again, then you want to delete also their
Contracts that's what their referential integrity is going to do a cascade update and cascade delete if you remove the main source
[going] to remove the items that follow with it if a student drops from
College you want to drop [also] the courses and things of that nature
Then here we click on create and notice that you have a line between the two
The next thing that you want to do then is notice now
We have the [contacts] primary Key here could link will
The foreign key from the invoices table, so we want to say from invoices here
We want to drag this to the foreign key releases table again the same idea
Contact number to contract number want to many relationships that means that you can have multiple
Invoices for the same contract that's what that means and by the way
You can move this however it makes most sense to you
If for [some] reason you chose the wrong thing notice that they [have] to match your customer
Id has to be under customer?
Id it can be linked to a different field if for some reason you have it improperly matched there
You can right click or select [this] once and then you can choose either edit relationship or simply?
delete and then if you want to delete it you say yes, and
Then now it's disconnected and then you can redo it again. So you just drag your customer
Id to customer id and then click on create again, and now you have it the way you wanted it
Once you're all set with this you need to click on close, and that's where the system is going to save
these
links between the tables because
unless these tables are linked together we cannot get
Data from one table to the other and when we link these tables through this
relationship that we can actually go and say I
Want for example a query here or a report of some sort that I want the first name last name
And then I want the contract number and then I want the amount [and] a date and the contact type
that's when you can kind of cross and pull the data from multiple systems, then I click here on close and
Then it's going to ask us to save the relationship here
We say yes, and now the relationship has been defined and now we can actually create queries
We can create reports we [can] create forms to pull data from multiple tables here
so this is where the beauty of
Microsoft access actually takes place
let's very briefly learn just for the sake of testing at this point how to
Create a quick query on pulling data from two or more tables
Just before I finish this section, so you get an understanding of how the relationship
Connection here between the tables what the advantage is so if we were to look here at customers now
Let's have a [customers] table, but I don't really have any contact information for this customers table
If I go to contract, I have only contact information, but I don't really have any customer information
Other than this id so now what we want to do is we want to pull the customer [information] and the contact
information and have it displayed a certain way, so here's how it works [you] go here under create and
Then you go under a query wizard
And then we click on
[ok] and then we want to pull here first from the table
Customers notice that we have three choices where we can choose from so we choose customers?
And then now it's just a matter of whatever we want here
So we say I want the first name the [last] name and then let's say I'm interested now from that table. I'm
Interested on only these two fields the next thing that I want is I'm going to go under
Contract, and then I want to pull here the contract number
I want to pull the contract amount I don't care for the customer
Id because I know I have their first and last name already
And then I want to see the date and the type of the contract so notice it's about six
Fields here from two tables now. I click on next and then
Click on next again, and now give it a meaningful name
So whenever you're defining the queries you want to define it with a meaningful name
contracts by first and last name click on finish and now
notice that we have a new query created and now we have the data first name and
last name and a contract number and
Then the amounts the date and the type and notice so we pull the data from two tables from here you can create all kinds
Of other things whether it's reports or new queries or forms and we'll cover those in next few sessions
One other thing before I finalize is I should have mentioned this earlier is that in order for you to link?
those tables correctly you need to make [sure] that the fields that are going to be linked through the
Relationship are of the same Data type
and this is where a lot of
Users get frustrated because this does not work
What that means is is [that] on the customers table when you go to design your table?
You need to make sure if that [is] a number field if it's numbers you need to make [sure] that this customer
Id over here on the next table [in] the foreign key? It's actually a number field as well
It's the same type of field again here
The contract number on this table and contracts table that needs to match
exactly of the same Data type
formatting otherwise
They will not link and you'll get errors on your linking and more concretely
You can see that in my case. I go here to design view for the customers table. [I] have it under customer
Id I have it a short text field typically it's not going to be a short text you want that to be a number and
Autom number typically I'm not going to change it right now, but typically it's an auto number and needs to match
number and
Order number they can be used as one type of data
So that's fine, but you just need to be assured they match so in my case
I have short text here, and if I go to my next table
under contracts if you look for my
Customer Id here it's going to be
Short text as well. That's why [I] didn't run into any problems
So those can be numbers or all the numbers that they have to match on both corresponding tables
Remember that in your design, and it will save you [a] lot of frustration
So that's how to defining the relationships in an access database works. It's one of the key aspects of designing a database
successfully
