Creating a table in Design View:

First, I am creating a new table where I have created three columns or fields. (Note the table is still not named or saved).

I have started to create a table. There are three text fields of varying lengths. The StuIdno has been established as the Key which means that under Indexed it now says Yes (No Duplicates). Note the comments after each field appear at the bottom of the screen when the view is shown for data entry. That means that you can give instructions to the person keying in the data about format etc. On name, I want the data entered as Last, First and I can give this instruction in this area which is called the Description column.

When naming the column or field, it is better to give one word names. SQL does not naturally support spaces and so adaptations must be made if the name is multiple words. You should also avoid words that could have meaning to Access, SQL, Visual Basic etc. For that reason it is better to call the field StuName as opposed to calling in Name.

The Data Types that are available in Access are:

Data Type

Type of data used for

Size and limitations

Text

This is what is frequently called a character field that can hold all alphanumeric characters. If you do not have a specific reason for using another type, this is the default.

This field can hold up to 255 characters.

Memo

Alphanumeric data that can be used for things like comments, therefore this type can include whole sentences and paragraphs.

Maximum of 64,000 characters.

Number

Numeric data - this is used when calculations and numeric formatting are desired.

The size depends on the type chosen. There is room for integers, decimal numbers etc. The range is 1 to 16 bytes.

Byte - 1 byte holds from 0 - 255

Integer - 2 bytes holding -32,768 - +32,768

Long Integer - 4 bytes holding through +2,147,483,648

Single - Floating Point +3.4 x 1038 - not precise due to rounding after 7 significant digits (see notes in Access)

Double - Floating Point + 1.797 x 10308 - precision concerns after 15 significant digits

Decimal - 16 byte integer - assumed decimal precision from +1028

Replication ID - see notes on replication manager

Date/Time

This includes storage of date and time data in a variety of formats.

8 bytes

Currency

This is a variation on numeric that stores monetary data with 4 decimal places.

8 bytes

AutoNumber

This generates a unique number that can be used as in place of indexing a field.

4 bytes

Yes/No

Boolean data is stored here in a true/false or yes/no type of format.

1 byte

OLE Object

This field can hold pictures, graphics, ActiveX objects etc.

1 gigabyte max.

Hyperlink

This can store an address to link to data stored on the Internet. For example the URL.

2048 character max.

Once you have established a data type and picked a size, you can then use the other options to format your data, to validate data etc.

In this example, I want to use an Input Mask on the StuIdno which is actually the social security number. First I have StuIdno selected. Then I select Input Mask and click on the three dots which indicates that there is material available. I have decided to use the standard social security number mask, so I click on it. I want test the mask in the Try It by typing in 9 digits and seeing how they will display in the mask. When you click on next you will be asked if you want to change the format character which is set to be the hyphen. You then get asked if you want to store the masked characters with the data. I replied no. I want to store the 9 digits but have the mask show them with the appropriate hyphens.

The input mask that is created, is: 000-00-0000;;_

When you enter data in the field, you will initially see the hyphens. In this example I have typed 1 as the first digit: 1__-__-____ (Note the mask appears when you start to enter the data).

The explanation of the mask is that there are three parts separated by the semi-colon. The first part contains the actual mask. The second part determines whether or not the literal that is being displayed should be stored with the data. If this character is 1 0 then the literal is stored, if it is a 1 or left blank, only the data is stored. The third part shows the character that will be displayed when you do data entry. My understanding is that the underscore indicates you have not made a selection and is therefore the default. Once you understand the concepts of creating the mask, the wizard is only needed if you find it more efficient.

If you want to use an * for example, type the * here.

The mask will appear as 000\-00\-0000;;* and when you go to enter data into the field you will see: ***-**-****

If you want spaces to appear, enter " " instead of the hyphen or *.

The mask would be entered as: 000\-00\-0000;;" "

The results when I keyed in a 1 are: 2 - -

If I want to change the separate character, I can insert it after the \.

For example, 000\^00\^000;;_ would show the ^ symbol between the parts of the social security number.

Text fields do not come with built in format options, but they can be created. With the state code I decided to put in a format that requires text characters (characters or space) and displays the ? if no entry is made.

The format code was: @;?.

This means the format for the characters is @ which allows characters or spaces and the ? is what will be displayed if zero length or null values are entered. If I enter just one character, I can also enter a space. If I also want to convert to uppercase (incase any letters are entered in lower case) I can do this by using the greater than symbol. To convert to lower case I can use the less than symbol and the & allows non text characters. I am now going to change the state code to force upper case and display XX if no state is entered.

The entry under Format is: >;"XX"

The results work, if I enter ri it will be converted to RI and if there is no entry XX will appear.

Now I am adding an entry under default value. I have entered MA as the default value - note that Access changes this to "MA". Now I am saying the default is MA, convert any entry to upper case and if the area is blanked out show XX.

In formatting a numeric field you can choose to represent the data differently when it is positive, negative, zero and null. For more detailed information on this see the handout for Access97 on Tables and Data in Access. Briefly this format will display the information in different formats depending on what is entered in the field.

$#,###.00[Blue];$#,###.00[Red];"Zero"[Green];"Null"[Cyan]

ON GPA I decided to use a single number. I want all of the digits to display so I made it fixed. I selected 3 decimal places

When working with the date I chose the medium date which will have the format 12-Jan-00. I can enter the date as 01/12/00 and it will be converted.

Check the other handout on format and input mask in Access97 for more information.

Caption: Caption will go in the column header when provided. If not provided the field name goes into the column header.

Validation:

On GPA, I put a validation rule that said it had to be > 0 and less than or equal to 4. This was written as >0 And <=4.

When I validated the number of credits, I wanted >=3 And <=120. First I entered it this way and then I changed it to BETWEEN 3 AND 120.

On Major, I entered the validation rule of "CI" Or "BU" Or "OF" Or "GS" Or "ND". If I try to enter another major, it will be rejected and an error will appear.

The comparison operators that can be used in validation rules are:

<, <=, >, >=. =. <>, IN, BETWEEN, LIKE

You can also use wildcards in designing validation rules. ? means a single character, # means a single digit and * means any number of leading, trailing or embedded characters that can be anything.

If you also include validation text, the error message that you choose will be displayed. For Major, I entered a validation text that now appears in the error box in place of the generic message.

One more kind of validation uses the IIf test which allows you to check on a field in a slightly different way. In the first example, I did the test on the GrantsFinAid field. If it was > 2000 then I noted false meaning that it should be rejected. Otherwise it should just put the data back in the field. We will be looking at these kinds of statements in more detail in later discussions. For now I just wanted to introduce them.

Indexing:

An index makes searches and sorting more rapid. When creating an index, you must specify whether duplicates are allowed. Usually you want to allow them in fields such as name, address etc. because there can be multiple. When creating primary keys, clearly duplicates are not allowed. I established an index for the major field where I specified under Indexed that duplicates were allowed.

Lookup Column:

First I am going to do the lookup using the Lookup Wizard. To do this, I first select Lookup Wizard under Data Type for the column named city.

When the wizard starts it first asks me where it wants to get the data. I tell it I will type in the values that I want.

The next screen then asks me to type in the values which I do. I am only entering information for 1 column.

 

When asked what label I would like on my column, I accept the default of City. The Wizard is now complete.

Notice that I now have a pull down selection for the cities that I want to enter. If the city does not appear on the list, I can type it in instead of making the selection.

In the next city - City (note I changed the previous city to City1 when I started this new city field) - I am going to select the option I want the lookup column to look up the values in a table or query. The next screen will then ask me to tell what table. I have created a table called city that has just one column that contains a city for each record.

The next screen asks me to select the fields/columns from the table.

The next screen shows the cities and gives me a chance to adjust the look. I left it alone. It then asked me for a title etc. and I chose AnotherCity. The result of this is the SELECT statement shown under City in the description of the table.

Now lets look at the differences in the code that was generated.

The first time I did the Lookup for City and typed in the values, the wizard generated the following code:

The second time I did the Lookup for City and used the city table that I had added to the database, the wizard generated the following code.

The differences are in the Row Source Type which on the first attempt was List Values and on the second was Table/Query. On Row Source, the list that I entered in the first attempt was duplicated and on the second attempt a SQL statement to the city field from the city table.