Home

Access is a Relational Database Management System -DBMS

In American Standard Code for Information Interchange 8 binary digits = a byte. A byte = A-Z or a-z or 0-9 or American English punctuation.  In data base language a # of bytes centered around a content area such as First Name, Age, Part No., Social Security Number, etc. is called a field.  When a field is given a name this is also called a Attribute.
 A number of fields centered on a general theme such as Payroll, Medical, Demographic, Grades, etc. is called record, also called a tuple. 
A number of records with the same number of fields, in the same order and the same data types  (NOT) is called a data table. A table (relation) is a two-dimensional structure made up of rows (tuple) and columns (attribute).
A number of data tables that have a common field that can be joined together so that data can be extracted from multiple tables based on the relationships of the tables is called a relation data base management system.  The common field must be of the same data type.

In summary:

  1. A number of bytes = a field
  2. A number of fields = a record
  3. A number of records with the same number of fields, in the same order and the same data type (NOT)  = table.
  4. A number of tables with a common field allowing the joining of the tables and extraction of data from multiple tables = a relational database management system (DBMS).

In access there are seven main modules for building relational data bases and system tools for managing the database.     

1. Table - this module is used for creating the ten field types used in a table. Data is stored in the table.

Table 1
Access Field Types

Field Types
Auto Number A number that is assigned automatically and never changes again.
Currency Dollar amounts
Date/Time Dates and times
Hyper Link Hyper Link address HTTP, FTP, E-mail, .doc, .exl.
Lookup Values that come from another table, a query, or a list of values you supply.
Memo Can contain numbers or letters up to 64,000 including spaces
Number Byte , Integer, Long Integer, Single precision, Double precision. 
OLE - object Picture, sound, or video
Text Can contain numbers or characters up to 255 bytes including spaces.
Yes/NO Can contain Boolean values (yes/no, true/false, on/off) Can contain only one of two values
Attachement Better for video, photos, music than ole.

Relation (Table)

Table 2
Example of Table

SSN StudentId FirstName Lastname
544444444 101 Harry Smith
111111111 102 Jane Thompson
222222222 103 Pete Jones
333333333 104 Karen Hovde

Each column has a unique name called the attribute name (SSN, StudentId, Firstname, and Lastname) which allows us to access a specific column with out specifying the columns position in the record.  Each column has a domain (set of values) from which its data is drawn.  The domain of the SSN column is all legitimate social security numbers. The domain of Studentid is all legitimate assigned student id numbers.  The domain of Firstname is all first names of students.  The domain of Lastname is all last names of students. 

Each row shows the characteristics of an enity. An enity is a person, place, object, event, or idea for which you want to store and process data. The enity in Table 2 is student.

2. Forms:  Forms are used to create a graphic user interface (GUI) to enter data directly into the tables.  When ever you are designing input forms and are reading the data from a source document make sure that the input form is exactly like the source document in appearance and movement from field to field.  This will reduce data input error. 

3. Queries: Queries are used to extract data from the data tables.  If tables have relationships created with joins on common fields then data can be extracted from multiple tables. There are basically two types of joins. 

Global joins which are loaded into memory when the database is activated. One to Many, One to One, and Many to Many are global joins.

Local joins are created in a query and are only in effect when the query is run.  Right outer, Left outer and Inner joins are  local joins. 

Queries are used to extract data from a single table or multiple tables.  When a query is run a dynamic data set is created that is independent of the table(s) underlying the query and only exist when the query is run. 

  For example:

Every time someone looks up a phone number in the local Solano County phone book the first thing that must be done is to determine what city the individual lives by going through the phone book.  Since the phone book is first indexed on City in A to Z sort order (Ascending) we can quickly find the city.  City = Fairfield.  The next index is the last name and is also in ascending sort order  within in the city.  Since last name is also index in ascending order we can quickly find the last name within the city.  Last name = Smith. Since there are 132 Smiths in Fairfield, the third used.  First name = John.  Since there are 15 John Smiths the next index Middle Initial must be read.  Middle initial = L.  Five Smith, John L. were found so now the next index is address. We find only one John L. Smith at address = 2040 Thompson Court. At this point because:
City = Fairfield  and
Last Name = Smith and
First Name = John  and
Middle Initial = L  and
Address = 2040 Thompson Court

are all true, we are able to select the phone number 555-555-5555.
City contains LastName;  Lastname contains Firstname;  Firstname contains Middle Initial; and Middle Initial contains address. This is called a select query were we deicide what criteria must be met to select data from a table (phone book).  Queries, that in some way change the data in a table are called action queries. Append, Update, Delete and Make Table are the four action queries in Access.  All queries when saved in access are saved in Structured Query Language.(SQL)

4. Reports - Reports are used to format the output of your tables and queries. Reports have a number of sections.

    Page Header - appears at the top of each page and is often used to place the labels for the fields in the detail section.

    Page Footer - appears at the bottom of each page of the report and is used for placing the page number. 

    Report Header - appears once at the beginning of the report  and is used for report title, logo, date, time, etc.

    Report Footer - appears once at the end of the report and is used to summarize the data for the entire report by creating calculated fields with the text box from the toolbar. 

    Detail - this is used to place the most often displayed data in the report.  Often the fields are placed in the detail section.  Calculated fields can also be created here but only apply to the current record.  

    Group Header - this is an optional section which is used to differentiate between groups of data. Ten group headers can be created.  For example:  If you were organizing data by galaxy, star system, planet, continent, nation state, state, county, city, street, and persons name you would create ten group headers for this.

    Group Footer - this is an optional section which is used to summarize the data for each group using the text box from the toolbox to create calculated fields. In the above example for group header we could determine the number of humans in the galaxy, star system, planet, continent, nation state, state, country, city, street, and last name. 

Report Layout  one group report
Report Header once at beginning of report
Page Header at top of each page
Group Header up to ten
Detail primary data goes here
Group Footer calculations on data for group
Report Footer once at end of report calculations on data for entire report
Page Footer at the bottom of each page

 You will notice that the report footer appears before the page footer.  This is always the case for the last page of the report.

5. Pages -This module was discontinue with Access 2007 - This module is used to create static, dynamic, and interactive web pages on a local area network.  Not on an internet web server. 

    Static web pages reflect the content of the database when the static web page was created. Static web pages are actually created outside of the Pages module.

    Dynamic web pages reflect the content of the database as it is in real time. You see the current status of the data base in that the dynamic web page is update each time the database is updated.

    Interactive web pages (Data Access Page) allows the user to  edit current records, add records, and display data in various ways from a local domain web server.     

6. Macros - In this module often repeated functions such as the steps to open a file, generate a query, run a report, etc. can be automated in the function based macro language. The macros can then be attached to command buttons or event procedures to further automate work. All macros are written in the macro editor. 

7. Modules - This module is used to write visual basic for applications functions and sub procedures that will be attached to command buttons or event procedures.  The visual basic in access only compiles within access.  A separate object file is not created outside of Access.

System Tools: the system tools that we will be working with.  Replication, Synchronization, Splitting, Encryption, Decryption, Passwords, Switchboard Manager (all switchboards are saved as forms), start up options, and database documentation..

The Switchboard is the system that allows us to turn our data base into a  series of menus with command buttons that have macro code or VBA code attached to them.  Always only have one entry and one exit point from the program through the main menu.  All subordinate menus are to go back to the main menu. When you as a developer turn a access program over to the user, the user will only see the switchboards (menus).

Advantages of Database Processing

1. Getting more information from the same amount of data.- by putting the data in one database the information is available to many users.
2. Sharing data- several users can have access to the same data and use it in a variety of ways.
3. Balancing conflicting requirements- a person or group should be in charge of the database- by keeping the overall lneeds of the organization in mine the database can be structure to benifit the the entire organization.
4. Controlling redundancy- eliminating redundancy not only save space but also makes the process of updating data much simpler.
5. Facilitating consistency-because the database reduces redundancy there is less possibility of inconsistency.
6. Improving integrity-an integrity constraint  is a rule that data must follow in the database. A database has integrity when the data in it satisfies al established integrity constraints.
7. Expanding security- the prevention of unauthorized access to the database. database allows the assignment of groups and passwords.
8. Increasing productivity- frees the programmers who are writing database access programs from having to engage in jmundane data maniputaliion activities such as entering data.
9. Providing data independence- is the property that lets you change the structure of a databnase without requiring you to change the programs that access the database; such as forms, queries, macro, modules, reports.

Disadvanteges of Database processing

1. Larger file size- all the data that the database manages is stored in one file which requires large disk space and memory
2. Increased complexity - The complexity and breadth of the functions fprovided by a DBMS make it a complex product.
3. Greater impact of faliure- if several users are sharing the same dtabase, a faliure on the praert of any one user that damages the database in some way might affect all the other users.
4. More difficult recovery.- a database inherently is more complex tht a simple file, the task of recovery is complex and must go back to the state before the errors.