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

Relation (Table)

Table 2
Example of Table

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. 

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