This tutorial is to help you understand databases and Microsoft Access. This might help you with various programs or programming languages in the future. This tutorial will focus on Microsoft Access and the terminology and database types and such related to that program.
Before I get started, here is some terminology you might hear through out the tutorial, and need to be aware of when using Microsoft Access. So here it is for you to become more familiar with.
DATABASE - a collection of data and relationships between the data stored in external memory.
FLAT FILE - a stand-alone file that has no relationships with other files.
RELATIONSHIP - a common field between two files.
DBMS (Database Management System) - the software that allows you to create and build databases.
CONTROLS - graphical representations of text, data and images placed on your form.
BOUND CONTROL - a control that is tied to a field from a table. A text box is an example of a bound control.
UNBOUND CONTROL - a control that is not bound to a field in a table. A label is an example of an unbound control.
CALCULATED CONTROL - a control that involves a calculation. This calculation can use formulas involving fields from a table or can be a built-in function using a field from a table.
DATA - characters, numbers, facts and figures that are fed into a program to help it do it's processing.
FIELD -a group of related characters.
RECORD -a group of related fields.
FILE -a group of related records.
TABLE - another name for a file in a relational database.
STRUCTURE - specifies the layout of the file, the field names, the data type for each field and the length of each field.
KEY FIELD - a field within the table that uniquely identifies each record within the table.
DATA TYPE -refers to the type of data stored in each field, whether it is text, a number, a date etc.
Which brings us on to our next topic... Continue on to learn more about data types within Access.
Data Types in Access
Access has ten different data types used for entering data. Text is the default data type. You can enter the data type in a couple of different ways, by either:
Here are the 10 different data types:
In case if you don't know exactly what some of the data types are, they are all described below...
Use this data type to enter text,symbols and numbers that do not require any calculations (such as phone numbers and zip codes). The default length of the text data type is 50 positions, but it can be up to 255 characters in length.
Use the memo data type for lengthy text or combinations of text, numbers or symbols. Memo fields can be up to 65, 536 characters long. The Memo data type is used for long messages that you might not know exactly how long they will be.
The Number data type is used for entering numbers (duh). This can include integers as well as decimals. This field is used for numeric data that represents a count of items, or numeric data used in mathematical calculations.
The number data type can be further broken down, by specifying the field size for the number. The default is LONG INTEGER. Below are the five different field sizes for numbers.
Use the Date/Time data type if you are going to be working with dates and/or times. Just like the number data type, you can further break the Date/time down by specifying a specific type of date or time. Below are the specific Date/time formats and examples of the formats.
The Currency data type shold be used when you are going to be working with money data fields. The currency data type will insert a dollar sign ($) just to the left of the number.
The AutoNumber data will automatically number the records when you are adding records to a table. It will start with 1 and increase by one each time a new record is added. The AutoNumber data type is good to use for a key field because it ensures that each record will be unique.
*If you delete a record (even accidentally) or you back up to the previous record when entering records, Access will not renumber the records. In the case that you back up, it simply skips that number.
The Yes/No data type is used when fields are either going to have a value or Yes or No. The Yes/No data type is most commonly used for flagging accounts paid or not paid, or checking orders to see if they have been filled or not or determining whether to send a Christmas card to a person on your Christmas list or not.
When the Yes/No data type is used, a box will appear in the field when entering records. Checking the box indicates Yes and leaving it blank indicates No.
The OLE Object data type contains a pointer to an object. This object can be a Word Document, an Excel Spreadsheet or a graphic image embedded in an Access table.
The Hyperlink data type is used to store e-mail addresses or addresses or websites. When the record is entered, the e-mail address or web address will be underlined in blue. When you click on it, Outlook or Outlook Express will be loaded to indicate you want to e-mail that person or in the case or a web address, your browser will transfer you to that website.
The Lookup Wizard is used to choose a value for a field from another table or choose a value from a list of values. This prevents you from having to re-type a lot of information by selecting a value from a table where the data is previously entered.
FIELD SIZE :
This indicates the length of the field. If the field is a TEXT field, you can set the field size. For the other data types, the field size is set for you. For Text fields, the default length is 50 positions. You can have a maximum of 255 characters in a text field.
This indicates how you want the information to appear when it is displayed or printed. The Format property affects only how the data is displayed, not how it is stored in the table.
The input mask defines a standard pattern for determining how data is entered into a field. The input mask allows you to set up formats for Social Security Numbers, Telephone Numbers and Zip Codes. The input mask can be applied to fields with data types of text, number, data/type and currency.
The caption is the name that appears in the field headings at the top of the table. The default is the field name as you typed it into the structure. The caption only refers to what is displayed in the field heading at the top of the table in datasheet view, and does not change the name of the field as it is stored internally in the structure of a table.
SWITCHING BETWEEN OBJECT VIEWS
To switch between Datasheet View and Design View, click on the View Button. In order for this to work, you actually have to either be in the records (Datasheet View) and in the design (Design View).
DELETING A FIELD
INSERTING A FIELD
A new field will be added at that location; existing fields will move down.
MOVING A FIELD TO ANOTHER LOCATION
CHANGING THE COLUMN WIDTH IN DATASHEET VIEW
There are two ways you can change the column width in datasheet view:
WHAT IS A FORM?
Well, a form is an object that displays only a single record at a time. It allows you to focus in on the contents of a single record, rather than several records.
Forms are used to accomplish the following tasks:
One might ask, "How can I create forms?" Well, here is their answer. You can create forms from scratch, use the Form Wizard or use AutoForm. AutoForm is a tool that allows you to create a form automatically by clicking on an icon in the toolbar (icon with lightning bolt) or by clicking on INSERT and then FORM and selecting AutoForm.
FIVE SECTIONS OF THE FORM
Their are five different sections of the form in Microsoft Access. Each differen section will be briefly described below.
FORM HEADER -The information displayed here will only appear at the top of the first page.
PAGE HEADER - The information displayed here will appear at the top of each page.
DETAIL - This is where all of your fields will be placed. This is the body of your form and the information placed here will appear on every page.
PAGE FOOTER - The information displayed here will appear at the bottom of each page.
FORM FOOTER - The information displayed here will only appear at the bottom of the last page.
When doing a calculation, you always begin with an equals sign (just like Excel). If you use a field from the table, you must enclose it in brackets. Each field must be enclosed in its own bracket. Numbers do not have to be enclosed in brackets. The field name must be spelled exactly how the field is specified in the structure. An example of a calculation is shown below.
TIPS ON CALCULATIONS...
SUM - calculates the sum of all of the values in the records for that particular field.
DATE - displays the current date
NOW - displays the current date and time
PAGE - displays the page numbers. The page function will automatically number all of the pages for you.
WHAT IS A REPORT?
A report is an object that displays detailed information in a printed format. Normally reports are used in situations where you have large amounts of data that would be difficult to view on the screen. The information is easier to read if it printed out on paper.
FIVE SECTIONS OF THE REPORT
REPORT HEADER - information will only appear at the top of the first page.
REPORT HEADER - information displayed here will appear at the top of each page.
DETAIL - This is where all of your fields will be placed. This is the body of your report and the information placed here will appear on every page.
PAGE FOOTER - information displayed here will appear at the bottom of each page.
REPORT FOOTER - information will only appear at the bottom of the last page.
FONT SIZE - how large or small the print is.
FORE COLOR - the color of the text on a control.
BACK COLOR - the color of the text in the background behind the control.
BORDER COLOR - the color of the text for the border of a control (This normally applies to labels).
HANDLE BARS - the little squares that appear on a selected control which allow you to change the size of the control.
MODIFYING A REPORT:
This is a list of controls used in a report.
Click on the Aa object in the tool box and drag it over to the grid.
Click on the Ab| object in the tool box and drag it over to the grid. To get from Design View to Print Preview or from Print Preview to Design View, click on the View button.
Click and drag the rectangle object to your grid. Your can change the size of your rectangle by clicking on the handle bars. To change the color or thickness of the border of your rectangle, click on the properties icon.
Click and drag the line object to your grid. You can change the length of your line by clicking on the handle bars. To change the color or thickness of the line, click on the properties icon.
FORMATTING A CALCULATED FIELD
RELATIONSHIP - a common field between two files.
TYPES OF RELATIONSHIPS...