Welcome back to our course on Access 2016.
We’ve been setting up the fields in the trip table in the Esprit de Tour database.
And in this final section on setting up the fields we’re going to add the remaining
ones which are all numeric.
The first of these is going to be the duration of the trip in days.
So first of all I’m going to call this field DurationDays.
The default data type as always is Short Text but on this occasion this is going to be numeric.
So I’m going to change that to Number.
We now have a bit of a quandary because depending on how much you know about the way that numbers
are stored in computers in general, in Microsoft Office or even in Access you may or may not
be able to choose a number field size.
Now if I click down at the first property in Field Properties there’s a little dropdown
there.
It says, Byte or integer or long integer or single.
You may have no idea what each of those are and you may have no idea which of those will
be most suitable for storing the duration of a trip in days.
Now the information you need is embedded in the Access Help.
But there isn’t one simple place to find it which fully explains I think everything
that you need to know.
So I’m going to take you to two places in Access Help that will be of assistance to
you in this.
So first of all let’s open up Access Help and let’s go to Design Tables and one of
the options there is Data Types.
And this explains in pretty good terms all of the data types in Access.
So for instance short text holds alphanumeric data, names, titles, etcetera up to 255 characters.
We’ve used that one quite a bit already.
Long text, large amounts of alphanumeric data, sentences and paragraphs.
And as I said in the previous section up to about a gigabyte of data but controls to display
a long text are limited to the first 64,000 characters.
So all of the data types that are supported are in this list.
It’s very helpful.
But when you get to numeric data it rather unhelpfully says, Numeric data 1, 2, 4, 8,
or 16 bytes.
Now even if you know how many bytes you need to store the duration of a trip in days whether
you can translate that to the names in that dropdown list I just showed you is another
question.
So in order to work out the name of the numeric type that you need you need to go to another
place in the Access Help.
And in order to find it the way I always remember it is that I search on conversion.
And the first item that you find there is Type Conversion Functions.
Now believe me you don’t need to know anything about type conversion functions but if you
go down into that topic it explains those different numbers and what range each of them
can store.
So it tells you that a byte can store from zero to 255.
If we used byte to store the duration in days of a trip our longest trip could be 255 days.
Now that’s not bad actually.
That’s what, about eight or nine months?
Something like that.
However we may ultimately have longer trips than that so maybe we should go for the next
size up if you like.
And the next size up is integer.
An integer can store numbers in the range minus-32,768 to plus-32,767.
Now a 32,767 day trip is a long trip.
You’re getting on towards a hundred years.
I doubt whether many people will be going on trips that long.
So integer looks like the type to go for.
While we’re here you might want to take a look at what long is.
Now that’s a big number.
Or what double is.
Now that’s a really, really big number.
But we’re going to go for integer so let’s close the Help, let’s go back to our properties
and let’s select as our type here Integer.
Now so far I’ve been ignoring some of the properties for these fields.
And the time has come to stop ignoring a couple of them.
You saw just now that we can store a number from minus-32,768 to plus-32,767 in this type
of field.
I want to make sure that when people are storing the duration of a trip they always store a
positive duration.
Now a day trip would just have a duration of one day.
So it only needs to be one or more but you can’t have a trip duration of zero and you
certainly can’t have a trip duration that’s negative.
So I’m going to introduce here a validation rule and some validation text.
My validation rule is going to be greater than zero and my validation text is going
to be duration must be greater than zero.
Is the duration required?
Yes.
Is it indexed?
Yes.
We’ll very often be looking at trips or choosing trips by duration.
So we need to index that.
And of course duplicates are okay in duration.
We’ll have many trips with the same duration.
So let’s now go back up and enter a description for the field and let’s try it out on the
Louisiana Explorer.
So in order to test out my validation I’m going to put in a duration of say minus-3
days.
Let me just click away from that record.
There we are.
Duration must be greater than zero.
Okay.
Let’s click back in there and change that from minus-3 to plus-8.
Okay that’s fine.
So our validation is working fine.
I’d probably want to check it with zero as well but I can assure you that’s fine.
We’re going to turn our attention finally in the trip table to the price range.
And notice that these are the prices excluding flights.
Now although price range is something that you very often see expressed if you were say
looking for a holiday, a vacation.
In terms of the functionality of the database and enabling users to search a price range
it’s better to store the price range as two separate values.
A minimum price and a maximum price.
And it’s also important to note that this is the price range in general terms for the
trip.
When somebody comes to book onto a specific tour, so that’s an instance of the trip
starting on a particular day, for example, there will be a specific set of prices for
that tour.
Normally an adult price, possibly a child price when children are allowed.
Maybe special offers like family prices and so on.
So the price range here really is just a range of prices and the tours will have their own
specific values.
So we’re going to store a minimum price and a maximum price.
So we start with PriceMinimum, data type.
Now on this occasion it will be currency.
And by default Access 2016 will use my local currency which is currently set to U.S. dollar.
Increasingly travel companies in particular are dealing to a worldwide market.
And people accessing the Esprit de Tour database as potential customers may or may not see
these prices advertised in U.S. dollars.
We may well build into the database in the later stages of development facilities to
deal with currency exchange, currency conversions so that although we will store our price ranges
and our individual prices in U.S. dollars a customer in another country such as the
UK might see these prices in pounds sterling and maybe in other countries, in India for
instance, may see the prices in rupees and so on.
So what we’re building here is if you like a basic core set of prices in U.S. dollars
but we may well use other currencies when we’re dealing with customers in other countries.
So let’s put in a description here and let’s put in a validation rule and some validation
text.
Is a minimum price required?
Yes.
Is it indexed?
Yes.
There will be lots of searching on prices I’m sure.
Now let me do the same for PriceMaximum.
Okay let’s go in and put in a price range for the Louisiana Explorer trip.
And there you are.
I’ve also checked that my validation rules are working okay and they are.
Note that when I define this field as being of type currency I automatically get correct
currency punctuation.
I didn’t have to type the dollar sign there or the commas or the dot-zero-zero for the
cents.
That’s all done for me automatically by Access.
So at this stage in development I’ve now finished the definition of the trip table.
I’m going to need to load some data.
I’ll be coming to that a little bit later on.
But that’s it on the trip table.
It’s time now for you to do Exercise 01 and that’s covered in the next section.
I’ll see you then.