Databases

The records stored in a database are very much like the records you keep on paper and accumulate into file cabinets. Customer records store address information, contact information, and other details about the companies and people you provide products and services to. Freight bill records store the details of the transactions between you and your customers.

Records are sometimes called rows because it is easy to visualize many records as a ledger or list and each one entered into a single line - a row.

A field is a single piece of information that makes up one part of a record. If we continue with the idea of a ledger, a single customer record can be arranged into regular fields such as business name, address, phone number, etc.

Fields are sometimes called columns because all the fields line up vertically in a ledger.

In simplest terms, a table is a collection of records. Inside the TruckMate there is a table called CLIENT that stores all the customer records. Another table called GLACCOUNT, stores a list of the General Ledger Chart of Accounts. If we were to walk into Scrooge’s accounting house, we would no doubt be able to find a ledger book containing records of all his customers.

There are many different kinds of databases. Some of them are very simple and some are staggeringly complex. One of the things that all databases have in common is the ability to share data with more than one person at a time. More than one user can look at the same customer record at the same time. Accessing the database for information is called querying.

Simple queries ask the database to retrieve all records from one table. More complex queries ask the database to retrieve data from only certain fields and/or only records that meet specific criteria. But there even more complicated queries than these.

The databases that are used by TruckMate allow many tables to be defined and related to one another. Both IBM DB2 and Borland InterBase are examples of relational database management systems - RDBMS’s. Relational databases enforce relationships so that data does not become corrupted.

An example of a table relation would the one between GLACCOUNT and GLTRANS. GLACCOUNT lists all of the General Ledger account codes defined. GLTRANS stores journal entries made against an account code. Journal entries can only be entered against an account code that exists in the GLACCOUNT table. The RDBMS enforces this by checking the account code entered into the GL_ACCT_ID field in records entered into GLTRANS. The account code has to match one of the account codes listed in GLACCOUNT and if it does not the RDBMS raises an error, preventing the user from completing the journal entry.

And now we get to the important stuff. The ability to relate tables allows you to query the database for information from more than one table at a time. These types of queries happen far more often than the simple queries mentioned earlier. Here are some examples of this kind of query:

Generate a list of freight bill records that were delivered in the month of May and were billed to customers with addresses in the state of Washington, USA.

Generate a list of drivers who are available to be dispatched in the Chicago area and have enough service hours available to deliver a load to Savannah, GA in 2 days.

It is these kinds of queries that you will want to perform on your database on a day-to-day basis. To create and use these queries you will need to:

When you become familiar with how data is stored in fields, records, and tables, you will find that building Crystal Reports will be much easier.