Hello again and welcome back to our course on Access 2013.
In this section we’re going to take a bit of a lightening tour of this contact list.
We’ve referred to several features of it so far in the course but it is in effect a
working database application.
It’s a desktop database and I’d like to go through basically what it can do and explain
some of the terminology we’re going to use later on in the course.
After this section and from then onwards we’re going to be going into quite a bit of detail.
So this is a chance to get a bit of a broad overview of what a desktop database looks
like in Access 2013.
Now the first thing I want to do is to just hide the Navigation Pane for the moment and
I’m also going to minimize the Ribbon and we’re going to look at this contact list
because what this database is about is a contact list.
Now at the moment, we are looking at this contact list in what’s called Form View
and if you look down at the status bar, the left hand end, it says Form View.
If I right click on the Contact List Tab, I have an alternative of going into what’s
called Layout View or going into what’s called Design View.
Now Design View basically determines how the contact list is designed and I can use the
information about contacts in different ways.
I can lay it out in different ways and I’ll be coming back to the design later on.
But when it’s in Form View, the contact list is shown on the screen in such a way
that I could actually add data.
So if I wanted to add a contact all, I would need to do is to click in the first free field
there and I’m going to just put in the first name of John, last name Doe, and I can go
through and put in information about a person by clicking from field to field.
My first person is John Doe.
Now once I start typing into that form another line below, it opens up.
It says New and if I wanted to put a second person in, I could start typing the second
person’s details on the next line.
But let’s go back to John’s details.
I’ve got John Doe.
Company, I’ve got a drop down.
Oh, that’s empty.
So I’ll say he works for Acme Inc.
Job title, drop down; it’s empty as well.
I’ll say he’s a director.
Category, it says Personal.
Let’s say he’s actually a business contact.
Put in his email address.
Put in his business phone number.
If I’ve got a home phone number I can put it in there.
If I’ve got a mobile or cell phone number I can put it in there.
Zip postal code.
I’ll leave those for now.
So what I’ve done is to enter some of the details about one person.
Now while I’ve been doing that you may have noticed this little pen on the left here and
what this pen tells me when it’s highlighted like this is that this particular record is
in progress.
I’m typing in the details but I haven’t actually finished entering the details yet
and there are various ways that I might finish entering the details.
One of the ways that I can finish entering the details is just by clicking on the next
row.
If I click on the next row, what happens is the little pen disappears and I now have a
person in my database.
The person I’ve got is John Doe.
Now notice at the bottom of the workspace, there is a scroll bar down there.
I’ve been using it to scroll left and right through the pieces of information about John
Doe.
If I go right back to the left again, I can see first name John, last name Doe, and so
on.
So what I’ve done is to add a record to my contact management database.
Now one of the very helpful things about this particular database is that apart from that
way of adding data on the contact list form, there is an entry here that says New contact.
If I click on New contact, watch what happens.
What I get a form, a dialog form, a Contact Details dialog.
In this I can add the same sort of information that I added just now but I have a dialog
to do it in.
So if I wanted to somebody else, like for instance Jane Doe.
Company, well, oh look there’s Acme.
Now Jane doesn’t actually work for Acme but as if by magic Acme, the company I added
just now for John has appeared on this drop down list.
Now Jane is not an employee of Acme.
What about job title?
Well, look directors popped up there as well.
That wasn’t there just now.
Now I could go through now and put in the other kinds of detail for Jane.
So let’s give Jane a home phone number.
Let’s assume she’s a personal contact and let’s go up there and check the list;
yeah personal, business, family.
Let’s say she’s a personal contact.
That’s fine.
Let’s put Jane’s and then there are other pieces of information that I could add about
Jane within this same dialog.
But let’s suppose that that’s enough for now and let me click on Save and Close.
Watch what happens.
If I now look back at my contact list form, Jane has been added there.
So although I didn’t add Jane using this form, you can see her on the form.
So that’s two different ways of adding a contact to my contact management database.
Now let me just open up the Navigation Pane again and go back to the section Forms.
Now you’ll see that there are actually three forms there and we’ve seen two of them in
action.
Although they both can be used to add contacts, they’re very different forms.
The contact list form, the one that you can see there, has a view whereby you can see
the contacts as rows in a table or what’s normally referred to as a datasheet in Access.
And then the contact details form which is a dialog type form.
Let me just go into Design View for that again.
You see everything laid out as a single dialog that you can use primarily to add a new contact
to the database.
So there are two forms.
They look very different.
They have some functions in common and they have some functions that are different.
Now there is a third form there called Getting Started and if I just open the Getting Started
up, you’ll see that that is more to do with Help explaining how to get started with contacts.
So welcome to the contacts management database.
This is not primarily about adding a contact or looking at existing contacts.
It’s really about giving you some help.
And actually in this particular form, it’s got links through to some videos explaining
how to use the database.
So there are forms that are not only used for maintaining data but they can be used
for information or for giving help.
Now again that’s the sort of form in an Access database where we would design it and
we would add the functionality to it, including things like these links here, these hyperlinks
that take you through to other parts of the system.
So they’re the forms in this database.
Let’s go up to, have a quick look at tables.
So let’s close the contact list and under Tables, the first table is called Contacts.
Now generally speaking, to open something in Access 2013, you can either right click
and there’s almost always an open at the top or you can just double click and it’ll
open.
Now when we have a table open we’re actually looking at data.
In this database there really is only one table of data.
There’s another table over there called Settings but I’ll talk about settings in
a little while.
This is at the moment where all of the data in this contact management database is stored.
It’s effectively like a huge Excel spreadsheet.
It has a number of rows, horizontal rows, and each row represents one person, one contact.
Now for each row or each record there are a number of columns and each column is one
field.
What we sometimes call an attribute.
So for Jane Doe who’s the second row in our table, the company attribute is blank,
the last name attribute is Doe, the first name attribute is Jane, and so on.
Or we might say that for John the value in the job title field is director.
And that’s how we refer to the individual cells in this spreadsheet, in this open table.
Now when we come to design and build databases, one of the most fundamental tasks is to actually
decide what data we’re going to keep and then to decide which table or tables to put
the data into.
It would be an extremely simple database that only had one table and many business level
databases will have probably dozens of tables.
We’re going to be dealing with examples in this course with two, three, four tables
maybe, not too many more than that.
But once you’ve got the principles, extending those to a larger number of tables is not
particularly difficult, although it can be quite time consuming and it does involve quite
a lot of attention to detail.
Now this table, the contacts table, we’ve got open but now I want to go into Design
View.
So if I right click on the Contacts Tab there and go into Design View, we’ll get a completely
different view of this table.
The first thing to bear in mind is that we don’t see any data at all in this view.
This is the view where we’re looking at the design of the contacts table and we have
each field in the table defined and for each field we’ve got a sort of grid at the bottom
with information about that field.
Now this is what we’re going to look at in detail in a couple of sections time.
We’re going to look at table design in a lot of detail, but let’s just take one example
here to get the general principles.
Let’s take the example of the last field there, category.
Now the category field has a data type of short text, and being short text it means
it’s a field with some text in it.
It’ll just be regular words.
In the table at the bottom, it tells us that the field size is 100 which means that the
most text you can have in there is 100 characters and then there are a load of properties related
to that.
For instance, if I go down there, is this field required?
No it isn’t.
If I then click on the second tab down at the bottom here, Lookup, it says you’re
going to look up the possible values here from a list and the list is personal, business,
and family.
That’s why when we were putting that data in earlier on, you could see that list with
personal, business, and family.
Now that’s a pretty lightening quick look at one table and in fact one field within
one table, but hopefully that gives you some idea of what’s involved in designing a table
of data.
Now one thing you may have noticed is that as I’m now in Design View of the contacts
table I’ve got a contextual tab at the top.
I’ve got the Design Tab in the Table Tools.
If I finish looking at the contacts table in Design View just click on the Close box
there.
Bear in mind I’ve made no changes.
If I want to open that table again but this time to look at the data, don’t forget I
can just double click on it, I get Table Tools again but this time I get a Fields Tab and
a Table Tab.
So again you can see how depending on what we’re doing, what we’re looking at, we
will get different Ribbon tabs including contextual tabs as well.
Now there are one or two things about this database that I’m not going to look at,
at the moment.
I’m not for instance going to look in detail at queries.
We’re going to come back to queries later on.
In fact this particular database is one we’re going to use for a good few examples to get
us started on various topics later on.
But I would like to look at reports next because sometimes what you want from a database is
a report, a sort of snapshot of information from the database.
If I take this particular report here, it’s called Directory.
As with most of the objects that we deal with in Access databases, if I want to design it
I’ll right click and go into Design View, but if I just want to look at a report, I
can just double click on it to open it.
Let’s open this directory report and what you see is a full alphabetical contact list.
This shows the list of the people in the database in some kind of alphabetic sequence and with
a selection of the information about each person shown.
And as with a form, as with a table, there are mechanisms within Access to design reports.
I could have a report showing all the people in the database, just the people of a certain
age, just the men, just the women, just the people who work for a particular company,
and I can choose an extract of the data and that incidentally is one of the things that
we use queries for and I could produce a report containing the information I want in the sequence
I want and perhaps date it for the day that I want as well.
Now there’s a second report there which is called Phone Book.
Let me just double click on phone book.
Phone book is the same people are in it but the information is presented in a different
sequence.
Phone book, business is one list; personal is another.
If I added more people to the contacts table and ran either or both of those reports again,
of course they’re always showing me the latest information with the way that they’re
designed at the moment.
Now particularly if you’re new to databases or at least new to Access I hope that quick
overview of some of the main features of an Access database has been useful to you.
The general principles in there are ones that are going to be very important from now on.
Before we start going into the details of how to design and create a database though
I’d like to look at web apps because this is the fundamental new feature in Access 2013
and although the principles behind the databases that you build in web app form is exactly
the same as the ones that you’ve just seen, the finished product and to a large extent
how it works has some differences and they’re not all subtle differences either.
So in the next section we’re going to have a look at web apps.
I’ll see you then.