Tables by George Hernandez

A Table is an object to define and store data. It is a datasheet collection of fields (columns) and records (rows), the cells (intersections) of which are used to define and store instances about a particular subject. Tables are usually prefixed with tbl.

Tables can have a Primary Key to make each record unique. Tables can utilize Indexes to speed data retrieval.

Fields in a table can utilize Lookup Properties to facilitate the lookup of frequently entered items.

It is good practice to run the Compact Database utility under the Tools menu in the Database Utilities selection.

Tables are said to be Joined into Relationships.

A Relationship describes how one Table (or Query) of data may be joined or related to another.
- One-to-One: one row (record/instance) ties to one row in another table, eg a table of students joined with a table of students' health records: each student has only one health record.
- One-to-Many: one row in the parent table ties to one or more rows in the child table, eg a table of students joined with a table of tickets: each student may have multiple tickets.
- Many-to-Many: one or more rows tied to one or more rows in another table, eg a table of students joined with a table of classes: each student may have multiple classes and each class may have multiple students. Many-to-many relationships are often accomplished by creating a separate third intersection/junction table that acts as a child table to the two parent tables.
A Join describes how the fields are matched in the relationship between one Table (or Query) of data and another. Two related datasheets will have a common field that is the key to the relationship.
- Inner Joins (aka equi-joins) bring together rows from two tables whose common field match.
- Outer Joins also include unmatched rows.
  - Left Joins include all the rows from the first/left table. An arrow will show on the right end of the join line.
  - Right Joins include all the rows from the second/right table. An arrow will show on the left end of the join line.

Importing, Linking, and Exporting

You can import from, link to, and export as Access, dBASE, Paradox, FoxPro, SQL-ODBC, spreadsheet (eg Lotus 1-2-3 or Excel), and text files.

Text files should be delimited (i.e. usually with commas, tabs, semicolons, or spaces) or fixed-width (i.e. each field occupies the same number of spaces in each record). In delimited file, fields with text strings should be enclosed with a qualifier such as double quotes. If there are qualifiers within the text string, then those qualifiers should be enclosed within another qualifier such as single quotes.

Reasons for importing another database file:

The file is relatively small
The file is not frequently used by others.
The data you create from that file won't be used by others.
The old file is going to erased.
You need the best performance while using the data

Reasons for linking to another database file:

The file is larger than the allowable Access file size of 1 GB.
The file is frequently used by others.
The file is shared on a network.
You want to separate the data (tables) from the "application" (queries, forms, reports, macros, and modules). This allows you to modify the application without having to import the file again.

Fields

Fields are chosen from Tables or are calculated from other fields by using Zoom to manually make the expression, or by using the Expression Builder utility. Field Properties set in Queries override the Field Properties from the Field's source.

Field names can be up to 64 characters, including spaces ( ) and most symbols except for periods (.), exclamation marks (!), square brackets ( [ ] ), or the back quote (`). The use of spaces is not recommended if you ever expect to move your application to a SQL database that does not support spaces in names. For that matter most variations of SQL do not accept any special characters -- just letters and numbers. Some SQL variations also only accept a maximum of 18 characters for table and column names

+: Adds two numeric expression
-: Subtracts the 2nd numeric expression from the 1st
*: Multiplies two numeric expressions
/: Divides the 1st numeric expression by the 2nd
\: Round two numeric expressions, divides the 1st by the 2nd, drops the remainder, and then returns an integer
MOD: Round two numeric expressions, divides the 1st by the 2nd, and then returns the remainder
^: Raises the 1st numeric expression to the power indicated by the second
&: Converts either of two expressions into text strings if necessary, and then concatenates the 2nd text string onto the 1st
" ": Encloses a string, pattern string, or segment of a pattern string
[ ]: Encloses an object name, eg the name of a field. Also used to enclose Query Parameters.
.: Separates objects when qualifying them, eg [Table1].[Field2]. Note that the exclamation mark (!) is also used in the same manner.

Page Modified: (Hand noted: 2007-10-12 18:29:38Z) (Auto noted: 2007-11-17 06:45:39Z)