Stephen Martin from the Ag Electronic Bulletin Board office at MU discussed basic topics associated with Access Databases. He explained the terms and some common functions associated with creating databases.

Access - Nuts and Bolts Topics

The MDB File - container for all of the objects that make up a MS Access database

Tables, Queries, Forms, Reports, Macros, Modules, ...

Tables

Field Types - what general type of data are we storing (text, number, date/time, etc.)

Data Types - defines how data is stored, viewed, and used by the database system

Key Fields - unique identifiers for records

Primary Key Fields - unique identifier for a single record in a specific table

Foreign Key Fields - primary key of one table use in another table to establish a relationship

Relationships - define how data in one table is related to the data in other table/s

Referential Integrity - ensures that parent records exist (no orphan records)

Cascade Updates & Deletes - helps automate the tasks necessary to update/delete records that are part of a parent child relationship when referential integrity is being enforced

Normalizing - Table Analyzer Wizard

Relationship Types


One to One, One to Many, Many to One, Many to Many

Finding, filtering, and sorting records - turn data into useful information

Validation Rules - use to ensure that data entered into tables meet your rules/requirements

Input Masks - data entry templates that provide for consistent data entry

Lookups - provide a list of values that are valid for a field - can limit entry to these values if desired

Import/Export - text files, tables, spreadsheets

Copying and Pasting Tables - create quick backups of your data before making scary changes

Linking to Data - link to external data like text files, spreadsheets, tables from other databases

Queries

Query by Example - user friendly way to define how you want to view your data

Query Types

Select - general query, also used as the basis of many action queries

Crosstab - summarized data in a row column format like a spreadsheet

Parameter - prompt the user for the desired criteria or criterion

Action

Make-Table - creates a new table from query result set

Update - changes values in existing fields based on criteria

Delete - deletes records from table based on criteria

Append - adds new records to existing table from query result set

Forms - create the user interface to your tables and queries

Form Types - general categories

Transaction Processing / Data Entry - enter/edit records

Decision Support - view as information, graphs, charts, multimedia

Reports - present data as information in various formats

Report Types - general categories

Single Column

Multicolumn

Tabular - formatted version of table or query datasheet view

Groups / Totals - group and summarize your data ( most useful )

Macros - automate many repetitive and/or complex tasks

Switchboard Manager - add-in to create a menu driven user interface using macros

Microsoft plans to eliminate macros from future versions of Access in favor of VBA

Macros are much easier than VBA

Modules - user programmable VBA code modules

VBA - Visual Basic, Application Edition or Visual Basic for Applications

Application programming language used for everything from simple program automation to building complex decision/logic code segments. Not as easy as macros.

Database Documenter - create a road map to your database (data dictionary)

Structured Query Language - SQL (pronounced "seequel")

Compacting/Repairing - sounds scary but is necessary to keep a happy healthy database

Field & Data Types - most of them

Text	Up to 255 characters	1 byte per character
Memo	Up to 64,000 characters	1 byte per character
Byte	Numbers from 0 to 255	1 byte
Integer	Numbers from -32768 to 32767	2 bytes
Long Integer (default)	Numbers from -2,147,483,648 to 2,147,483,647	4 bytes
Single	Numbers from -3.402823E38 to 3.402823E38	4 bytes
Double	Numbers from -1.7976913486231E308 to 1.7976913486231E308 (real big)	8 bytes
Date/Time	Dates and times	8 bytes
AutoNumber	Unique sequential or random number automatically inserted in a new record	4 or 16 bytes
Yes/No	Boolean values: yes/no, true/false, on/off	1 bit
OLE Object	Can store objects like pictures, sounds, or binary files like Word docs	Up to 1 gigabyte

 

Operators - some examples

Arithmetic	+ , - , - (unary) , * , / , \ , Mod , ^
Comparison	< , <= , =, >= , > , < >
Logical	And , Or , Not , Xor (exclusive or )
Other	Is , Like , In , Between , & ( concatenation )

 

Functions - some examples

Date/Time	Date( ) , Now( ) , Day( ) , Month( ) , Year( ) , DateDiff( )
String	Format( ) , InStr( ) , LCase( ) , Left( ) , Trim( ) , Val( )
Conversion	CInt( ) , CSng( ) , CStr( ) , CCur( )
Math/Trig	Abs( ) , Cos( ) , Log( ) , Rnd( ) , Sqr( )
Financial	FV( ) , NPV( ) , PV( ) , Rate( ) , Pmt( )
Misc	IIf( ) , IsNull( ) , IsDate( )
Aggregate	Avg( ) , Count( ) , Max( ) , Min( ) , StDev( ) , Sum( ) , Var( )

Look for web links to useful database learning tools at: http://agebb.missouri.edu/cotf/ a few weeks after the conference.

The other three files topics are:

• FRMSMP97.EXE - example forms
• RPTSMP97.EXE - example reports
• NEATCD97.EXE - example VBA code

Stephen Martin
martinst@missouri.edu
AgEBB (573) 882-4827