Hello my name is Sali Kaceli. The following is a
comprehensive tutorial on using Microsoft access. During this tutorial I will cover basic concepts
related to databases and how to design and use a basic database using access. 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 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're providing to your customers, and then you might have a list of invoices
and other purchases from vendors and things alike. 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 retrieve 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. Then 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 very similar to the output they summarize the fields or records from a
table or a query in an easy to read format. For example that would be a 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 an actual 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 contract table, and then we have invoices. So think again about
these as separate lists. So the customer table of course it would have the list of customers.
We have here the customer ID, company, first name, last name, telephone number,
street address, city, state and so on. So those would be very similar to what we have
seen in excel. You can sort this data you filter it and things of that nature but think of it just
as an as a list. Now besides the customers if you're running a business then you'd probably
have different contracts or different sales so in this case this is another example of a
table the contracts table if we double click on it we have the contract number the customer ID
contract amount the date and then the type. And then notice we have another table here called
invoices and the invoice table has the date number and the item what it was for the
amount and whether it was paid or not. So those are the tables. Again this is where
the data is stored into a database. Think of it when you hear tables storage. The next component
in the database like I mentioned earlier it is the queries. So if we go here the queries
for example are like we have the table customers. Notice we 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 let's say email address and some other pieces of information
here. We create what's called a query and we'll learn how to create queries momentarily
here. So notice I have another entry here under the customer table and this is a query that
we have run that I defined earlier. If I double click on it notice I have only the first name
last name telephone number and email address. 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 you'd go under the customer data entry form for example in a 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 would utilize for entering the data into the into the database.
And then the final component as I mentioned earlier it is the reports and the 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 onto and then the reports
how the data is printed out or output. Once you're in Microsoft access you'll notice
it very similar to Microsoft word and excel and other applications. On the left hand side you'll
have the recent databases or 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 you will be utilizing in a moment and then
further down here we have different templates that are available in Microsoft access. 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 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. Now here's a concept that you might want to keep in
mind when dealing with Microsoft access databases. Unlike word documents in excel where you open
a document and you create the document and then you manipulate it and all that type of thing,
in Microsoft access as soon as you choose to create a database you have to give it a name and
you 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 predefined with all the components and such so it's slightly
different for creating a database from scratch. So first thing that you're presented here is a
tutorial on how to use this not every one of them is going to give you this option so we're going
to simply click on 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 and typically a database has four components just like in a computer that you have
the four basic functions of a 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. So it's very similar to the output in your computer.
So in our case here we have this form so if we wanted to add a new student we simply click on new
student and then we fill in the information there. You can add the picture you can add additional
information then 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 form. So this serves
as an 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 would 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 we'll 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, the next one would be the forms that we just used a moment ago and then if you
wanted to create a report of all the students you simply click on all students and it
will display a more visually pleasant report for you to print it out. So those are
some of the basic concepts using the template. So now stay tuned for the next session that will
learn how to design our database from scratch. We'll create the first table and then
start building a couple additional tables. In this session we are going to create a database
from scratch and we are going to learn about some of the various components of a Microsoft access
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 a 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. 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 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 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 for this new table. Next to it here you have also tell me what you
want to do or the tell me feature 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 it 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'll 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 fields
so you'd have the ID field, the first name field, and then 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 you'd say this is record one or record two or records 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 related to that specific customer. Here this
would be the field name so right now it says ID so we could change that to say customer
ID the 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
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 street 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. Now 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 could 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 we could 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 this
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 the data typically we for now we're going to enter it here
directly into the 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 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 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 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 some 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 ID, a unique customer
ID, and those are typically referred to that becomes your primary key. The primary key again
that is what it 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 it a 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 is a data sheet 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 is the column for each column in that table that we saw earlier so we could change
the names here or you 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 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-digit
abbreviated version of it or you can make it 40 characters long or whatever the length of
the field there so you can define the field. You can also change the format and 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 structure 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 long
text so you want to make sure here that the user can enter enough text I believe
that would be 64, 000 characters that it will accept in that field when you start typing on it.
Once you're 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. The 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. In this brief video we're going to learn how
to add additional fields to a table in an access database and then we are 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. 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 you 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 we could do it a couple 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 want to change this on the drop down
here to be yes or no. What 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 tweaked that you can add additional fields in there by
the way you have under description in the design view you have an area where you can actually put
notes design notes for you as a designer of this database you could say additionally you can go
up further for any of those fields and such. As we covered very briefly in the last session each
table needs to have a primary key and the primary key can be a number field it could be anything but
it has to be something. Typically it's a number and 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 with the same name and 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 on that table so you want to make sure that you set the primary
key on the correct field and that's why typically it's an ID or something a number that is generated
by either the system where you assign it manually but that number has to be unique so I'm going to
make this back as the primary key. And now let's suppose that we are done with the 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 user-friendly interface to enter new 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 I'd
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 that 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 is basically going to 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 this it's displaying the first record that we had entered already in
our table. Now you might say well about this field file one file two file three 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 we'll learn about
changing the design of forms 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 to 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. Now notice that this it's it's not quite lined up properly and
all that type of thing I'll show that in a 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 look up 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'm to go here
to my table by the way you need to close any of those other things that you might have
opened 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 or that I entered through the form. So again the forms are
very similar to the input function in the computer and you utilize them to review 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 just
simply resize this stuff if you need it to. So this is one way to resize it or to adjust
the design of it. It's somewhat simpler. The other method to modify the view of this form is by
clicking here on view just like we used the design view to modify the design of the table we
can use the design view to modify the design of our form here so if we click on design 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 bold 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 it's these second fields here the ones in white
that are basically connecting so you don't want to change this wording here to put it a space
that's the code that it 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 I cannot display it 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 that the
control source it says here. So it's pulling the data from the customer information table and it's
pulling the from the field first name. So this is more complicated but just for your information you
don't want to change the 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 notice first name here it's in bold. 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 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 state so you're putting criteria within that query or that question. Basically you're
simply asking a question give me limited information based on this specific criteria. So
here let's say we want the first name last name and then the street address and 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 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 you 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
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 to know how many sales so you 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 of the week or such. So that's very briefly the queries. If you needed to change
and we'll learn how to modify the queries so just check the next tutorial on customizing and
working with queries in a database. Now next in this session we are going to learn how to create a
report. Reports in a database are a key element as well it's very similar to outputting information
you want to print it out particularly it's to organize the data in a certain way. It's very
similar to queries but the queries are displaying the data in very excel-like worksheet here. 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 go under the create option here the create tab and then you want
to go under reports and my suggestion would be that you utilize the report 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. Whenever you build
a report on the query it 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 street address and whatever else that you want here zip
code and such. And then we click on next, next and we can choose how to sort those customers
let's say by first name or by 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 and adjust the design by going to the layout view
and then resize those fields accordingly. Now typically the the numbers here this number
sign that means that it does not fit in that field so you need to still resize this. Or the
other method here to to adjust the design of this is by going here under view and then 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 a design view for designing any of those components of the database.
Now when we are done with this report we close it. Since we tinkered with 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 our 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 that you utilize typically. Now in this session we are going to learn how to
create additional tables in our Microsoft access database. Typically an access database or any
database out there 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 you should have access to a link to
download the working files. The working file is a zip 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 extract 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 had to enter the
data manually it would 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 the contracts for those customers and then and the 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 pieces 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
we have the customer the company first name last name and the information related to the customer.
Notice the customer ID here is a unique number. Then the next one the contract list notice
you have the contact number the customer ID you have the contract amount it's another 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 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 it's csv because of the comma separated values and then notice that
each value here it's 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. We'll import the
actual data and also the design for the tables in one shot 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 columns and also the data type matches from the external file with your current design
in Microsoft access. Since we are starting here understanding the concepts here and 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 a
table if you wanted to bring it into an existing design you'd have to take let's
say the customer list 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. Then
you'd have to have another field in here called company and you'd have to create that. Then first
name last name telephone number street address all of those have to be in exactly the same order if
you're 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 here's 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 working files
and notice we have here customer list. Click on open and then 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 we click ok here and then it
says show the worksheet so this is our worksheet this is 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 want to choose here
the first row contains the headings so the first row in our excel spreadsheet had for example 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 you're importing select the fields below and do you want to allow
duplicates yes or no. Now typically in a database for example for the customer ID we don't want to
allow duplicates so you'd say no duplicates in our case we're just bringing this to play with so we
minimize any issues and we'll just leave it alone. Now the data type here it says it's going to be
short text. Typically you want to make sure that your customer ID it's actually a number field
so we can change that at this point and say the number this would be typically it would be
a double here that you choose the data type and such for the sake keeping it simple I'm going to
leave this as short text. Then we click on next. Now it says do you want to choose the primary key.
Notice it's trying 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 here. However keep in mind it cannot have two records
with the same customer ID so you need to be sure that the data that you're 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 we'll call it customers and then simply click on
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 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 on the very top here and it also has the various fields such as the
email field and all that 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 a new field so if you
wanted for example a field called comments it's going to add it and then you choose the type
here so we're going to make the comments field to be a long text here and then we close 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 a contracts 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 a contract list we're
going to create a new table along with a structure for that table from your external data file from
our excel file and then we click ok. Go next here we tell it that the first row has the actual
information next. Then the contract number. Right now we are choosing to allow duplicates but
typically we don't want 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 the contract number as the
primary key for here. Then we click on next and then we just say this is going to be called the
contracts table. Click on finish, click on close and now if we close this we'll have contracts and
customers and notice there are 65 contracts at this point and then we also have the customers.
Now in this next session we're going to learn how to import data or how to create a
table by importing data from a csv file. In 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 imported from some kind
of system out there that's comma separated values. So to create a new table we go here under external
data and we want 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 would 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'll typically be
a csv file. Then we 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 contain the field name so make sure you select this one
if you skipped it go back and try to follow it. So we want comma first row contains the field
names click on next and then we leave these 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 in this new table it's called invoices. Notice one key aspect here
if we go to customers we have here customer ID this will be our primary key for this table so
even if we went over here under design view notice this customer ID has this golden key here. So
that's our unique identifier for this table. Now if you go to another table here so if we go for
example to contracts and open this up and go under design view notice that contract number is the
unique identifier for this table. Anytime you're 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 the primary key
for the customers table that we saw earlier so it was on this one. What that means is 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 would 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 the using primary and foreign keys. The foreign key is the common field
between two tables it's 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 contract 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 11005 that's saying that this contract
3033 belongs to customer 11005. The concept to remember is when you're designing this you need to
design also for foreign keys the common field that will 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 a primary key but then you have
a contract number which will eventually connect to the contract number here from the contracts
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'll learn how to link the tables using
the primary case in the foreign case. 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 we 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 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 unless
you're sure that you want to delete these and at this point we have three tables. So we
have the customers table we have the invoices table and the contracts table. To learn how we
did this please refer to the previous tutorial. 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 it's a 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 the foreign
key in this case goes to match with the primary key of the contracts table. So as a design that
the database you need to factor in and plan on what the foreign 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're going to define the relationships. So we go here under relations and then we choose
to add the customers table, we choose to add the contracts table and then the invoices 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 that you see the structure depending on how you organized your
database. To link those tables together notice we have here customer ID from the customers table
and we have the foreign key here under 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 contracts table here and then you let it go. Now this point notice
it's saying it's going to use the customers table linking to customer ID in the contracts table.
It's going to be a relationship of one to many. What that means is is that you can have one
customer with multiple contracts 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 the referential integrity I'm not going to check it for now to keep this simple
but that means that if you deleted a 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. It's going to do a cascade update and cascade delete if you remove the main source
it's 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 contracts primary key here could link with the foreign key from the invoices table so we want
to say from invoices here we want to drag this to the foreign key from the invoices table. Again
the same idea contract number to contract number one 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 here customer ID has to be under customer ID it can be linked to
a different field, if for some reason you had it improperly matched there, you can right click so
or select it 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'd 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 the date and the contract type. That's when you can kind of
cross and pull the data from multiple systems. Then we 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 notice I have the customers table but I don't really have any contract
information for this customers table and if I go to contracts I have only contract 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 contract information and have it displayed a certain way.
So here's how it works. You go here under create and then you go under 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 want to go under contracts 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 we 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 the contract number and then the amounts the date and the type.
Notice so we pulled 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 the next few sessions. One
other thing before I finalize this session I should have mentioned it earlier is 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 in
the contracts table that needs to match exactly the same data type formatting otherwise they'll
not link and you'll get errors on your linking. And more concretely you can see that in my case
if 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 an auto
number typically. I'm not going to change it right now but typically it's an auto number and needs
to match number and auto number they can be used as one type of data so that's fine but you just
need to be sure 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 auto numbers but 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 defining the relationships in an access database works it's
one of the key aspects of designing a database successfully. In this session I'm going to demonstrate how
to create a compound form to input data between two or more tables in access. So once we have
linked the tables and defined the relationships in a database then you can do a lot of wonderful
things and you can enjoy working with Microsoft access. And now we have linked those tables
together and we have defined the relationships to basically create a form.
Forms that can be created either on an individual table for example if I go
here under create and I go under form wizard here and let's say I want customers I want to create
a new form for all my customers click on next and then next and then next again. So there is my
form for the customer. So this is a simple form in Microsoft access. To create a compound form and
by the way the form now has been saved here under forms customers go under create here go under
form wizard and then we choose customers let's say I want everything for my customers table and
then I want also data from our contracts table. And here under the contracts I want to make
sure I have the contract number I don't need the customer ID because it's going to be in the
customers table then I want the contract amount and then the signing date and then the type
as well and then click on next. At this point it asks us how do you want to organize the data.
We want to see basically since our customers are going to have more than one contract it's a
one-to-many relationship we want to organize it by customer so we have customer one and then you
can see three or four contracts customer two one contract or five contracts or 15 contracts or
whatever they have and then we leave everything else alone here and then click on next. Then here
we click on next again and then we give it a name so we could actually say contracts by customer
and then click on finish. By the way make sure you give a meaningful name there and now at this point
notice what happens here. So we have the customer on the top here their customer information but
then right below this customer we also have the orders or the contracts. You could go and add new
contracts if you wanted here so you could have and such. So now for for this customer we have
a new contract created as well so basically at this point your assistant can use it
to look up customers and their information or you can use it to update the new contracts
or new orders related to that customer or your customers. Notice you have also these
controls here so you can go from one customer to the next so you go next here and by the way
you'd have to adjust this. So if you click here on customer on the customer area now we can
go from one customer to the next and so on. If you want to go from one order to the other and
add new orders you have also those controls for this sub form. This form here is a mechanism to
enter new data view data. You have the main form which is contracts by customer but then right
below it you have the sub form which is pulling from the contracts table. So we have the data
from two tables being displayed at the same time and then you have the main controls for the main
table over here and you can create new records as well if you need it by using this button right
here and then in the bottom here you have the actual orders or the contracts for the customers.
To update data in here whether you want to change to add new comments or whether you want to
add new forms or new customers from here. Now remember all the data that we enter from here
it actually goes and gets stored in the actual tables so if we go here under customers we
should have this new customer that we just entered earlier. So that's how compound forms
work and how you create them and update them and use them. The next session I'm going to show
you how to customize the form in Microsoft access 2016. Earlier in this tutorial I created a
form called contracts by customer. This is what it looks like so far and the purpose of
this session is how to customize this form. There are three different views for customizing
anything in Microsoft access. Here on the left hand side you have the layout view and then you
have also the design view. One thing that you can do definitely is you can customize this and
make it smaller or bigger or however you want to resize this. So let's say you want to make the
text there formatted slightly different and you have the formatting tools and basically change it
however you want very similar to Microsoft word. Now the next thing that you might want to do here
is that you don't really need this customer ID data so big and such so you can resize
it and we are doing this by using the layout view. The next one is go through each
field here and customize this to your liking. Additionally you can change the formatting for
this so it will be a little bit easier to read. You want to make sure that you adjust only
the stuff here on the left and not elsewhere and basically you're formatting this to whatever
you want so you're customizing it to your liking. Since we are formatting this and changing
the layout view notice that you have the form layout tools. These are the contextual tools
related to this form in Microsoft access. We have these different tabs here that you can change and
tweak and such but one of the cool things here is that you can apply themes. Instead of you spending
all afternoon tweaking this and changing this probably the width of these fields customize
them manually here as far as the colors and such what you could do is you can go and simply apply
one of those themes. So you pick from one of those themes here and notice it's going to change
the design whether it's the font or whether it's other components related to this form. So pick
one of those themes and then further adjust it and then notice that you have also various color
schemes here that you can apply didn't change too much but you get the idea and then you can apply
also the different fonts as well. So that's one way to customize this. If you wanted to tinker and
insert images and such you can utilize this and you add logos and such and then you could click
here under property sheet and this tells us where this field is linked to. It controls the linking
to the table itself. So for example right here it says that it's linked to the comments field on the
customers table and then notice there are lots of controls here that you can change. Typically you
don't want to tinker with this or to change this to something different because then it will
break that link and then the data will not be updated. This is one way to customize this form.
The next way to customize is going into the design view and this is another mechanism so I'm going
to save these changes first this is a little bit more in-depth. It shows the data where it's
linked behind the scenes with what fields and you can line things exactly the way you want
them appropriately and all that type of thing. Notice that you can change here colors and
alternate rows and background image and all that type of thing and the fill and all that type of
stuff. That is more advanced that you can resize stuff move things around if you needed to but in
a nutshell that's how you customize. You customize it by going through these different views that we
just went over here. Once you are all set with a design and such then you can close your design
save the changes and then open it up again and it should have applied the changes that we made
earlier. You want to make this forms as nicely as you can so that your staff that is utilizing
these forms is actually using them effectively and also it's pleasant and easy for them to
enter the data. Keep in mind that you can create more than one form based on the data
so if one of your assistants needs access to only a couple pieces of data here then you create
a form specifically for them and for others you create a more complete form and such. So hopefully
that makes sense and that it was helpful. In this session I'm going to briefly demonstrate
how to search for specific records by a specific field via a form in a Microsoft access database.
So supposedly this is our form here and we have contracts by customer and now your assistant is
keeping track of your customers and also keeping track of orders and such and now a new customer
called and how can she pull up the information for a new customer. So you can pull up the information
by using a variety of methods here by using any of these fields. All that the assistant would have
to do is click on a field that says searching by customer ID she simply needs or he needs to simply
click on the field here click on find and then type the customer number so for example 11040 then
click on find next and then notice we have that specific customer in here. The other thing that
you can do or she could do it or anyone could do here is searching let's say by first name or by
some other field. So I'm going to go back here and let's say by first name you can click on the field
there and then choose Michael find and you can have this over here as well and there is Michael
Ingram phone number and information related to that specific user. Then you can find additional
ones if needed as well. So notice there are two records with that. You can also filter the records
by a specific field so let's say you wanted all the records to be displayed the customers
that start with the name first name Michael so you can select it here and go under filter and
then notice you have all the different options here so you can simply uncheck them all and then
pick what you want here so let's say Michael and unfortunately it's beyond the recording area here
but you can you get the idea it's going to be Michael or whatever. But let's say Jessica here
I'm not sure that there is more than one Jessica but let's say Jessica and John and then I'm gonna
uncheck Michael here. Now notice there are three filtered results that showed up here and we can go
from one to the other to the other so there were two customers with the name John here. Keep in
mind whenever you have filtered stuff it's going to display only what you filtered by and then
this field right here where it says filtered it's going to be highlighted there. You can
do that with any of those fields by either searching and finding records from up
here or by filtering from this option over here. In this session I'm going to demonstrate how to
create queries from multiple tables in an access database. We'll create those queries using the
query wizard and then in the next session we are going to do the advanced query method. In order
for us to create queries from multiple tables we need to first make sure that the tables have
been linked via these relationships here so you can check that from the database tools and then
relationships and you should see these links. And notice here that we have the customers
table which is linked to the contracts table. So let's say that we want to to create a
listing or a query as the technical term is of the customer ID the first name last name street
address and then the city, zip, state and then we want to also list the contract information
for these customers. So here's how we do that. We'll close this first and now we go here under
create and then we go under query wizard and then click on ok and then we are going to go first to
customers we're going to pick customer ID first name last name state address city zips. And then
we go to the next table here we go under contracts and then we'll pick up the contract number and
then the amount date and type. Then we click on next and then click on next again and then
here we could have some kind of meaningful name and then finish. Notice at this point we have
the list with the data that we requested customer first name last name and such and the data has
been pulled from both tables. So that is creating a query using the query wizard. You could do also
the similar thing as well if we save this now we can go and create one for invoices. So let's say
we want to see the contracts and how many invoices remain to be paid. So we go here under query
wizard and then go under simple query wizard again and we can do this with two other tables so we can
go under contracts we can get the contract number and then we can go under invoices and
then pick the invoice number the date and whether it was paid or not and then next
and then give it a meaningful name click on finish and here we have the contract number the
customer ID the contract amount date the type the invoice specific number the date that the
invoice was issued and whether the invoice was paid. So next we are going to learn how to
utilize the advanced queries in a database. In this session I'm going to briefly demonstrate
how to utilize the query design in access 2016. So far in our access tutorial we have
learned how to use the query wizard in defining and designing a query but in most cases
in access 2016 and previous versions of access a more effective way to utilize queries and
design queries is to use a query design. The way that works is that instead of you going
through step by step and adding specific fields in a query you can actually design this query
using this method. So to utilize the query design we click here on query design icon and then the
big idea here is that you pick the tables that you want to work with initially. So in our case
here the tables have been linked by using the relationship module that we saw earlier and now
we'll pick the tables that we want to utilize and then click on ok. Typically the relationship
looks like this so we have customers then we have contracts and we have invoices. The way
it works is that you have these tables here with all the different fields from each table
and you pick specific fields from each table and you're creating a new query. So for example
we want here first name last name and let's say the invoice number and typically you can either
double click on these fields that you want to add to the query down below or you can simply drag
these fields down here like we did a moment ago. If you double click on this asterisk sign it will
insert all the fields that are part of that table. Now here we want for example the invoice number
the contract number and I'm double clicking at this point the item and the amount and then
we want also whether it was paid or not. Now let's assume that these are the fields that we
want in our case now you can run this and see what it looks like. Notice you have the first name last
name actually they are kind of backwards you can readjust that we can go back and readjust it and
we have invoices paid or not. If we want to tweak this query again we go here under design mode and
go into design view and then tweak this again so if we wanted for example first name to be first
simply drag this to the left and once we move the fields the way we want or customize this view
the way we want then we can run this again. And now know this first name is in the beginning and
then you have last name and so on. So that's how you briefly utilize the query design in Microsoft
access 2016 in an access database. Notice as well if we go back here to the design view you can
define the sorting order you can define specific criteria whether the criteria is either or or
different various criteria by various fields here and that's what we'll learn next. And before
I finalize it completely here notice also there are additional parameters that you can utilize
here and we'll cover these shortly as well such as the query builder and the totals field and such.
In this session I'm going to briefly demonstrate how to utilize criteria how to define the criteria
within a query in Microsoft access 2016 using the query design. So here's how it works. Let's say we
have this query here called invoices and contracts actually we have not named this yet it's query
number one we go here under query design view and now let's say that we wanted to see for
example only the invoices that have not been paid. If we go under the invoices table and notice
here that this is a yes or no field to enter a criteria so we see only the invoices that have
not been paid. We go here under the design view and what we want to do is here under invoices paid
notice there is an option for criteria there is a row here that we can put a criteria for this
specific field. So we want to display only the invoices that have not been paid. In that case we
want to display those that meet the criteria for no so we simply have to put no in there and
now click somewhere else outside of this area and then run this query. Now notice it's
displaying only those that have not been paid. If we wanted to display the text here
whether yes or no or invoice is paid with a wording no next to it just to be
sure we make sure that this check mark and by the way that should be there automatically
make sure that it has the check mark right here. Now if we go and run it again notice it says
invoice paid no and it's displaying only those. If we want to hide that field we go back to the
design view and then just take out the check mark and then run it again and it will not display
it. So that's how you insert a criteria within a query using the query design. Now you can have
multiple criterias as well. If we run this query right now notice that there are invoices ranging
from a thousand dollars to more than thirty thousand here. So let's say we want to see only
the invoices that have not been paid of greater than ten thousand dollars. So to insert that
criteria we go back to the query design here and then under the amount for the specific
criteria we want to put them so the both criterias would be met we say greater than equal
ten thousand and then click anywhere outside of this field and then run this. So we are saying
we want the criteria all of these fields plus the amount needs to be greater than or equal to 10,000
and then the invoice needs to be not paid. We run this and now notice we have all these invoices
displayed. The other thing that we could do is we could sort this and we can go back here to
the design view again and customize this further so under the sorting criteria we say we want
to sort this in descending order when I see the largest amounts first followed by the smallest
ones. So notice we are doing three things so far. Click on run again and now notice the 105,000
invoice comes first and then the rest are following that. Then if we go back to the design
view we can even insert as many criterias as you want and I hope you get the idea so you could
sort for example by a specific zip code or by a specific city and so on. Under city for example if
I wanted to add an additional field and I want to insert it right there just simply drag it in there
and then we could have various criteria. So right now I don't have any criteria by city and if I run
it notice it's just going to display the city but let's say that I want the city Lansing or Holland
so I want to display those two cities. Now in my case here I can go back to design view and I'll
enter two criteria so one of them will be Lansing now you have to type that correctly and I can
say or Holland so it could be either one of them and then run them. Notice it is
displaying only the city Lansing or Holland but then notice that the criteria
is not quite what we were expecting earlier. Notice that we have 2,500 so it's no longer just
10,000 or more. The reason for that is because we have here in our criteria stating that the
criteria could be Lansing greater than 10,000 an invoice is not paid or anything from the city
called Holland. So we either have to move this up here or utilize the criteria either or that
we are using earlier but then keep in mind that it's not going to apply so we have to put them
like this with the or here and the additional two criterias then we run it and notice these
are the only clients that have not paid their invoices yet with the greater than 10,000 balance
and only Lansing or Holland just for those two cities. So that's how the query design works
with multiple criterias within the query. In this session I'm going to demonstrate how to
use the query design and also define calculated fields or have calculated fields as part of the
query design. Let's assume that we have a bunch of customers they have not paid their invoices
and we want to calculate a late fee. So part of our query we want to display what the late fee
calculation would be. So here's how we do that. We go here under the query design and then first
thing we need to pick the tables that we want to utilize so let's say we want customers then
we want contracts and then let's say we wanted the invoices. So I'm just going to list those
three tables that we are utilizing at this point but in reality I'm going to use only customers and
invoices in this case. So we want the first name I'm double clicking on them last name and then
let's say you wanted to street address, city, state, and zip and then we
want also the invoice number the date the item the amount and then we want
whether it was paid or not. I'm going to resize this I'm just dragging it up so we can see this
a little bit easier and now if I go and run this query notice it's going to display the data that
we picked. However it's not filtering yet or it's not giving us only the unpaid invoices. To fix
that we go under design view again and then we go under invoices we say not paid so whether it
was paid under the criteria notice the criteria row here we say no. Now the next thing that we
want to do and by the way if we run this again notice it'll be displayed now if we go back we
want to create here a new field and it will say late payment and then part of that field we wanted
to calculate what the late payment would be. So the way you do that is by clicking here on this
option that is called builder. So we want to build a new calculated field. We click on builder
so first I selected the new field where we want to do this and then secondly we
want to utilize the builder function. Before we can utilize the builder function it's
best to save this query first so we could say just click on the x here on the top right and
we'll say yes. Then we'll call this late fees and then click ok. Now we go back here to the late
fees and we run this query then we go under the design view by the way you could right click on
it and choose design view as well to get to it and then we scroll all the way to the empty new
field here and then we want to click on builder. On builder by the way once you save it notice
we have all these fields right here under the expression categories. That's basically telling
us that these are all the fields that are being utilized as part of this query so all that we
have to do is we click on the invoice amount and double click on it and notice it's putting it
in brackets and such and then we do the asterisk which is the multiplication so we want to say the
amount multiplied by some kind of percentage so you could say it's five percent late fee for
any unpaid invoices past 30 days or whatever. So then you do that by times zero point or
as part of your database you could design another field in there or column to say late fee
like how much the late fee would be and then in that way you don't have to enter the 0.5 manually
you can just multiply the late fee multiplied by the actual amount of the invoice. In
our case we're just going to do it this slightly manually. So we have the invoice
amount times 0.5 and then we click on ok. You could have simply selected another field there
instead of 0.5 like I mentioned a moment ago. Now we click ok and then notice now it enters all
kinds of codes here. Now instead of you choosing to have that as expression one we could have that
called late fees or late fee and then simply run this and now notice if you scroll to the right
you have here the invoice was not paid it's twelve thousand dollars and the late fee is six hundred
dollars and it has calculated this by the way for all the amounts. Now if you don't want this
invoice paid no you can just simply hide it like and I'll show that in a moment. Let's say
we want to format this in in currency in the dollar amounts we can do that as well
and we go back here under design view and then we want to choose to not display whether
it was paid or not just that column we want to hide it and then the amount here if you click on
the actual field and go under property sheet here on the top or you could right click and choose
property and then under the format we want to click on the drop down here and we're going to
choose the currency. And then you can even choose a decimal places let's say two decimal places
and then you can close the property sheet here. Now if you run this again notice that the late fee
will be in currency and that's how you calculate the late fee using the query design in Microsoft
access 2016. Now we save this and then at any point for any of the customers as you enter and
change data this will be generated automatically. In this video I'm going to demonstrate how
to create reports and customize reports in Microsoft access. This process is going to start
with the very basics and then we're going to move into some of the more advanced features
customizing reports in Microsoft access. So in this case we have a database with three
tables and if you wanted to learn more as to how to work with an access database please
refer to the previous videos on this tutorial. So we have three tables we have a contracts table
let's assume we have contracts for customers then we have a customers table and then we have an
invoices table so the tables look like this. Now in our case we also have those tables
linked together which is a common feature of an access database or any of the relational
databases out there. So as such if we go here under database tools and then we go under
relationships you'll see that we have those tables linked together and if you want to learn
more about these you can check the previous tutorials as well. And now we can create reports
based on each one of those tables individually or we can create reports by pulling data
from multiple tables. For example if we wanted customer ID from the customers table and
the first name and last name from the customers table then we can also pull for example the
contract number we can pull also the amount and signing date and the contact type
because those two tables are linked together. So let's learn how to do this. To create
a report we need to go here under create tab and then we can click here under reports and
notice we have this whole section with reports. So we can create using the design option the blank
report start from scratch or we can use the report wizard. If you're just starting with the databases
I would suggest that you start with a report wizard as that is going to be the simplest way for
you to learn how to do this. So we go here under report wizard and then you'd basically pick the
first table that you want to pick the fields from. So in our case we go to the customers
table and let's say we want the first name and we want the last name and let's say we want
the street address the city zip state and email. So these are just the some of the fields from that
table. Now in our first instance here we are going to simply create a report just from one table so
that we get the idea and then we are going to pull the data from multiple tables. So we go here under
next and then we choose how do we want to group those if we wanted to create groupings of those
customers in our case for now we're just going to leave this alone and then we click on next
and then here it's asking us do we want to sort those customers so we could technically choose
to sort those by first name or by last name or by zip or any of those fields here. So we are going
to just say we want to sort them by first name. Then you can also choose additional sorting
criterias as well subsequent ones typically the first option here is going to take priority
then it's going to go to the second priority and so on. So we click on next and then it's going
to create the report here in the tabular format and then you can also choose whether you want
the orientation to be portrait or landscape. If it's a lot of fields that you're going to have in
your table it's best probably to choose landscape but for now with the number of fields that we have
we just can create it using the portrait view. And then we give it a name here so just the
customer list we can call this whatever we want customer list and then it's going to give us a
preview of this report. We click on finish here and notice the preview has been created. So if I
close it here on the right hand side now notice under reports I have a new customer list report.
If I double click on it notice I can see all those customers here. Now you'd say well it's
kind of cutting here the email field on the right hand side how can I change that how can
I customize that? We can customize any of those reports that we create a couple ways or you can do
this in a couple ways you can either go here under view as soon as we have selected the report here
we can click on view and you can change it to the layout view here. So that's one way to customize
it and this is under the layout view we can go and simply resize the fields so that's one way to
do it so notice I'm just readjusting the width of those fields. Now probably the address needs
to be longer notice I'm going to the right here and such. The state doesn't have to be as wide
then the zip I can adjust this accordingly. Notice you need to adjust the
labels here on the top as well. I'm pressing here ctrl a to select everything and
we can also go here under format and we can change for example the font for this to be a smaller
size so you can control how this will display by utilizing or changing the font visual aspects of
it. That's one way for you to customize this. If you wanted by the way these fields here on the top
of the labels to be in a different format notice I'm holding down the ctrl key here and I'm
selecting all those labels. Now under the format then I can go here under format and then make
this bold. I can choose a different font size and all that type of stuff. Notice also under
the format we had the select all option that I did earlier using the ctrl a key on the keyboard.
If you wanted to change the top or the heading of this you can simply go here and double
click on the heading and then customize this any way that you want. So that's one easy way to
customize this report that we just created here. Notice that there are different designs here that
you can apply as well to this report so notice you have these new tabs here on the very top under and
these are typically referred to as the contextual tools since we are tinkering and working
here with the report module of the database and adjusting the layout and the design of it
notice we have this like design tab arrange tab format tab and even page setup so you can change
the look and the feel of this specific report. Notice also under the design tab you also have
these design themes so if I wanted to apply a different look and feel to this I can apply one
of those themes. Now to go back to the normal view for this report we go here under view and
then report view and notice it has changed it has been modified with the changes that we applied
earlier. Another way to customize your report is also to utilize the design view and I'll
go more in depth about this shortly here. So you go here under design view after we have
selected our report and this is kind of more complex and again notice you have here again the
same contextual tools on the very top however you can change here notice it says page header if I
wanted to move this field a little bit farther to the left and adjust the width and such again these
are the labels notice I can do them more precisely from here. So you can adjust the width of those
fields here and you get the idea at this point. Now these would be just the labels on the top and
these would be the actual fields in the bottom. Notice you can also adjust the footer what will
show up as at the bottom of the report from here as well. So let's assume that I didn't need the
email field you can simply press delete from here and delete both the label and the actual field
the bottom stuff here it's actually the field from the table that it's linking to the table
and then we can close this report here on the top save the changes and now double click on the
report again and notice we do not have that field for the email. Also if you wanted to see a
preview of this you can click here on the top left and choose print preview and this is how
a report will look at this point. Notice you also have the footer here the page
footer. We can close the preview from here and at this point let's also learn how to add
a field to an existing report. So let's assume that this is our report and then somehow we missed
adding the email field to an existing report. Of course we could create something from scratch
however in certain cases your report might be fairly complicated and you want to add another
field or a couple fields. So here's how you add the other fields to it. You can right click on on
the report and choose design view or you can go up here under view under the home tab and choose
design view so you'll kind of want to do this from design view. Now on the right hand side or on
the very top here notice how it says add existing fields so you can click here and add existing
fields so notice we have the list of fields here from our table and we can move this a little
bit and then we want to add here the email field so we simply drag it but we need to drag it
notice in this white spots here where the detail for the form is. We don't need to put it under
page header we want to put it under the detail area this would be the content of our report we
are kind of linking this field with the actual table from the customers table here. So
this is not the label it's actually the data from the table. So we simply drag it and we
just drop it where we want it. Then adjust the positioning of it on that report how you
want it how wide you want it and all that type of stuff and then notice at this stage
it's not giving us the option to put this the label for it because if I run this right now
I save the design notice it's not going to have the label here on the top as to what this is. We
need to basically go under the design view again and in this case the label is right there
but notice it's kind of like if we move this a little bit notice the email label it's
over here to the left. What you can do is you can simply click on that email label the one
that it came in or the one that it brought in delete it reposition your data field here
how you want it and how wide you want and then above it under the header area that's
where you need to create a new label for this. So to add the label on the header
what you do is you go here under the label area here on the design notice there's
this icon label here and then choose where you want that label and how wide you
want it and then type in their email. Hit enter and then reposition it
the way you want it realign it. Same thing with the one here as
well and let's go under view here and choose report view. Notice we have this
email field created. If we wanted to to change the formatting and adjust the formatting again
you can simply go under view go under layout view and then under design apply a different theme
or apply the theme that you had from before. Now notice in certain spots here we have this
or for this we might still need to adjust the formatting notice this has a border alone
around in this field so we might need to adjust the formatting for it and get rid of the border.
To change the border there are a couple ways to do that you can either right click here under
the field and notice I'm under the layout view you can do this also from the design view
and if I go here under properties for this object. So once I go to the property sheet for
this specific field notice here under border it says to use a border style solid and I can change
that so that it's just transparent or nothing and then you can choose the border
effect as well if you need it too. If I go here under and close this now
that border should not appear anymore. Notice it's gone if I wanted to change the size
of the font of course go back to layout view and for this field you can go under the format tab
and then change the size to match the rest of the form and then if we go here under design tab again
and if we go and view this report this is how it will look at this point. Instead of spending all
this time to customize the look and feel of this you can also go back here and use what's called
the format painter so you can copy the formatting of a specific cell and adjust it for other
cells. So if I go back here to my layout view and I'll go and undo some of those things that I
had done earlier so some of those changes notice right now if I go back to view here notice I still
I have my border stuff again here. Go under format painter here from the layout view and I can copy
the formatting of an existing cell and apply it to another one. So I can go to any of these cells
go under format here and then use this icon right here the format painter that copies the formatting
of an object and applies it to another object so click on it and then go to the other object any of
these here and apply it and now it's all changed automatically. So that's the easier way to do
this. Then close it click on save changes and now you can double click on it and this is your report
and this is the print preview of this report. So this is how you create a report this is how
you customize the report and how you remove fields from the report and also how you add additional
fields to the report if you had an existing one. In this video I'll demonstrate how to create a
report from multiple tables in Microsoft access. Reports are one of the most commonly used
features of an access database as a user. Most of the time you're not going to be creating
databases and you're not going to be designing them and you're not going to be creating new
tables in a database even though you can do that. However most of the time you're going to be
creating queries in a database and you're going to be creating reports from the existing
data from that database. Now the reports can come in handy whether you're in a corporate
environment where you have a corporate database or whether you have an access database but the
concept it's going to be pretty much the same. Since the tables are linked typically in
the linking of the tables you can see it here under the database tools and relationships
we have customers customers have contracts and then for each contract there are invoices as well
so it kind of all this stuff is linked together however now in our case we can create a report
from fields from the customers table for example first name last name and then contact information
and also include the contract so we want to see who our customers are what contracts those
customers have. So that's what we're going to learn in this session. So to create
a report you can go here under create and then we want to go under this section here
under the report section and we want to look here under the reports section. My suggestion if you're
starting is to use the report wizard so we click on that and then we are going to pick some fields
here from the customers table so we go for example if you wanted the customer ID you can pick it as
well click on add customer ID first name last name and let's say we want just their telephone number
and the email address. Notice you can pick only certain fields. Then you can also go here under
the drop down before we move to the next step and we can pick from the contracts table from another
table we can pick fields from that table as well because those tables are linked with primary
keys and foreign keys. So we go here under table contracts and now let's say we want the contract
amount and then the contract type so that's all we want in our case then we click on next. Since we
are using multiple tables the system is asking us do we want to group the report results by
customer or by contracts? So in this case if you have a customer that has multiple contacts
you want all those contracts for that customer grouped together so in my case I'm going to choose
customers and it's going to list me the customer and then a sub listing of all that customers
contracts. So that's what this is for. So you have customer up here and then the contracts will be
listed right below it. Then we click here on next and then so what kind of levels do we want here
so we could say we want the customer ID first name last name telephone email and then the contact
information right below it so then we click on next and then how do you want to sort those so we
could sort those records and then you could also choose here the the report summary options. And
here let's say you want to see what the average is or what the sum of all the contracts was and
all that type of stuff for each customer you can just choose here some and the system is going to
give you the total of all their reports for each customer where all of their invoices or contracts
for each customer. So this option is going to be available if you have numbers in their number
values or amounts in there. Then we click on next and then it's asking us do we want to have the
orientation as portrait or landscape and you can tinker with that and check it out but in our case
since we do not have as many fields it's okay to have this portrait. Then we click on next again
and then we have to give a name for this report. So in our case we'll say customers and contracts
or you could say contracts by customer. Now you can change this later as well but it's
important to give meaningful names as you start with the report. Then click on finish and this
is how our report will look like. So this is kind of a preview we need to kind of adjust it
a little bit notice we have these amounts or dollar signs here and all this type of stuff
so we'll need to customize this in a little bit. So for now I'm going to close it here.
To customize this further we go under view and if you wanted a preview of it at this stage
you could do a preview but it's not ready yet no this is not quite ready with all of its stuff
yet. We can go back here close the print preview option go under layout view and we can adjust
the layout of this a little bit. So notice how we have the amounts here we could actually move this
farther to the left so these would be the amounts and then we can resize this field then
we can go and get also the description of what the contract is and then resize that
as well how we want it to look. Then also resize other objects that you'd like here.
Now notice we'll assume here we know what the contracts are and such but notice that they're
the actual labels are here on the right hand side I'm going to delete them for now just so that for
simplicity because we kind of know the amount here and also we know what the contract was.
Now this over here this is supposed to be the total for each contract because we have chosen
the totals so this you might want to move it also right below the amounts area and then notice you
have here summary for customer ID and then three records and such. You could change the wording
here. Notice it has sum you can move that field closer and instead of just sum you could say
total and basically adjust the formatting any way you want. Remember you can also apply themes
for this to customize it so it looks slightly fancier without you having to waste your afternoon
with this and then if this is not very useful in your case you can simply delete that specific
field here and then also remember that you can apply here the formatting so let's say I wanted
the formatting to be something like the previous cells here to remove this border and notice you
can go under format. Take the format painter apply it to another field and notice it's applied.
So that's an easier way to apply the formatting and again you can take the time to customize this
and make it as fancy as you want. Of course you can also adjust the labels here on the very
top you could format that differently as well and let's assume that you wanted these
labels here on the top you want them bold you can again format them any way you
want. Let's say that you wanted also the actual customer information just the customer
data a certain font or color or whatever you could format it however you want and it would
make it more visually pleasing. So this is one way to customize this using the layout view.
So if I close this and save the changes by the way you have to save the changes in Microsoft
access only when you're changing the design of something otherwise it will save the data
automatically into your tables or into your forms. So now if I open this up again this is what
it would look like if I go to the print preview this is what it'll look like again for the print
preview not bad you have the customer 11 or 11 15 here whatever that number is and notice you have
all their contracts and also the total for each contract here and you have also the footer for
this page for this report. Now if you wanted to make this and make more changes in a granule
format and such and go into more detail for tweaking the formatting of this you can also
use under if I close here the print preview you can use the design view. And under the design
view you can adjust here additional components for example the contract amount here notice it's
not fully lined up I could adjust the size of it I could adjust the how much space is between
the amounts and contracts and the total here and format certain things a different way and let's say between each customer I wanted a
little bit more space I could adjust the spacing after each record and so on. Now if I go and save
this and run it again by double clicking on it notice there's a little bit more space
between each customer and the total and the prior customer's information. So that's one
way that you can customize this and tweak this further. As you're working with the reports and
such and we learned about this a little bit in a previous video sometimes you might want to add
a new field to the report so for example we have this report here but for whatever reason we wanted
to also know here what the actual contract ID is for whatever reason is part of this report. So
instead of us recreating the report from scratch we could simply add one more field to this report
to this existing report. So to add the field you could either go here on layout view and then
under this area right here add an existing field you could do it from here and you could choose to
add so right now it's showing us the fields from the customer table however we could choose
all tables and we want it under the contract table and we wanted let's say the contract
number and we want that contract number at the end of the contract information area or
wherever you need it so we could drag it from here actually put it in the very beginning and place
it basically wherever you need it wherever you want it. I'm going to delete the little label for
it on the top for now so that we keep our report slightly cleaner adjust the size of it accordingly
how you want it change also the font for it and that's one way to display it in there using
the layout view. The other way to display this field in there to add this field and this is what
you'll probably use in most business environments is by using the design view. So if I go here to
my report I'll close it for now open up the report again and go under view design view or I could
right click on it and choose design view that's another way to do it. Now at this point notice
these are my existing fields I have the contract amount I have the contract type however let's
assume that right next to contract type I want to add another field. In this case I go here under
add existing fields and I could add the field the same way that I showed a moment ago and drag
contract number over here. I want to delete the label for it unless you have a use for it at this
point in this view and probably don't need the number to be that long and then that field at this
point should have been added. So if I close it and run it I should have the contract number at
the end of each contract for each customer here. So at this point let me explain how this data
is actually linked to the and how you control the linking of each field to the actual table
because the data from the report is actually pulled from the tables it's not really stored in
the report it's actually just at any time you're on the report it's querying it's looking up the
data in the table and it's displaying whatever the criteria is that you have specified. Go here
under design view and if you go to any of these fields or the data fields here let's say
contract type or contract amount and such you can select the field and then just go under
the property sheet. Property sheet it's going to do is it's going to bring up the property details
for this specific field that we just selected and then here it's telling us how it's going
to format it and all that type of stuff and it's the contract amount that we have
selected currently it could we could select any of the fields here we would want to from
this report and we could customize here any of the details how we want this to be formatted
basically so this is just the formatting currently for this item. So we're controlling how
the formatting is going to display for that value so far. However if we go here under the data tab
and the control source is where is this linked to? It's telling us what the source of the
control to what table and what field is it picking so in this case it's actually going to
the contract amount from the contracts table and if you click on the drop down if for some
reason you need to link it to a different field that you had selected initially in
your report you could just pick it from here or if you want to link it to a completely
different field that does not show up over here under the list of fields for your current
report you can click on these three dots and then go under your database that you have
opened and you could link it to a different field within one of your tables. So for example under
contracts here if I wanted to link it to the date or to another one whatever it may be that I wanted
simply select it from here and then click ok and then run the report again and it's
going to link it to a different control source from a different field on that table
that you select. So that's how we can create a report from multiple tables and also group the
results and create a calculated field for the amounts within each order for each customer and
group those orders together. So that's one way to do that. So if you want to further customize
this report and add or change the order of those fields and such you can simply drag those
fields by going here to the design view. So go here under design view and let's say
we wanted to change the order of those fields you can simply shuffle the fields around here. So
this will put the contract number in the beginning you can resize this how you want it the
amount now it'll be in the end also the total we'll put the total on the right hand
side at this point right below the amount. Then you can readjust the spacing between
them and now if we close it save the changes run it again notice we have the number in the
beginning we have the actual contract information and then we have the amount on the right hand
side. If we wanted to further customize it in this case such as formatting and such
we can go here under view layout view and tweak this a little bit more use
a format painter and click on the destination cell it'll apply the formatting
of the previous cell that you have selected. Now if we close it save the changes
run it again now it should have been readjusted the layout of it. If you want to
see a preview of it click on print preview and at this point it will show up like this. Of
course you can customize this stuff and make it as fancy as you would like you can make the reports
very complex. It's very customizable basically. [Music] In this session I'm going to demonstrate
how to create a query with user input and as a result of that
query then we'll create also a report where the user will have
to input data some kind of data and then the
report will be created automatically or generated
automatically. So first we have here a table called contracts and the
contacts table has notice the customer ID,
amount, signing date and such. By the way this works the same way
for multiple tables as well as long as they are
linked together so the concept it's the same whether we are using a table here
or multiple tables. So in our case we'll
want to create a query where the user will actually
have to input let's say the the date, a specific
date. So we'll go here under create,
go under queries, query wizard simple query wizard, okay,
and then we'll go under contracts and we'll put the number
ID, amount, date, and the type. Any of the fields that you want to
include. Click on next next and initially we are not inserting
any criteria yet. We give it a name and then finish. At this point notice
that it ran the query with all the fields that we selected
and the query is listed down here. Now this stage we are going to go and
change the design of this query where the user will be prompted to
enter let's say the date. We are going to change the design.
You can either do it open it up here this query first and then go under the
home tab and then go under view and go into design view or you can right
click and choose design view. Now notice here
these are the fields that we have selected that we are using for our query
and notice under there's an option here for a criteria.
So let's say we want the signing date. We're going to put a criteria in there
where the user is actually going to input
that and the criteria if you're going to use user input you need to put it in
brackets and then close brackets as well. We'll
close it at this point. We could of course
run it but we're going to close it so we save these changes.
And before we run it if we go here under contracts notice that we have some dates
here for example 3/1/2010 or 2/9/2010 or 5/28/2010. So
let's take note of 3/1/2010 and 5/28/2010. Now we're going to run
this query that we created and notice it says
enter the date. This is looking for that parameter so
the user needs to just type 3/1/2010 and then hit ok and notice there are two records with this from that table.
If I go and rerun this again and let's say I do now I want to run it
on 5/28/2010. And notice we have two more records as well.
So that's how we create a query using user input or with user input.
From here you can actually create a report. So reports usually
run from data based on the tables or you can run
reports from queries. So this you're learning actually two concepts: you're
learning how to run a report from a query and also that
query and the report then it is going to ask
for user input. So let's go under create tab to create
the report. Click on the report wizard and then we
are going to select the query here contacts by date.
Pick all the fields that you want. Click on next.
Choose whether you want to do any grouping or any of that type of stuff
for now we're going to leave it the way it is.
Next you can choose to sort this by contract number or ID or however you want.
Next and then click on next again and then give it a name contracts by date
in our case. And you can name this however you want.
Click on finish. Notice it's prompting us for the date so if we say 3/1/2010, it should give us
only those two records that match that criteria.
If we wanted to run this again for another date
we can simply go here under contracts by date user input and let's say 5/28/2010. Click ok
and there is our data. Now you can customize of course this report
any way you wish to the formatting and such so notice we
have these number field here and such you can always customize that and choose
to make that larger. And you can reference the previous video
on how to do the formatting of the reports and customizing other reports.
And probably at this point you are wondering well how can I
specify and how can I have this query so that instead of me putting 3/1/2010 date and giving me just for that specific
date can I have this query customized so I put the start date
and it gives me all and also I put the end date for a particular
range and then have the system list me all those criterias.
So and that is very doable and you all you'd have to do basically go back
here to the design view and we need to tweak and change
this to include the range. So we can
accomplish this by doing the followings: we enter the range,
the starting range here, it has to be in brackets, you can have those words be
whatever you want them to be, you put also the
end range in brackets as well and you use the word and in capital
letters. Now here we are saying we want anything
greater or equal to the start date and
anything less or equal to the end date. Then we save this by clicking on the
save button here. On the top we'll close the query
and then run it again. Now it says enter the start date
and we enter 3/1/2010 of course it has to fall within the
range of data that you have in the system
in your table. We enter also the end date 5/28/2010 and notice the system displays the data that
falls within those parameters. We could run this for any date ranges
so we could say 1/1/2010 I want to see all the contracts from the
January 1st and then all the way for the first 6
months to 6/30/2010. Click ok
and notice these are all the records or all the contracts for that date range.
Now you can do the same thing you can create a report for this where
you can run this report at any point. The thing of the report or the advantage
of the report is that you can make the data
look nicer and such. If we go here under create
and we go under reports and report wizard and we go under the contracts by
date we insert all those fields in there, I just
clicked on all the fields, press next, next, we can order them by contract
number or whatever there by date however you want them.
Let's do them by date, next, next. We give it a meaningful name click on
finish. Notice it's trying to run it at this
point so we say 3/1/2010
to 12/31/2010. Click ok notice we have the data for all
of that date range. Of course you can go
here and change the look and feel of this
customize this report any way that you would prefer. And you can go into the edit option here
under design and then view in the layout view and adjust how this
is displayed. Format this any way you prefer change
the look and feel of it. Close it, saving it, and you can run
this report at any point for any sales or whatever it is and it works
exactly the same way with multiple tables as
well as long as they are linked properly. So
that's how you create a report based on a query
with user input whether it's single user input or multiple user input. It's one of
the powerful features that most users will use in Microsoft
access from day-to-day tasks and business tasks. In this session I'm going to demonstrate
how to export data from access into excel. A lot of times in
the business scenario and such you can run reports
you can run queries or you want to export data from
your database and put it into an excel spreadsheet and
then work with the data and create charts and
or use it for other functions from within excel. So you can do it from
the tables, you can do it from a query. you can
export the data from a query, or you can export the data from
a report. So let's see from first from a table exporting data from a table into
excel. We click here on the contracts table
which looks like this at this point we right click on it and there's
this option for export and you can choose to export it
in any of those formats. You could also go under external data here and choose
to export from up here into excel. So
right click, export, excel, and then choose a location where you
want to save this and how you want the formatting and the
layout if you prefer. Click ok and you could choose to save
those steps. Those steps by the way will show up on
the external data here under the saved saved exports. Click close. Now that data
should be in excel. If I go here to my file manager,
notice I have a file here called contracts.
I open this file and now I can work with this
any way that I prefer from excel and of course they're two
separate entities at this point so if you change something in excel, it's not
going to update it in Microsoft access. So that's how you
export data from a table. The process is exactly the
same for exporting the data from a query
or from a report. So for the query we run the query first so in this case I
have the query here the date parameter and all that type of stuff so
so this is my data and now at this point I can simply right click
choose export, excel, choose where I want to store it.
Give it a name, press save, and finish. And we can do this for
the contracts as well by customer. Same thing with the report this is our report
right click choose export, choose excel format,
choose where you want to store it, notice contacts by customer report. Click ok and now if I go to my folder where I exported this, notice the
data is there and this was the query that I
ran earlier for the dates from January 1
to June 30th from the query. So that's how exporting data works from
Microsoft access to another system such as Microsoft excel. It's a
commonly used feature in business. In this session I'll demonstrate how to
create a mail merge using a query from Microsoft access. In
order to create a mail merge we can either use a table in Microsoft
access, an existing table, or you could use an existing query.
So in our case we are going to first create the query
and then we are going to create the mail merge.
That way you learn both at the same time. So we go here under create, go under
queries here, query wizard, and then simple query
okay and then we're gonna go under customers and let's say we want to use a
customer ID, customer first name, last name
and then let's say the email and also let's go under the contracts table
and we'll get the contract amount and then
the contract type as well. So we are just creating a query at this point with
those fields from two tables. We click on next, click on next. We give
also a name to this call it and then click on finish.
So the data is here at this point. Now to create the mail merge we can do this two
ways: we can either create the mail merge directly from Microsoft access or
we can go to word first and create the mail merge from there
or the other option would be to dump this data into Microsoft excel and then
do the mail merge from Microsoft excel. So we'll basically do it from access
from this point and then I'll show you from word as well.
So if we go here to the this is our query
and then we go under external data notice you also can right click
so we have our query here and then we go to external data
and the other option here is to actually create
a word merge. So word merge it says you specify the table and you create a mail
merge wizard through Microsoft word. We click on Microsoft word merge here, it
says do you want to link it to an existing
document or do you want to create a new one? We'll just create a new one because
we don't have an existing letter currently. Click ok.
Now the system is going to open Microsoft word automatically
and here at this stage it's asking us as to what we want to do. Do we want to
create letters, email messages, or envelopes and
such. Once we are in Microsoft word here we
type our letter we can say "dear" and then insert field, we put first name comma
and we basically just type our letter and then every so after in our letter we
can actually include here various fields from our
access table. Basically type the letter here
with incorporating the fields that we have in our
access database. So here we could use their first name, last name, we could use
their contract amount stated somewhere and then a contract type.
If we needed more fields then we need to go and customize
our query from the access database. So for now I'm just going to put the
amount and the type here. So we say "thank you for partnering with
us to meet your needs with the recent contract" and the computer will
put the amount in there. You could put the contact type. Of course it has to make sense. And then you basically finish typing
your letter. Then we go here under the next stage
here or start your document and then we can use
the current document in our case or we could have created a use an
existing document if you have a letter from before.
Then we go select recipients, we'll use an existing list and then
we'll select the existing list here which is basically a customer query for
mail merge so we don't really need to select the list it's already
selected because we started the mail merge from Microsoft access so that's
how it's linked automatically there. Now here
we write our letter which we actually just did earlier and then it
says here previewer letters notice "dear Owen"
and then contract one and then this was the contract.
And then the next step here is to complete the mail merge.
So we click on complete the merge and then edit individual letters.
Then click ok here. Now here we can see all the various
letters from that mail merge from that specific
query. Now keep in mind this you don't need to
modify if there isn't something that needs to be corrected and such, you need
to actually go back to the database and change the information in the
database and then rerun the query. The beauty of this stuff is, and I'm
going to close this mail merge the results at this point so
I'll say I don't need to save the results, but
the beauty of this is that is that if I save
this form and this is the form actually that typically has the codes and all
that stuff linked to the database, if I save this, and then I close word, and then I come
back here to my document, so this was my letter
and two weeks have passed or something time has passed now I want to rerun this
again. Notice if I open it up again it will ask
me do I want to update it for link to the access
query, I say yes and now I can go ahead here
and go under mailings, go under mail merge,
and then step by step and pretty much here I don't actually have to do them
step by step, I could go here under finish and merge
and then edit individual letters and it's going to
actually rerun the whole query again. I know I did this fairly quickly so you
can rewind the video and see it again for your benefit. But the advantage is
that you don't have to always create a new
form, you can simply save the form from word open it up
whenever you want to rerun it and it's going to
pull the data directly from that query, from the access database automatically
for you. So this was the way how to do a query
from Microsoft access into word. The other way to do it is that you
can open word first and then go to Microsoft access
and pull the data from a query or from a table and that will work as well.
So if I go here to Microsoft word and I open a new document here and
then I go under mailings, I go under start a mail merge, and I'd recommend
that you use the mail merge wizard here. Then start next and
we're going to use a current document here
or you know just this current blank document that we have in front of us.
Next here we're going to select the recipients. Now notice it doesn't
link automatically to our table into the database so we have to actually go in
and link to it. So if I click here on browse,
now I need to find my database where I have it,
and double click on it, and now notice it lists
all the tables and the queries so you can actually do a mail merge from
actual tables or from the queries within a database. Now I click ok here.
Notice it shows all the records here including the fields that we had
selected as part of that query. We could filter and sort them if we
wanted to do that further more but for now we're not. Then
we're going to write the letter and we say "dear"
and then insert field and we say first name, space
last name, and then write the letter with the various fields. So I put the amount. So basically you're just putting the
actual fields that you want to use. Of course this I'm doing this fairly
quickly here for the sake of time. You could put other additional comments and then you can preview those letters
so this is how it's going to look thank you
"dear Owen thank you for your contract type" and such and such
residential number one and and so on and so on. Next you click on here and
complete the merge then you choose edit individual letters.
This just gives you an idea as to what they are going to look like
so right now we have five letters that have been generated automatically.
So that's how that works. Now the other thing that you can do
is from access or or from word you could do
also email merges. So if I go here to my customers know this
customers table has an email field so I could actually do an email merge
for those customers and I could either create a query that includes those email
fields or I could use my customer table for
this purpose. So if I wanted to send them contract information and all that stuff
which is in a different table then you'll need to use a query
and you need to include the email field in one of those queries.
For the sake of time I'm not going to create a query for that at this stage
but I'm just going to use the table customers which has already a field with
email in it and will create an email merge. So
as I have opened here the customers table and I click on
mail merge or word merge. So here's customers,
word merge, and then it's asking us do we want to link
to an existing word document or create one? We want to create a new one.
So basically you're going to write the email in word
and then it's going to use Microsoft outlook
to actually deliver the mail so it's important here as well,
the third piece of this puzzle is actually to use
Microsoft outlook and have Microsoft outlook configured to be able to receive
and send emails as well. So now notice Microsoft word has
been opened here in the bottom and then we say okay I want to create
email messages here, then I say next here to start the
document. Then it says the only use the current
document or do you want to use an existing document so we'll use the
current document which is right here. Then select the recipients the
recipients it's already linked to the customers table in
access so we don't have to really select the customers at this point or type a
new one, we'll use the existing list. Then we'll
write the email message and here we just write "dear" and then put their first name, last name,
comma, and you're basically just picking the
fields and being creative or whatever you need is to utilize those fields in
your email to them. In this case we're
going to tell them okay here's what we have on file with you
or from you on our systems and if you have any problems or anything like that
just let us know. So save the name first and last name then we could put in
there street address... and you basically format this a little
bit nicer if you'd like. You can go here under the home tab and
customize this any way you want and so next you click on preview your
email message. Then you complete the merge next and
then we're going to choose here electronic
mail. So this is the difference between the word email merge
and a regular mail merge for letters. Here for the email merge you have to
click on electronic mail and the other field here
the other important thing is that you have to tell the system
which field actually contains the email
address. So in this drop down it has to be the
field that corresponds with the email address from your access
table or access query. For example here is
an access this is email it's called email the label on the top here.
And then you type the subject line here so this would be just like as if you
were to type an individual email to somebody that would be the subject that
you want to write to them and then the email format html. You want
to use html particularly if you want to embed
pictures and things of that nature as part of
your document here. But what we talked over here it's going to
be the body of the message that is going to be sent through
email. Now again it's important if I press
ok here it's actually going to open up Microsoft outlook
and it's actually going to try to send all of those emails
that are in the table in the access database so it's going to try to email
here 42 customers all in one click basically. So I'm not
going to press ok here because it's not going to try to
send those but that's how this system will work for
sending email messages. It's a powerful feature.
Again you can actually go then back through Microsoft word and do the same
thing like we did for the regular letters and create an
email merge. You can do it either from Microsoft access,
from the table itself and initiate the email merge,
or you can do this from word, configure the
go through the steps of the mail merge and then
pull the table, pull the fields, write the email, and press send from word
and then in the sent messages in Microsoft outlook you'll see
each individual email that was sent out. So that's how you do mail merge
and an email merge and of course you can also do labels if you needed to
from here as well through Microsoft access. [Music] In this session I'm going to demonstrate how to create
a switchboard or a dashboard for your database
so that whenever you open your database you'd actually instead of having a view
like this and then trying to figure out as to what
is where, you'd have more of a nicer view like a dashboard menu type of
system where you'd be able to get to certain
tables or certain queries or reports much easier and more
user-friendly. So how do we create that? Unfortunately
in access 2016 Microsoft hid that way. So the easiest way to get
to the switchboard manager is by simply going here under
the tell me feature which is this little box right here telling Microsoft
access what you want to do. So we go here on the tell me feature and
we type switchboard and then click on switchboard manager
and it says the switchboard manager was unable to find the valid switchboard for
this database would you like to create one
and that is great because that's what we want to do so you click on
yes and then it's going to give us a default switchboard. So once it opens
the default switchboard here we click on edit
and then we want to add new items to this switchboard.
Then we click on new and then here is one of the options or one of the menus
that we can define. So let's say we wanted to update
customer records and here we go under the command then we
need to tell the system as to what we want to do. In our case we want to
open a form either in add mode or edit mode. So in our case we want to
update existing records. So we want to choose edit mode
and then we go to the form that we want to be editing.
So we go under here and we choose the customer form and then click ok.
Another option so we click here on new and we choose add new customer and then go under the switchboard. Click
on the form for open form in add mode
and then we go over here under customers form
as well. So now we have two of them. Then we add another command that we want
as part of our dashboard. Click on new and then let's say we want
to see the contracts by date. Do you want to open a report,
so you can either open forms or reports in this case or macros.
So here we'll click on open a report and we'll change this let's say contracts
by date and then you click on open report and
then you choose a report that you want to run.
Click ok and you can keep on adding more items like this as well.
So we could go here and choose, then you can also choose a new item here,
return, because you want to navigate back home.
You choose go to the switchboard and the switchboard that you want to go
is the default switchboard. So I close the main switchboard. Now I
can go and create additional switchboard pages as well. So
in this case I want a switchboard just called reports. Click ok and now I go into the report
switchboard I created new to add a new item
then I add it then I edit the report the switchboard
and I add a new item there. So let's say I want in there
view customers and then open a report, pick the report
that you want and so and then keep on adding whatever
components you have because sometimes in a database you'd have
hundreds of reports and hundreds of queries and such and in this stuff here
on the left it could get very messy so the
switchboard actually helps you categorize
what you want in the order that you want them presented.
Then whenever you create another switchboard it's also important to
create here return to the main switchboard option as well.
So return home and then you want to go to the switchboard and you want to
choose which switchboard you want to go to.
Click ok and then close this stuff and then close it again. Now whenever you define the switchboard
you also have to do one more thing in order to tell Microsoft access in the
database so whenever it opens which form to open. So in this case we
need to go to do that we need to go under the file
menu we go here under options then we go
under the current database and then over here where it says display,
right here, we want to choose to display whenever you open the database
you want to choose to display the switchboard.
Then you click ok and then it says we need to close the database for this to
take effect so we'll need to exit the database
and now notice it presents me with the main switchboard here.
Of course we could change the colors we could make this fancier and all that
type of stuff as well. Typically you can hide all this
other stuff here on the left and make it so it opens like this. So
basically if you wanted to add a new customer, you could click on
add new customer and now notice it takes you to your customer
to add the new customer. If I wanted to view the contracts by date range
so notice it's going to prompt me for the contracts and it's going to play me the report
content that I had requested. I could review also the
customer report if I needed to and there it is with a single click. So
that's how you create the switchboard items here. If you wanted to change the
colors and all that type of stuff you can simply go here under view
and then under either design view or the layout view,
you can change the theme to something more appealing to you. And you could add more controls and you can add more text in here as well. When you're ready and done you could put
a logo if you prefer and all that type of stuff,
but basically this is a mechanism for your staff and yourself to be able to
access this and in a user-friendly way. If you wanted that switchboard manager
tool all the time you can either use the quick the tell me feature over
here or you could actually go ahead and add it over here under the
quick access toolbar. You can go and choose more commands
and then choose to show all the commands and then scroll down here to switchboard
somewhere switchboard manager, and then you add it
to the right. Then click ok
and it's going to show up here on the quick access toolbar
on the very top so that will display you your main switchboard
where you can make edits and change stuff if you need it to do that.
[Music] In this tutorial I'm going to
demonstrate how to add a new field to your database or to one of the tables
in your database. Suppose that you designed your database
and now all of a sudden you have a need to add an additional field
for your customer table or some of the tables there.
So what you can do is go ahead and go to the specific table
and the easiest would be to go to design view here
and then add a new field wherever you want that new field.
So for example first name, last name and then
telephone number and such but let's say I want to also post in there a
picture for this customer. Then under the data type what you want
to choose here is attachment
and basically you're saying we're going to add a field called picture and this
is going to be an attachment. Now the point, the reason why I'm
choosing attachment is because I have not covered the attachment option
earlier and this is new in in access. So once you define this then
click on close here for the table and save the
changes we are saving the changes because we
change the design of the table. If we open the customers table, notice
one of the fields will be attachment, like it has this attachment
option right here. Now in order to add the attachments you
can of course double click here and add and choose to
add attachments and such the picture and then choose okay that's
one way to do it. Or the other way to do this would be if
we created here a new form. If we go here under form and under customers we add all these
fields next next finish, we should have in here a field for the picture. So let's say for customer Owen here we
want to add his picture. Notice we click on it and notice here it
says manage attachments. You can click on it click on add and
then go and find the picture. Click ok and there is the picture.
So that's how you add attachments to an existing table
by adding the field first and then by creating
the form next. You can also customize an existing form
with an attachment field by editing and using the design
view and the layout view. If you made it this far in the tutorial
thank you for doing so. I hope that this tutorial was
beneficial to you and that you can go ahead and check
out also the other tutorials that I have on this channel.
[Music]