Search
Left Quote    I have not failed. I've just found 10,000 ways that won't work.
- Thomas Alva Edison    
Right Quote
 
[login] | [Register]
 

Databases and Access

by: bs0d
Page: 1 of 6
(View All)

Introduction


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.


TERMINOLOGY:


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:


  • Typing the first letter of the data type, (then Access will fill in the data type for you).
  • Or by clicking on the down arrow on the right-hand side, and selecting the data type from the drop-down menu.

Here are the 10 different data types:

  1. Text (default)
  2. Number
  3. Date/Time
  4. Memo
  5. Yes/No
  6. Currency
  7. AutoNumber
  8. Hyperlink
  9. Lookup Wizard
  10. OLE Object

In case if you don't know exactly what some of the data types are, they are all described below...

TEXT

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.


MEMO

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.


NUMBER

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.


  • Byte
  • Integer
  • Long Integer (default)
  • Single
  • Double

DATE/TIME

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.


  • General Date: 2/14/02 10:21 AM
  • Long Date: Sunday, March 10, 2002
  • Medium Date: 10-Feb-02
  • Short Date: 04/03/02
  • Long Time: 6:37:03 PM
  • Medium Time: 6:37 PM
  • Short Time: 18:37 CURRENCY

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.


AUTONUMBER

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.


YES/NO

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.


OLE OBJECT

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.


HYPERLINK

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.


LOOKUP WIZARD

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 PROPERTIES:



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.


FORMAT

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.


INPUT MASK

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.


CAPTION

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

  1. Click on the yellow tab next the field to highlight the field
  2. Click on EDIT
  3. and then DELETE
  4. and YES to confirm the delete.

INSERTING A FIELD

  1. Click on the yellow tab next to the field to highlight the field where you want to insert a new field
  2. Click on INSERT
  3. and then ROW

A new field will be added at that location; existing fields will move down.

MOVING A FIELD TO ANOTHER LOCATION


  1. Click on the yellow tab of the field you want to move,
  2. Position the mouse pointer so the arrow is pointing up and to the left,
  3. Press down and drag the field to the desired location and release.

CHANGING THE COLUMN WIDTH IN DATASHEET VIEW


There are two ways you can change the column width in datasheet view:

  1. Go up to the field headings and position the mouse pointer on the line to the right of the field that you want to change. A double-arrow should appear. From there, click the left mouse button and drag either to the left or right. Dragging to the left will make the column narrower. Dragging to the right will make the column wider.
  2. Double-click on the line to the right of the column. This will set the width to the longest item including the field heading.

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:

  1. Enter, view, and modify data
  2. Automate your tasks
  3. Provide instructions
  4. Print information

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
  • Page Header
  • Detail
  • Page Footer
  • Form Footer

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.

CALCULATIONS:

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...


  • All fields from tables must be enclosed in brackets
  • Each field must have its own set of brackets
  • All calculations must begin with an equals sign
  • Field name must be spelled exactly as it appears in the structure

FUNCTIONS:


SUM - calculates the sum of all of the values in the records for that particular field.
=sum([price])

DATE - displays the current date
=Date()

NOW - displays the current date and time
=Now()

PAGE - displays the page numbers. The page function will automatically number all of the pages for you.
=[Page]

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

  1. Report Header
  2. Page Header
  3. Detail
  4. Page Footer
  5. Report Footer

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.

LABELS

Click on the Aa object in the tool box and drag it over to the grid.

TEXT BOX

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.


RECTANGLE

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.


LINE

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.

RELATIONSHIPS:


FORMATTING A CALCULATED FIELD

  1. Click on the PROPERTIES icon on the tool bar.
  2. Click on FORMAT tab.

RELATIONSHIP - a common field between two files.

TYPES OF RELATIONSHIPS...

  • ONE TO ONE -one group of data can be related to one and only one other group of data. When creating a one-to-one relationship, you are going from the key field of one table to the key-field of another table. An example of a one-to-one relationship would be student to student-id.
  • ONE TO MANY - one group of data can be related to one or more other groups of data but the reverse is not true. When creating a one-to-many relationship, you are going from the key field of one table to a corresponding field in the second table where the field in the second table is not a primary key. An example of a one-to-many relationship is a mother to her children.
  • MANY TO MANY -one group of data can be related to one or more other groups of data and vice versa. When creating a many-to-many relationship, it takes two one-to-many relationships put together. An example of a many-to-many relationship would be uncles to nephews.
  • REFERENTIAL INTEGRITY -A system of rules in Access that ensures that relationships between records in related tables are valid and that you don

    1  |  2  |  3  |  4  |  5  |  6  |  
    Next »


    No Comments for this page.

    You Must be logged in or a member to comment.


Tutorial Stats

Tutorial Stats

17,435 Views
0 Total Comments
3 Rating of 5 (1 Votes)

Options

Tutorial Options

· Login to Rate This Article
· Login to Post a Comment
· Read more by this author
Digg This Article! Del.icio.us: Bookmark This Article Reddit: Bookmark This Article BlinkList: Blink This Article! YahooMyWeb BlogMarks: Add This Mark! Furl: Save This Article Spurl: Mark This Article

Articles

Related    

· Getting Started with Excel
Your Article Here



"AllSyntax.com" Copyright © 2002-2018; All rights lefted, all lefts righted.
Privacy Policy  |  Internet Rank