Hello, welcome to this video! Let's see in a short time how to use
Microsoft Access from Office 2019!
Leave us suggestions below this video to improve our next video tutorials
dedicated to Microsoft Office!
Microsoft Access is a professional Database Management System software,
used to gather data, collect and save databases, and create formatted
reports to export outside. Due to its powerful features, Access is often
used in a business environment, such as accounting, finance and human
resources.
In this video we are going to see and explain all the basic tools inside
Microsoft Access, in order to start for the first time.
When opening Access, a start-up page opens, divided in two parts. On the
left, you can find and open an existing Access file (or database); on
the right you can start with a new document by taking a ready template
from the list. In order to see how to use Access, let's just start from
a Blank database template, defining its File Name and where to save it.
The new Access database opens on the workspace, showing the Ribbon on
top, collecting all the tools available inside the software; the
Navigation Pane on the left, with all the objects inside the current
database; and the main preview, where you open and edit each of these
objects. These are divided in Tables, Forms, Queries, Reports and Macros.
We are going to see each of these during this video.
The Tables are used to collect and store your data, in order to create
your own database. These are made by columns and rows: the columns are
called Fields, and define the kind of data that each of these must save;
the rows are called Records, and represent the single sample of data,
such as a single person or measurement.
By default, your Access document has a single table, called Table1, which
is completely empty and shown on the Tab above the preview.
First of all, fix the kind of data to save by adding each Field. Click
on Click to Add to select the data format, and then type a name for the
Field.
There are several formats available: choose Short or Long Text to insert
text, Number to insert numerical values, Currency to add any currency,
Date to import any date from the calendar, Yes/No to add a clickable (or
boolean) square, Attachment to insert any file by double-clicking on its
symbol, and Hyperlink to add any web address link. To edit the data
format of the selected Field, just go to Data Type under the Fields tab.
Once Fields are defined, you can start inserting data by clicking on each
cell. As you type inside new rows, new Records are created automatically,
increasing the number under the ID Field. This is also called Primary
Key, and is used to identify each Record singularly.
As Fields and Records are filled with data, the database is created.
To gather data you can also import an external file, or use math or logic
expressions inside Calculated Fields.
To import an external database, just go to the External Data tab, and
then to New Data Source. You can import from any File (including Excel),
any Database (such as Access or an SQL Server), or other sources online.
On the dialog box, browse for the file on top, and follow the guided
procedure.
Inside your Table you can also add Calculated Fields. These take data
from other Fields to perform expressions and collect the result.
On the data format list, choose Calculated Field and select the kind of
data to work with. At this point the Expression Builder opens, with
everything you need to perform the operation. To insert arguments, type
directly with your keyboard, or choose the navigator at the bottom to
add any data from your Access document. From Expression Elements, choose
the object interested (such as Table1); from Expression Categories the
Field to be used as argument above. To insert operations, select
Functions or Operators under Expression Elements, and then choose the
function under Expression Values. Make sure to respect the correct format
suggested at the bottom, and always use Fields data which is compatible
with the operation. To get additional help about how to use the
Expression Builder, just click on the Help button.
Once the expression is defined, the Calculated Field shows its result,
which gets updated in case its arguments change.
Let's see how to manage and edit the Table database.
Use the Text Formatting section, under the Home tab, to edit the text
format, size, style and color. Use the Fields tab to add new Fields with
a selected data format, delete the selected Field, or use Default Value
to fix a default value with the Expression Builder. Use the Table tab
to make the table interactive by adding scripts and Macros. We are not
going to see these in this tutorial.
To rename and size a Field, or modify its expression, just right-click on
its name above.
In the bottom right corner, you can select the view to use. By default
you use the Datasheet View, but you can switch to the Design View to
manage all the properties for each Field in detail, such as Data Type,
Format and more.
To save and close the table, just right-click on its tab above. You can
double-click on it from the Navigation Pane to reopen it, and rename or
delete it by right-clicking on it from the list.
To create another Table database, just go to Create and then to Table
or Table Design. The new Table will show up on the workspace under a new
tab and added inside the list on the Navigation Pane.
The Form objects are pages that are sent to people or directly online.
This is compiled in order to collect new data and extend your Table
database with new Records.
To create a new Form, open the Table interested, and go to Form under
the Create tab. This Form gets the same name and content from the Table:
each Record is a page inside the Form, showing all the compiled Fields
imported from the Table. You can check all the Records by using the
navigation buttons at the bottom. The last Record page is blank and is
used to save new Records inside the Form and its Table.
Also in case of the Form you have several views available.
Use Layout View and its Form Layout Tools to adjust the Form layout and
its basic structure. By default, the Form is made by several cells (or
elements) contained inside two columns and as many rows as the Table
Fields are. You can use the options under the Arrange tab to add/remove
rows or columns or split/merge any selected cell.
Under the Design tab, enable the Select tool to move an element by
clicking and dragging it, or size it by selecting it and dragging its
edges. You can also right-click on an element to manage or delete it.
Go to Properties to open the Property Sheet panel on the right and edit
its properties in detail.
Use Themes to apply a ready template on the Form appearance.
Inside Controls, you can add other extra elements besides the Fields
imported from the Table, such as Text Boxes, Images, Buttons, Pages and
Lists. These can become interactive when Macros and expressions are added
through the Property Sheet options. We are not going to see these in this
video.
Use the Format tab to adjust the content appearance, such as text
properties, distribution, color and cell outline and fill. Consider that
all these modifications affect all the elements selected from the Form.
You can make multiple selections by clicking on the elements by holding
down the CTRL key.
To preview the form, use the Form View. This shows the form as it is when
it is compiled or shared. You can use the last Record to interact with
the Form.
Use the Design View to adjust the Form layout in terms of page size and
distribution. The Form is divided in page Header, Detail and Footer, and
can be edited on the preview or through the Form Design Tools as seen on
the Layout View.
The Query is another important object used to retrieve data and create
custom databases starting from the Tables you already have saved.
To create a Query, click on Query Design under the Create tab. This opens
on the workspace asking for the Tables to take data from. Click on one to
import a Table and hold down the CTRL key to add multiple ones. These
Tables show up as small boxes on top, complete with their name and their
Fields inside, including the Primary Key. You can add other Tables by
going to Show Table under the Design tab above.
At the bottom, choose from Field the data and the Tables to consider. You
can select a single Field, represented with its Table name, a dot and
then the Field name, or select a whole Table, with its name, a dot and
an asterisk. Below, choose the Criteria to use when collecting or
excluding data on the Query. This is specified through one or more
expressions in series, inserted by right-clicking on any Criteria cell
and then going to Build... to open the Expression Builder.
Also the Query has different views. The default Design View sets the
content and the criteria to realize the Query through a very easy
interface. You can switch to the SQL View to build your Query by using an SQL
code instead.
Use the Datasheet View to preview the Query table that resulted from the
setup. This gets updated in case any data inside the source Tables is
modified.
The Report is an Access object used to extract an immediate and complete
representation of a chosen database to preview or print it with adjusted
format and style.
To create a report, open a Table or a Query on the Datasheet View, and
then go to Report under the Create tab. By default, the Report is shown
through its Layout View, where you can edit and manage it just like the
Forms. You can move and size each cell and use the Design, the Arrange
and the Format tabs on top to add extra cells and interactive elements,
adjust the overall layout and set the text and the cells appearance. In
addition, you can use the Page Setup tab to adjust the page size, margins
and orientation, especially in case you want to print the Report.
Besides the Layout View, you can use the Design View to adjust the Report
appearance and the content of each cell, the Print Preview to take a look
at a preview of the printed Report, and the Report View to have a clean
preview on the Report appearance.
To make Forms and Reports interactive, you may need other Access objects
called Macros. These are script codes that run according to the content,
conditions, functions and rules you define inside them.
To start writing a Macro, just go to Create and then to Macro. The
procedure to realize a Macro is fully guided, but you can convert it or
switch to a complete Visual Basic interface if you are an expert.
To save your whole Access database or any of its objects inside, go to
File and then to Save As. Choose Save Database As to save your whole
Access document with all its Tables, Forms, Queries, Reports and Macros,
setting its file name and where to save it.
Choose Save Object As to save the Access object shown on the main preview.
In addition, you can go to the External Data tab, and then to Export to
export the current Access object to Excel, Text files, PDF, XPS
documents, emails, SharePoint list and other Office programs, such as
Word or Access.
Check out our video description to learn more about how to publish an
Access database!
Thanks for watching this video! Discover and subscribe to our channel
to learn more about other great programs inside Office 2019!