Drewslair.com - Microsoft Access for Beginners: Glossary

Microsoft Access for Beginners - Additional Information

Glossary

Application: In Microsoft Access, a database designed to provide a specific solution which includes a combination of objects such as queries, forms, reports and functions.
Auto Expand: A feature in Microsoft Access and other software which automatically completes entry of information into a field based on previous entries or other criteria.
AutoNumber: A database field type which automatically generates a random or sequential unique value for a field when a new record is entered.
Boolean: As a data field type, this indicates a value that can be either TRUE or FALSE. These two conditions can be represented in the field either by those words or through values such as 0 and 1 or Y and N.
Column: In a database table, this is a collection of cells that represents one element of data across all the rows such as a name or a city value. See Field
Composite Key: A primary index in a table that consists of more than one field so that no two records within the table can have the same combination of values in these fields. An example would be a table of contacts where the composite key combined the name and phone number fields. More than one record may exist with the same name or the same phone number but not the same combination of both.
Concatenate: To combine multiple text values and / or functions that generate text values into a single string using the " &" character, i.e. "Printed on: " & Date() could result in "Printed on April 30, 2008". This would be used to provide values for labels and text boxes, especially on reports.
Control: Part of a program interface that provides functionality to the user and can be designed to respond to events and user actions. Examples include command buttons and toolbars.
Data Normalization: The process of organizing data into tables or other groupings by subject so as to eliminate redundancy and ensure the integrity of the data.
Data Type: A format used by a database to store different kinds of data. Data types are defined by the properties and limits of the data being stored. Examples include the Text or String data type which stores alphanumeric data such as city and state names and the Integer type which stores whole numbers within a given range. Data types also differ in the functions that can be performed on them and the amount of space that is used to store them. A number such as 33325 could be stored as a numeric type (integer, single, double, etc.) so that mathematical functions could be performed on it. It could also be stored as text or string data if part of an address or other text field.
Database: In general terms, a database is any collection of formatted data such as an address book or any other list of items that share a set of common characteristics. In a relational database, data is grouped into tables by subject or object type with columns that contain the specific attributes (i.e. name, address, city) and rows for each item stored. Database applications designed in Microsoft Access include objects such as queries for retrieving specific data, forms for entering and displaying data and reports for compiling the data into a presentable format.
Datasheet: A display of rows and columns that represent the data held in a data table or generated by a query.
Event: In programming, an event is a condition which the program can respond to such as the click of a command button or the change in a specific value. Events can be generated either by user action or by a change in program conditions. Multiple events may occur or "fire" in rapid succession as part of a larger task such as the display of a form. The programming of an application based on response to specific events is called event-driven programming and provides for an environment in which the program function is controlled dynamically rather than a linear approach that simply runs a set of steps in order.
Field: In database terms, a table element that holds a specific piece of data. A field can be represented by a column within a table or a box on a form where the user can enter or view the data.
Field Validation: The process of applying certain rules to data within a field. This may be a requirement or limitation on the number of characters that can are entered, such as with a password, or the assurance that the data entered falls within a certain range, such as a date.
Filter: A set of rules that are applied when selecting information from a database. The filter limits the amount of information returned thereby enabling the user to see only the relevant data.
Foreign Key: A column in a database table that is used to match records with data in another table. One example would be a Customer ID field which is used as the primary key in the main Customer table and then as a foreign key in a table which lists the customer orders. The foreign key identifies which orders belong to a specific customer.
Footer: A repeating section in a form or report that is displayed at the bottom of each page or of the document itself. It can contain titles and controls such as page numbers and date displays.
Form: An object within a Microsoft Access application which is used to display and enter information. Forms may include multiple controls such as text fields and even other forms that are used to group and enter data. The forms can also contain a large amount of programming that is used to provide various functions to the user or to manage the operation of the interface.
Grouping: Within a query, data can be grouped on one or more fields in order to provide results on groups of records. An example would be a grouping by city or department used to report information or subtotals of different values based on these groupings of data. Groups are also used in forms and reports to create custom headers and footers based on specific fields.
Header: A repeating section in a form or report that is displayed at the top of each page or of the document itself. It can contain titles and controls such as page numbers and date displays.
Index: A structure in a database table that assists in the sorting and retrieval of data from the table. Indexes may be placed on single fields or combinations of fields and may apply restrictions such as the requirement of a unique value.
Inheritance: In Microsoft Access, the ability of a form field to inherit certain properties from the table field that it references. This often includes functions such as a data lookup on the field, providing pre-selected values for entry.
Interface: A collection of controls that enable the user of a program to use its various functions. This can be in the form of commands entered on a command line or graphical controls on a form.
Join: The link between two related tables. A join specifies how the data is related and which table is to take precedence in the relationship. The joins between tables affect the way that data is retrieved by queries and other elements in the database.
Lookup Field: A field that references a list of values from which the user can select the correct choice. This list may be a short list of values programmed into the field or a dynamic list drawn from a database table.
Macro: A set of commands intended to run in order to carry out a specific task.
Margin: The areas at the top, bottom and sides of the report where data is not printed. The margins are set for a report in the Page Setup dialog.
Menu: A list of options from which a user selects the necessary function.
Microsoft Access: A relational database management system (RDBMS) designed for use on personal computers. The software provides a system for designing database applications for use by a small number of users. These applications can include data entry forms, queries and reports in order to provide for all phases of data collection and analysis.
Normal Forms: A set of rules for organizing data which ensures that it meets specific requirements of data normalization. The normal forms are applied in order with each building upon the last in order to eliminate various types of redundancy from the data. Normal forms are referred to by the order in which they are applied (i.e. First Normal Form or 1NF). There are three basic forms which are most commonly applied with additional forms for specific situations.
Recordset: A collection of data records. This term most often refers to records that have been retrieved using a query or stored procedure and may have been pulled from a combination of tables.
Primary Key: The main index on a data table which usually contains a unique value to identify the item stored in each row of the table.
Query: A set of instructions which retrieves data from a database table. In Microsoft Access queries are designed using Structured Query Language (SQL).
Record: A collection of fields or values which combine to describe a single item. In Microsoft Access, a record is represented by a single row within a table.
Record Validation: The process of applying certain rules to the data within an entire row. These rules often involve the way in which different fields relate to each other. An example would be an employee table where the employee's start date could not occur before the hire date.
Referential Integrity: The process by which relationships between tables are enforced. Most commonly, this ensures that a record cannot exist in one table without a related record in another table. For example, an order cannot exist in the Orders table without a corresponding customer record in the Customers table. It also ensures that data cannot be altered in a way that would defeat the established relationships. Again, a record in the Customer table could not be deleted without considering all of the related records in other tables. Access allows for cascading updates and deletions of related records in tables to maintain referential integrity.
Relational Database: A database in which data is arranged into tables by subject or object represented which are then related or joined by common fields. A common example is a customer orders database in which one table contains information specific to the customer such as contact information and another table contains the orders. A Customer ID would be used to link these tables. This allows for organized and flexible entry and storage of the data without the limitations of a flat file. In a properly normalized relational database, data is stored so as to eliminate redundancy of information. In the example given, the customer contact information is stored once while each customer can have an unlimited number of orders in the related order table. A third related table might store individual items on the orders so that each order could have an indefinite number of products.
Relationship: Relationships specify how one set of data corresponds to another and are expressed in terms of the number of members on each side of a relationship. For example, the relationship between a Customer table and an Orders table would be a one-to-many relationship with many orders for each customer. One-to-one relationships are used when there is only one member on each side such as if certain information for each customer is stored in a separate table. Many-to-many relationships are also possible within a relational system but cannot be directly represented without an intervening table which will hold the ID fields from the tables on each side and break the relationship down into separate one-to-many relationships. One example of a many-to-many relationship would an inventory database tracking products and suppliers. There might be many suppliers for a given product and many products from each supplier.
Report: A database object that compiles database information in a format suited for printing and analysis. The report design contains fields that pull data directly from the database and can manipulate or perform calculations on that data as needed.
Row: In a database table or query, this is a collection of cells across multiple columns that represents one data record.
Schema: The schema describes the way the data will be organized and represented. This may be as general as an outline of objects represented by the database (i.e. employee, customer, order, product) and the way in which they relate or as specific as the collection of tables representing each object and the relationships drawn between them.
SQL: Structured Query Language - A scripting language that is used with relational databases to provide an interface for the retrieval and storage of information in the tables. The language provides keywords such as SELECT, INSERT and ORDER BY to specify actions to be performed on specified fields and tables.
Subform: In Microsoft Access, a form that is contained within another form, usually displaying a related set of data. For example, an employee form might contain a subform that would display a list of skills and certifications held by the employee which would be stored in a separate table. The subform is related to the main form by a query that joins the two tables.
Subreport: A subreport behaves similarly to a subform, displaying information related to the subject of the main report section.
Tab Order: The order in which the TAB key will move the focus between various fields on a form in Microsoft Access. This order can be set by the designer of the form to assist in proper data entry.
Table: A database object that holds data in the form of rows and columns. This can be a stored object within the database file or an abstract recordset that is held in memory during an operation.
Template: An object which defines a pattern according to which data will be entered. This can be a collection of symbols that specifies what type of characters will be allowed in each position.
Toolbar: A program bar which includes a collection of buttons that can be clicked to perform various functions. In Microsoft Access, pre-defined and custom toolbars can be used with forms as part of the user interface.
Unicode: An expanded character set used by Microsoft Office and other programs to represent a wide range of characters from different languages including Arabic, Hebrew, Chinese, Japanese and others.
Value List: A list of values made available to a control for selection by the user. In Microsoft Access, this typically refers to a short list of values that are entered into the property sheet of a drop-down style form control.
Visual Basic for Applications (VBA): A version of Microsoft Visual Basic that is used with the Microsoft Office Applications, most notably Access. VBA is used in conjunction with forms, reports and other controls to perform functions beyond what is possible through standard macros. The language offers many of the features of Visual Basic including error handling, conditional operations and debugging tools.
Wizard: A section of a program which uses a series of dialog boxes to guide the user through the choices involved in the creation of a project. In Microsoft Access, wizards can be used for everything from creating simple controls such as command buttons to entire database applications.

Return to Series Index