➪ (Under editing...)

Intro

The OpenOffice.org(OOo) database-management program namely Base is part of the Open Office suite, that enables you to maintain databases — collections of related data arranged according to a fixed structure (table made with a variable number of records. Each record has a fixed number of fields). Its structure makes the information easy to:

select
sort
display
and print in a variety of formats.

With Base (as any other DBMS), you can create and maintain as many databases as you need — you can even share them with other people over a local area network or the Internet.
Base is a general-purpose program — mean that works with almost any kind of information. A database can be as simple as a list of addresses to replace your card file. Or you can create a more heavy bookstore-inventory database with information about books, publishers, customers, and special orders. Base can also handle complex databases that contain lots of types of information and lots of customized programming (trough OOo Basic a Basic-like dialect).

The OpenOffice.org Basic programming language can be divided into four components:

The Basic language of OpenOffice.org : Defines the elementary linguistic constructs, for example, for variable declarations, loops, and functions.

The runtime library: Provides standard functions which have no direct reference to OpenOffice.org, for example, functions for editing numbers, strings, date values, and files.

The OpenOffice.org API (Application Programming Interface): Permits access to OpenOffice.org documents and allows these to be created, saved, modified, and printed.

The Dialog Editor: Creates personal dialog windows and provides scope for the adding of control elements and event handlers.

VBA Vs OOo Basic : Compatibility between OpenOffice.org Basic and VBA relates to the OpenOffice.org Basic language as well as the runtime library. The OpenOffice.org API and the Dialog Editor are not compatible with VBA (standardizing these interfaces would have made many of the concepts provided in OpenOffice.org impossible).

    A Base database can contain lists of records about almost anything. Unlike a spreadsheet program, Base makes information in lots of different formats easy to display — including alphabetical listings, formatted reports, mailing labels, and fill-in-the-blank forms.
    Base comes in scene firstly as a part of the Open Office 2.0 suite of programs. Previous versions of OOo don't have included a standalone DB application. Because Base is part of OOo suite of programs, sharing information with Writer documents and Calc spreadsheets is easy. In Base (some of the so called data sources) text files, spreadsheet files, Address book data are accessible in read only mode. No one can modify (edit, insert, delete) their structure.
   Base database files utilize an .odb file extension ( the ISO/IEC standard OpenDocument file formats (ODF), which it uses natively, by default) contains:

queries
reports
forms
formating info
a link to the DB where the records are stored
and indirectly macros or Basic code.

odb files are maintained with an HSQLDB database engine. HSQL is written in Java with a JDBC driver. It performs well for development and small client database applications, similar to Microsoft Access. It is not an enterprise-level database server.

The Six Types of Base Objects

Base databases are made up of objects — things you can create, edit, and delete, each with its own name and settings. Object-oriented systems allow you to create these things one piece at a time, using pieces that fit together.
Base contains various kinds of objects, including objects for storing, displaying, and printing your data, as well as objects that contain programs you write. At first, you’ll probably use only a few types of objects, but as you customize your database, you may end up using them all. You start with:

tables for storing data
forms for editing data on-screen
reports for printing data, and
queries for selecting and combining data

Later, you may create macros and modules, which contain programs that you write. Now we cover each of this main types of Base objects.


Tables for storing your data

   Tables are where you put your data. A table is an Base object that is made up of a series of records — the electronic equivalent of the index cards that make up an address list. Each record contains information in the same format.
   In an address list, each record contains information about one person: name, address, and other facts. Each individual piece of information — such as first name, last name, or street address — is called a field.
   Your database can contain many tables.

A bookstore database (for example) can contain a table of books (with title, publisher, price, and other information about each book), a table of vendors from whom you buy books (with company name, address, discount terms, and other information about each vendor), and maybe a table of your regular customers (with name, address, and other information).

In a table each row is a record, and the fields are shown in columns.

Is a spreadsheet a database? Not really. Many people use spreadsheet programs, such as Microsoft Excel or Lotus 1-2-3, to store lists of records. Some spreadsheet programs have limited database capabilities, but they aren’t designed to do as much as a database program. You can use a spreadsheet to store an address list — and you can enter, edit, delete, and sort the addresses (one per row on the spreadsheet) — but printing mailing labels or form letters is a major chore.
Spreadsheets don’t (and can’t) think of your data in terms of tables, records, and fields, but rather in terms of cells (the basic unit of a spreadsheet) arranged in rows and columns. That’s a too limited model for sophisticated information management — as you’ve probably suspected if you’re using Access or Base or other DBMS's for your data-base work. That’s the right tool for the job!

After you set up tables in your database and type in (or import) information, you can

sort the records
select records that match a criterion, and
then display and
print the records

Proper design of your tables — choosing how many tables to create and which fields are stored in which table — is key to creating a usable and flexible database.

Queries for selecting your data

   Queries are operations that slice and dice your data to answer specific data needs. The most commonly used type of query helps you select data from a table, perhaps to select which records you want to include in a report. You can create a query that shows you all the people in your address book who live in (say) Vermont, or all those for whom you don’t have a phone number.
   To create this type of query, you enter criteria that specify what values you want to match in specific fields in the tables (for example, VT in the State field to find Vermonters, or nothing in the Phone Number field to find the phoneless, or both).
   You can also use queries to combine information from several tables. A bookstore database may store book author names in the Books table and book ordering information in the Purchase Orders table. A query can pull information from both these tables — to show (for example) all the Terry Pratchett novels you ordered for the last month.
   Queries can also create calculated fields, including totals, counts, and averages.
   Another type of query is the action query, which does something to the records you select — copy records from one table to another, make a change to all the records you select, delete records you select, that sort of thing.
   Crosstab queries help you analyze the information in your tables by summarizing how many records contain specific combinations of values.

Queries are the way you get useful information out of your tables — and you’ll probably create zillions of them as you play with your database.

Forms for editing and displaying your data

An easy way to enter data, especially into more than one related table, is to use a form — a standard database (precisely is a Writer) document that displays information from one or more tables on-screen. You can do more kinds of work with forms; for example, you can:

edit your data or type in new records
choose the layout of the table’s information on the form
specify the order in which your items appear
group items together with lines and boxes
use pull-down lists, radio buttons, and other types of on-screen controls for entering and editing data

   But why stop there? You can build intelligence into forms, too — program some smart boxes that automatically capitalize what you type in, or check your entry against a table of valid values.
   After your database goes into production — that is, you use it for its intended purpose — forms become the most-used Base's object. As go the forms, so goes the database.

Reports for printing your data

   Forms are primarily designed to appear on-screen; reports (on the other hand) are designed to be printed out. Like forms, reports display information from tables; you get to choose the layout of the information. Most reports are based on queries; you use a query to choose the information that appears in the report.
   The report design defines:

the order in which records appear,
which fields appear where, and
which fonts, font sizes, lines, and spacing to use. (Control freaks, rejoice!)

In addition to reports on normal paper, you can create reports for printing on envelopes, labels, or other printed forms. Base comes with report wizards (Sun Report Builder) that make creating fancy reports easy. It can also print charts and cross-tabulations (crosstabs) based on the data in your database.

Macros for saving keystrokes

Look here. (...Todo expand....)
Macros are programs that automate the commands you give when you use Base— you “write” them by telling Base to record your keystrokes while you do something on-screen. For example:

You can write a macro that moves the cursor to the last record in the Orders table whenever you open the Order Entry form. (What are the chances that you’d want to edit your very first order? Most of us would be likelier to want to edit the last order or enter a new order).
Or you can write a macro that moves your cursor to the next applicable blank in a form, based on the entries you made so far. After you get some practice at creating macros, you can create buttons on your forms that run the macros with a quick click.
You can also tell your form to run a macro automatically whenever you move to a field on the form, or enter data into the field — handy!

   You don’t have to be a programmer to create macros. Base helps you write them by providing menus of commands.

Modules for writing your own programs

   Okay, now we come to the serious programming stuff: modules — another term for (like Visual Basic) programs. VBA (Visual Basic for Applications) is a programming language based on the age-old BASIC language; it’s specifically geared for working in Access and other Microsoft Office programs. Macros are fine for saving a few keystrokes or cleaning up the data you enter in a field, but when the going gets complex, you can use VBA. Identically in OoBase we must use OOo Basic.
   Programming isn’t for the technologically faint of heart. Fortunately, it’s rarely necessary. But when everything else is done in your database, take a look at that. Writing small programs isn’t all that hard — and if you acquire a taste for programming, who knows what you’ll end up creating!

Essential Database Concepts

Here are the Four Commandments of databases. Here exist lots more important rules and guidelines but these four apply right from the start, no matter what kind of database you are using:

Store information where it belongs, not where it appears. Where you store information has nothing to do with where it appears. In a database, you store information in tables based on the structure of the information. Each piece of information likely appears in lots of different places. For example, in a database for an online bookstore, book titles and authors names appear on your invoices, purchase orders, and sales receipts. But the right place to store those book titles and author names is in the Books table, not in the Sales table or the Purchase Orders table.
Garbage in Garbage out (GIGO). If you don't bother to create a good sensible design for your database — and if you aren't carefoul to enter correct, clean data — your database will end up full of garbage. A well- designed database is easier to maintain than a badly designed one, because each piece of information is stored only once, in a clearly named field in a clearly named table, with the proper validation rules in place. Yes, it sounds like a lot of work, but cleaning up a database of 10,000 incorrect records is (pardon the understatement) even more work.
Separate your data from your programs. If you create a database to be shared with (or distributed to) other people, store all the tables in one database (the back end) and all the other objects in another database (the front end). Then you link these two databases together to make everything work. Separating the tables from everything else streamlines the whole rigmarole of updating queries, forms, reports, or other stuff later without disturbing the raw data in the tables.
Back up early and often. Make a backup of your database every day. With luck, your office already has a system of regular (probably nightly) backups that includes your database. If not, make a backup copy of your database at regular intervals, and certainly before making any major changes.

Getting Started, Getting Around

Running Base

   Windows (and similarly *nixes) usually provides more than one way to perform a task; The most popular way to start Base is to click Start and choose All Programs➪ Open Office➪Open Office Base.
   Another way to get the program started is by double-clicking the name or icon of an Base database in --Windows Explorer or other-- file manager (this method both starts Base and opens the database you double-click). Or double-click the Base icon if it appears on your --Windows, Gnome, KDE, Xfce, etc-- desktop.
   Finally to start Base and open a recently used file, choose Start ➪My Recent Documents (or Places ➪ Recent Documents in Ubuntu) and choose the file.
   When you start Base without opening a database, Base wants to help you get started, and the initial Database Wizard window you see gives you the choices for:

starting to build a new HSQLDB database or
use an existing one (HSQLDB) or
connect to a external DBMS (MySQL, Adabas D, MS Access and PostgreSQL etc or trough support for JDBC and ODBC standard drivers allows you to connect to virtually any existing database). In that case Base can hence act as a GUI front-end for SQL views, table-design and query.

Opening a Database

Before you can work on a database, you have to open it in Base. Before you can open it, you have to create it! You can open an existing database from the Database Wizard screen, or within the regular Base window.

Using Wizards Years ago, in a land far, far away (Washington state, actually), Microsoft invented wizards, programs that step you through the process of executing a commonly used command. Instead of presenting you with a big, hairy-looking dialog box with zillions of options, a wizard asks you one or two questions at a time, and uses the information you already provided before asking for more input. All programs in Microsoft Office, including Access, come with wizards. Wizards appear in dialog boxes that pop up in response to a command.

All Microsoft (and not only) wizards follow the same pattern of asking a series of questions. Answer each question and click the Next button at the bottom of the dialog box — and you move to the next step. If you want to go back and change the answer you gave on a previous window, click the Back button. You can bag the whole thing by clicking Cancel. The Finish button is grayed out (and unclickable) until you provide enough information for the wizard to complete his (her? its?) task.You can select all items in a list by clicking the double arrow. Select one by clicking the single arrow. And you can deselect by using the analogous arrow buttons that point in the opposite direction.

Exploring a Template

(...Todo rectify-expand...)
A database isn’t a single object like a Writer document or Calc worksheet. Rather, a Base database is a collection of many different kinds of objects. To see names of objects in the Writer template you’ve downloaded, click the Navigation button at the right side of Writer-Base's standard bar.
.....

Viewing an object’s design

The objects in a database don’t just appear out of nowhere. Someone had to create each object. They did so through the object’s Design view. There are two ways to get to an object’s design view:

If the object is already open, per default you see the Design view.
If the item isn’t already open, click Table (from Database Pane section) ➪ click Create table in design view (from Tasks Pane section).

What’s up with the Security Alert? Whenever you open a database that contains macros or Basic code, Base displays a Security Alert. That’s because people can use macros and Basic code to create malware, software that isn’t good for your computer. There’s no way for Base or the computer to tell if the macros and code in the database is malware or not. You have to decide that for yourself based on the source of the database.

Viewing table relationships

Base is a relational database-management system, which means it’s a tool for managing large volumes of data where there are natural one-to-many (also known as "to-many") relationships among those data. For example:

Every one customer may place many orders.
Every one student may enroll in many courses.
Any one conference may be attended by many people.
Every family unit contains many people.
Every music category, such as jazz, has many artists, albums, and songs.

We could fill this post with examples of natural one-to-many relationships in the world.
   The natural one-to-many relationships among the kinds being managed in one database must be built into the design of its tables. Base can’t figure out the one-to-many relationships for you. Designing the tables so they accurately reflect those relationships is your responsibility, unless, of course, you’re using a template where someone else has already figured all that out.
   The names of tables in a database don’t really tell you anything about the one-to-many relationships among those tables, but you can usually see those relationships through the Relationships design window.
   Click on Tools ➪ Relationships. The Relationships design window opens. Each box in the Relationship window that opens represents a table in the current database. Each name in a box represents a field from that table. You can move and size those boxes using standard File manager's click and drag.
   The lines connecting the tables describe the relationships. The little 1 points to the primary key of the table on the “one” side of the relationship. The n or infinity symbol (it looks like sideways 8 or n) points to the corresponding foreign key on the “many” side of the relationship. For every one customer record, there
may be many records in the Opportunities table. You can’t change the relationships among tables in the Relationships design window. Well, physically, you could mess around with the lines connecting the tables. But the results would probably not be good. All of the other objects in the database (queries, forms, reports) are built around the tables and relationships you see in the Relationships window. Any messing around with connecting lines would almost surely prevent those other objects from working correctly. Nonetheless, the Relationships window gives you a technical bird’s-eye view of what the database is about.

Modifying Objects

   Before you tear into a (imported) template and start making changes, you really need to spend some time working with it. Get to know what it is and how it works. Get a feel for what’s good for your own personal needs, and what’s not so good or missing. Because forms and reports act as the main user interface to a database, you’ll probably want to spend most of your time with those.
   There are two ways to use each object.

In Design view: One way is in the Design view, where you actually create or modify the look and feel of the object. In Design view, you’re playing the role of the person who is creating the database.
As a user of the database: The other way to use an object is as a user of the database. The person who uses a database isn’t necessarily the same person who designed and created the database. The user just uses the finished database to put data into the database (trough forms), and get data out of the database.

   Keep in mind that you never use Design view to use an object. You use Design view only to change an object.
   Finally, keep in mind that the data you see in most forms comes from the underlying tables. If you want to add a field, you actually have to add the field to an appropriate table before you add it to the form.

Modifying tables

   All of the information that an Base database keeps track of is in its tables. If you need to store more information than a template provides, the first step is to create a field for that data. You can’t just stick the field in any table though. It has to be in whichever table is appropriate for the field. For example, if you need to store more information about contacts or customers, the field has to go into the Contacts or Customers table.

To modify a table, open it in Design view. Scroll to the bottom of the list of fields names already in the table. Or, to insert a new field trough menu's bar Double-click on table's name or right-click on table's name ➪ Edit, when table open in Design view right- click on the left side(row selector) ➪ Insert rows (don't care about row's sorting right now; you can edit it later). Then you can type the new field’s name into the column headed Field Name.
After you type the field name, choose an appropriate data type under the column headed Field Type for the new field and optionally a brief field's description under the column headed Description.
After you’ve chosen the data type, you can use options under "Field Properties" to set a size, a default value, a control if the field is required or not (format, caption, default value, or whatever is) appropriate to your field and the data type you chose.
The column to the right of the Data Type field is labeled Description is optional. Feel free to leave it blank if you want.
You can add as many fields as you wish.
Changing the primary key, its field name, or data type will likely cause a world of problems. You might be thinking of using some other unique value, like a part number, product ID, Social Security number, e-mail address, or whatever as a primary key. It’s fine to add such a field to a table. But don’t mark it as the primary key.
After you’ve added your own custom primary key field, you can set its Indexed field property to Yes, No Duplicates. That prevents duplicate records with the same value from being entered into the table. And it won’t mess up the existing primary key, queries, or other things in the database that require the table’s current primary key field.
Deleting fields and changing existing field names could result in some problems as well. The more existing fields you add or change, the more problems you’re likely to create later down the road for yourself (don't forget that reports and forms are function of tables).

Modifying queries

   Queries provide a means of sorting and filtering data from a table. They’re also the tool used to bring data from multiple tables together as though the data were stored in one big table. They’re used mainly to create a dataset to which you bind a form or report.
   When you add a field to a table, that field doesn’t necessarily get added to all queries to which the table is bound. It only gets added, automatically, to queries that contain a * column. The asterisk (*) is the symbol for “all fields from the table.” Of course, it also only gets added to queries that include the table to which you added the field. Other queries don’t need the field.
   To see to which table (or tables) a query is bound, right-click the query name in Queries Pane and choose Edit so you enter in Query's Design view. The table(s) to which the query is bound appears at the top pane of the Query Design window and in table row in Preview Pane (bottom Query Design window) .
   In short, if you add a field to a table, you only need to add that same field to queries that are bound to that same table: Don’t use the * symbol to automatically pull all fields from a table.
   If a query is bound to a table you’ve changed, and if that query doesn’t use the * operator to bind to all fields, you can add the field to the query. Just drag the field’s name from the field list at the top of the query into the field row.

Changing forms

   Most Base forms are like fill-in-the-blank paper forms, except you fill in the blanks on-screen rather than on paper. But forms can actually contain just about anything you want, including pictures, charts, graphs, and columnar tables.
   Most forms get their data from tables or queries (that are bound to tables). When you enter data into a form, you’re really entering it into the underlying table. When you change or delete data on a form, you’re really making that change to the underlying table.
   As with any object, you open a form by double-clicking (right-click ➪ Open) its name in the Forms Pane of Base window. The form opens in Form design view, where you can interact with data from the underlying table.
   When you want to change a form, open it with right-click on form's name ➪ Edit Form open in Design view with more (than before) toolbars open.
   Most forms are bound to an underlying table or query. The name of that table or query shows in the Data Source button when you’re viewing properties for the form as a whole. So let’s say you’ve opened a form in Design view and want to know which table or query it’s bound to. Here’s how you find out:

(Todo...to expand...)

Designing Your Database —the Relational Way—

   What is it, exactly? Designing a database means figuring out how the information is stored — that is, which information Base stores in each table of the database, and how it all connects together. Unlike working with a spreadsheet or word processor, you have to design a database beforehand — you can’t just start typing information in. (Well, sure, you can, but we don’t recommend it — the result is usually a mess.)
   How easy it is later (in maintenance time) to enter and edit information and create useful queries, forms, and reports depends on how well your database is designed. A good database design can streamline your work in Base. Here i takes you through the process of designing the table(s) you need in your database, including the relationships between them.

What Are Tables, Fields, and Keys?

   In Base, you store your data in tables — lists of records that work like the index cards that make up an address list. Each record contains information in the same (fixed) format, in fields — specified places for individual pieces of information.
   If you want to keep track of the customers of your store, you make a table of customers, with one record per customer. Each record is made up of the same set of fields, which could be the customer’s last name, first name, street address, city, state or province, ZIP or postal code, country, and phone number (as shown in Figure 4-1).
   After you use Base to create a table, you can really get busy — entering, editing, deleting, and sorting the records in various ways, and printing many types of reports (including columnar reports, forms, summaries, mailing labels, and form letters). Base allows you to create as many tables as you need in your database.
   Designing a database means deciding (for openers) what tables your database will need to include, and what fields are in each table. At the most basic level, it means designing the needed forms and most likely required reports.
   This is the computer equivalent of designing the form or file card onto which you write the data, specifying which blanks need to be filled in and which are optional.

Data types

Fields can be different data types. Some fields contain textual alphanumeric information, such as a last name or street address. Other fields contain numbers, such as someone’s age. Others contain logical (Boolean) information — a yes or no regarding some condition. Still others contain dates or times, such as the date that the record was added to the database. Table below contains a list of
the most commonly used Base data types.

(...todo report data types...)

Primary key fields for your tables

   A primary key field (or just key) is a field (or sometimes a set of fields) that uniquely identifies each record in a table. If (for example) each product in a Products table has a different product code, then the Product Code field uniquely identifies a record in this table. If you search the Products table for a product code, you come up with — at most — one record.
   However, not all tables have an obvious key field. You may have to combine two or more fields to come up with values that are different for every record in the table. In a Books table, for instance, you may have several books with the same title. If you assume that an author never writes more than one book with the same title, a combination of the Title and Author fields may work as a key field.
   For an address list, you may think that the combination of first and last name would do the trick, but it doesn’t take long before you realize that you know two Jim Smiths. You could use a combination of first name, last name, and phone number.

But you have another alternative: Have Base issue each record a unique number, and use that number as the key field. If you can’t figure out a good set of keys to use for a table, add an AutoNumber field, and Base automatically numbers (generally manage) the records as you add them.

   Base doesn’t absolutely require every table to have a primary key field (or fields), but if you plan to set up relationships between your tables, some tables definitely need them. Most important is that you can't enter new records if your table miss a primary key. Also, key fields speed up a search for records;
   Base creates automatically an index for each primary key field and can zero in quickly on any record by using those primary key values.

What Are Relationships?

   No trick question here — it’s just that some projects require more than one table. For example, a database for a store has to handle lists of customers, lists of products, and lists of vendors, for a start. All those bits of data have to be coordinated in some useful way.
   That’s where relational databases fill the bill. A relational database contains tables that are related. Two tables are related if they contain fields that match. If you have an online video store, a relational database system probably includes related Products and Vendors tables like these:

The Products table: This is a list of the videos and other products you sell, containing one record for each product. Each record for a product includes also a field that identifies the vendor from whom you buy your stock.
The Vendors table: This list includes name, address, and other information about each vendor.

The Products and the Vendors tables are related because the record for each video in Products table includes the name of a vendor in Vendor field that match the Vendor code field in the Vendors table; multiple videos may come from one vendor. Figure 4-2 shows how such a one-to-many relationship (more about that in a minute) works.

Well, sure, you could store product information and vendor information together, in one big table, but you’d soon be sorry. You may want to add fields to the Products table to contain the address of the vendor from which you bought the video. But here’s the problem: Whenever a vendor’s address changes, you have to make that change in the record for every item you buy from that vendor. What a pain!

A key principle of database design is: Store each piece of information once. If you store information more than once, then you have to update it more than once. (In real life — trust us on this one — if you update it in some places but not in others, you end up with a mess.)

How relationships work

Relationships between tables are much simpler than relationships between people. For two tables to be related, you specify one or more fields in one table that match the same number of fields in the other table. In Figure 4-2, the Product table relates to the Vendors table because the Vendor field in the Products table contains values that match the Vendor Code field in the Vendors table. When you look at a video in the Products table, you can find information about the product’s vendor by finding the record in the Vendors table that has the same value in the matching field.
Relationships, also called joins, and come in several flavors:

One-to-many: One record in one table matches one, or many records in the other table. The relationship in Figure 4-2 works this way because one vendor can sell many videos.
One-to-one: One record in one table matches exactly one record in the other table — no more and no less.
Many-to-many: Zero, one, or many records in one table match zero, one, or many records in the other table.

Next we explain all these three types of joins.

One-to-many relationships

This type of relationship is the most common among tables (by analogy, think of one person with a circle of friends). In a one-to-many relationship, many records in one table can match one record in another table. Here are some examples of one-to-many relationships:

Items in customer orders: If you run a store, customers frequently buy several items at the same time. One record in the Orders table could match several records in the Products table.
Vendors and invoices: If your company buys many items from another company, you end up with a bunch of invoices from (and payments to) that company. The relationship between the Vendors table and the Invoices table in an accounting database is one-to-many.
People living in states or provinces: The United States and Canada use standard two-letter state and province abbreviations, and if you have an address list, these codes should be correct. (Quick — is Quebec “QU” or “PQ”? No peeking.) To make sure you type in the valid state and province codes for the United States and Canada, you can create a State/Province Codes table against which you can validate entries in the State field of your Addresses table. One record in the State/Province Codes table can match many records in the Addresses table.

You use a one-to-many relationship to avoid storing information from the “one” table multiple times (the so called redundant information) in the “many” table. For example, you don’t want to store all the information about each student in the record for every class —unless you want to hear the groan of an overloaded drive. Storing each student’s information in one place (the Students table), and storing only the student’s name and/or student ID in the Classes table is more efficient (and easier to maintain). Many database designers call the “one” table the master table and the “many” table the detail table. In Base, primary key means the matching field(s) in the master table; foreign key means the corresponding field(s) in the detail table. In Figure 4-2, the Vendors table is the master table and the Products table is the detail table. The primary key (in Vendors table) is the Vendor Code field; the foreign key (in Products table) is the Vendor field.

One-to-one relationships

This type of relationship — where one record in one table matches exactly one record in another table — is much less common in database design. However, you may have reasons (perhaps security reasons) for separating information into two tables. Suppose, for example, you store information about the employees of your company. The Employees table contains the basic information about each employee (name, address, phone, and other personal information). The Employee Health table contains information about each employee’s health-insurance policy (in your company, all employees have insurance). Each record in the Employees table matches exactly one record in the Employee Health table, and vice versa.

The question is: If you have exactly the same number of records in the two tables, and they match exactly, why not just combine them into one table? Most of the time, that’s exactly what you should do. In the employee-database example, you can just add the health insurance information to the Employees table and do away with the Employee Health table.
However, occasionally you have a good reason to separate information into two tables connected by a one-to-one relationship. We came up with two such scenarios:

Security: One of the tables contains much more sensitive information than the other, and you want to restrict who can see the information in that table. Store the sensitive information in a separate table.
Subset of records: Maybe only some of the employees in your company have health insurance. (This is the real world, after all.) Rather than leaving a lot of fields blank in the Employees table, storing insurance data in a separate, related table is more efficient.
Multiple databases: Some information is stored in a separate database. When you use one database, you can link to a table in another database to work with the information in that table as if it were stored in your own database. If someone else’s database has information you need and you link to it, you can’t combine the two tables into one table, but you can set up a relationship.

Don’t be surprised if you almost never create one-to-one relationships between database tables; For a one-to-one relationship, you need one or more fields that link the two tables. Make sure that both tables have the same primary key field(s).

Many-to-many relationships

Many-to-many relationships are more complicated than either one-to-one or one-to-many relationships. That’s because a many-to-many is really two relationships in one. Here are some examples of tables in which zero, one, or many records in one table can match zero, one, or many records in the other:

Students in courses: If you create a database to keep track of students in a school, many students are in each class, and each student takes many classes. You have many records in the Students table matching one record in the Courses table. You also have many records in the Courses table matching one record in the Students table.
Committees: If you set up a database for a club or religious group, you may want to keep track of who is on what committee. One person can be on lots of committees, and one committee can have lots of members. The relationship between the People table and the Committees table is many-to-many.
Books and authors: One book can be written by a group of authors. And one author can write many books. The relationship between the Books table and the Authors table in a bookstore inventory database can be many-to-many.

Figure 4-3 shows a many-to-many relationship between students and courses. Each student is in several classes; each course has its own bunch of students.

The problem is that Base (and most other relational-database programs) can’t handle directly many-to-many relationships. Base refuses to accept that these relationships exist. (Don’t we all know people like that?) But don’t worry — you can work around this problem. You can create an additional table (called a junction table) that records the connections between the two tables.

In the students and courses example, you can make a new table called Course Registrations. Each record in the Course Registration table assigns one student to one course.

The Students table and the Course Relationship table have a one-to-many relationship: The Students table is the master table and Course Registrations is the detail table.
The Courses table and the Course Registrations table also have a one-to-many relationship: Again, the Courses table is the master table.

In fact, you probably want that table anyway, because you need someplace to record the student’s grade in that course. (We frequently find that the new junction table is useful anyway.)
Figure 4-4 shows the relationships among the three tables: Students, Course Registrations, and Courses.

To provide a single primary key field that uniquely identifies each student, we added a Student ID field to the Students table. Each record in the Course Registrations connects one student (by Student ID) to one course (by Class number).

In real life, we’d add fields for the student’s grade, payment date, and other information about the student’s enrollment in the course.

Designing a Database

When you feel at ease with the concepts of tables, fields, and relationships, you’re ready to design your own relational database. The rest of this section walks you through designing your database tables so your database is easy to use, flexible, and efficient. We use the example of a bookstore as we go through the steps to show you how designing works.

Identifying your data

Find out:

what information is available,
who maintains it,
what it looks like, and
how it is used.
Make a list of the possible fields (don’t worry yet about which fields end up in which tables). For example, a bookstore needs to track product descriptions, prices, purchase dates, customer names, who bought what, shipment dates (for online orders), and other information.

Eliminating redundant fields

Look over the fields you identified — make sure they’re all actually needed for your application.

Is each piece of information something that may appear on a form or report later, or be needed to calculate something? If not, throw it out. In this case, it’s worth repeating: Don’t store the same information in more than one place. In a database, redundant information makes double the work when you’re updating the information. Instead, figure out the right place to store the information, and store it there — once.
If you can calculate one field from another field, then store only one. For example, storing both age and birth date is pointless; a person’s age changes — the birth date doesn’t. Store the birth date; you can always get Base to do the math for you.
The same is true for information that you can look up. For codes of all types (such as state and province codes, product codes, and the like), make a table for the code that includes a field for the code and a field for the code’s meaning. Then all the other tables in your database store only the code — and Base looks up the code’s meaning when you need it to appear in a form or report. For the online bookstore, you don’t need to store the title and author of each item that a customer buys; instead, you can just store the ISBN (unique book number) of each book.
On the other hand, sometimes you can’t avoid redundancy. For example, an item of information may change in one place but not in another, so you may have to store it in more than one place. In the bookstore system, when the price of a book changes, the amount that the previous customers paid for the book hasn’t changed. In addition to storing the book’s current selling price, you may want to store the book’s price in the record for each sale.

Organizing fields into tables

Okay, you have a bunch of fields. Are they all in one table, or should you set up multiple tables?
One way to tell whether your system needs multiple tables is to check whether you have different numbers of values for different fields. Say the bookstore carries 200 different products (mainly books, we assume) and you have about 1,600 customers. You have 200 different product names, prices, and descriptions — while you have 1,600 different customer names, addresses, and sets of credit-card information. Guess what — you have two different tables: a Products table with 200 records and a Customers table with 1,600 records.

You could start out with a design like the figure next:

In this case, you’d soon realize that one customer can make more than one purchase. Combining customer information with purchase information won’t work — what happens when a customer buys something else? So you leave information about the customer in the Customers table — all the facts about the customer that don’t change from one purchase to the next — and move information about a specific purchase into a separate Orders table, like this below:

But wait — what if the customer buys more than three books at a time? (We usually do.) And if you own the bookstore, you don’t want to put an arbitrary limit on how many items your customer can buy. (Limit your profit for the sake of your database? In a word, nope.) Any time your database design includes a bunch of fields that store essentially the same kind of information (for example, Book 1, Book 2, and Book 3), something is wrong.

An order can consist of zero, one, or many books — does that sound familiar? Yes, a one-to-many relationship exists between an order and the items in that order, so you need to make a separate table for the individual items, like this:

Now each time a customer places an order (or comes into your store to make a purchase), you create one record in the Orders table, along with one record for each item purchased in the Order Details table. The Order Details table has room to store the quantity of that item, in case the customer wants more than one of something. You should also store the selling price of the book. Base can calculate the cost of that quantity of each book (price × quantity), so you don’t need to store that information.
The following are really good reasons not to store multiple fields (such as Book 1, Book 2, and Book 3) in one table, and to create a separate table instead:

You can’t anticipate the right number of fields. If someone buys more than three things (as in this example), you have to create a separate order and enter everything twice.
You can’t analyze the information later. What if you want to see a list of everyone who bought the last Harry Potter book, so you can notify them that the next one is coming out? If you have multiple fields for this information, your query needs to look for orders that contain a Harry Potter book in Book 1 or Book 2 or Book 3. What a pain.

We don’t want to drive this into the ground, but creating multiple, identical fields is a problem that many first-time database designers make for themselves. Be good to yourself and don’t do it!

Add tables for codes and abbreviations

Look at your tables to see whether the fields contain any standard codes, such as two-letter state and province codes, ZIP codes, or other codes. For example, the bookstore’s Customers table includes a State/Province field and a ZIP/Postcode field. The Products table contains a Product Type field so the bookstore can track sales of books (type B) versus other types of stuff (such as, F for food or A for audiotapes). Determine whether your system needs to do one of these tasks with the codes:

Validate the codes. Wrong codes cause trouble later: Validating the codes when you type them in is always best. If someone types VR for Vermont, the post office may not deliver your package. And later, when you analyze your sales by state, you have some Vermonters with the right code (VT) and some with the wrong code.
Look up the meaning of the code. Codes usually stand for something. Should your system print or display the meaning of the code? If you have a report showing total sales of products by type, printing Books, Food, and Audiotapes (rather than B, F, and A) is nice.

If you want to either validate or look up the codes you store, create a separate table to hold a list of your codes and their meanings. For example, you could add the following two tables to the bookstore database.

Although ZIP codes and postal codes are codes, most databases don’t include tables that list them. The reason is simple: Pretty soon your system would be overstuffed with them (about 100,000 ZIP codes exist, for openers). Plus you have to update the table constantly as the post offices issue — and change — ZIP and postal codes. If you really want to validate your ZIP codes, you can buy a ZIP code database from the U.S. Postal Service.

Choosing keys for each table

The next step in designing your database is to make sure each table has its own primary key field(s). Each table needs one or more fields that uniquely identify each record in the table. Look for a field in the table that has a different value in each record. For example, in the Products table, each book has a unique ISBN (International Standard Book Number). If your bookstore sells stuff other than books — say, bookmarks, espresso, and expensive little pastries — then you can make up codes for them. If one field is different for every record in the table, you’ve found your primary key field. For lists of codes, the code field is the key.

Autonumbering your records
Well, okay, you may not find a unique field. It happens — tables that list people (such as the Customers table) can pose such a problem. Some people have the same name; family members or roommates can share an address and phone number.

Most businesses end up creating and assigning unique numbers to people to avoid this problem. (For privacy reasons, don’t even think of asking for anyone’s Social Security number. Make up your own customer number!)

Fortunately, assigning a unique number in each record in a table is easy in Base: Just add an AutoNumber field to the table, and Base numbers the records as you enter them. In your bookstore system, you can add a Customer Number field to the Customers table.
   The advantage of using an AutoNumber key as the primary key field is that you can’t change its values. After you relate two tables by using an AutoNumber field as the primary key, breaking the relationship between the tables if you have to edit the value of the AutoNumber field later is impossible.
   For the Orders table, you can use Customer Number (instead of the customer’s name) to identify who places the order. However, because one customer may make several purchases, you still don’t have a unique key for the Orders table. One solution is to use a combination of fields as the primary key. How about using the Customer Number and Purchase Date fields together as the primary key? This solution works fine as long as a customer doesn’t make two orders on the same day. (Hmm, that may not work —people sometimes forget to buy everything they need, and come back later for one or two more items. Instead, you can add an AutoNumber field to this table to provide a unique Order Number).

Two key fields are sometimes better than one
Sometimes using a combination of fields works fine. In the Order Details table, you’d better add a field for the Order Number, so you can get immediate access to whatever order contains these items. You don’t need to add a Customer Number field in this case; after you identify the Order Number, Base can look up the Customer Number and other customer information.
   The Order Number doesn’t uniquely identify records in the Order Details table because one order can (and a bookseller would really love it to) include lots and lots of books. Use a combination of the Order Number and the ISBN as the primary key for the table — that way one order includes one entry for each book purchased.

A sample order-entry database design

Here is the new, improved table design for a bookstore system, with asterisks by the primary key fields.

Linking your tables

If you end up with only one table, you can skip this step — but that situation is fairly rare. Almost every database ends up with a second table at the very least — to contain those pesky codes.
Look at the tables in your database and see which tables contain fields that match fields in other tables. Determine whether there’s a one-to-one, one-to-many, or many-to-many relationship between the two tables (as described in the section “What Are Relationships?” earlier in this section). For each pair of related tables, you can determine which fields actually relate the tables by following these guidelines:

One-to-many relationships: Figure out which is the “one” (master) and which is the “many” (detail) table in this relationship. Make sure that the detail table has a foreign key field (or fields) to match the primary key field(s) in the master table. The Customers and Orders tables have a one-to-many relationship in the bookstore example — because a customer may have no, one, or many orders. (Okay, someone who has no orders is technically not a customer but still counts as a one-to-many relationship.) The primary key field in the master table (Customers) is Customer Number. To relate the tables, the Orders table has to have a Customer Number field as the foreign key.
One-to-one relationships: Make sure both tables have the same primary key field(s).
Many-to-many relationships: Base can’t store a many-to-many relationship directly. Set up a junction table to connect the two tables, containing the primary keys of the two tables. In the bookstore example, the Orders and Products tables have a many-to-many relationship: One order can have many products and one product can occur in many orders. The Order Details table provides the junction table, which contains the primary key of the Orders table (Order Number) and the primary key of the Products table (Product Code or ISBN). This junction table can also include additional information (the Order Details table includes the quantity of the book that’s ordered, as well as the price of each book).

The related fields don’t need to have the same name in the two related tables. But the types, lengths, and contents of the fields have to match. (We usually find the two fields having the same names less confusing — preserving sanity is also good for business.)

Refining your links
The relationships between your tables can be a bit more complex — what relationship isn’t? — so you may need to make a few more decisions about how your table relationships work:

Referential integrity: This nifty feature means you can tell Base not to allow a record to exist in a detail table unless it has a matching record in the master record. For example, if you turn on referential-integrity checking for the relationship between the Customers and States fields, Base won’t allow you to enter a record with a State/Province code if the code doesn’t exist in the State Code field of the States table. It’s a “No bogus codes!” rule, and doesn’t require any programming.
Cascading updates: Another way-cool Base feature updates detail records automatically when you change the matching master record. For example, if you find out that you have the wrong ISBN for a book and you change it in the Product Code field in the Products table, you can configure Base to update the code automagically in the Order Details table
Cascading deletes: As with cascading updates, this feature deletes detail records when you delete the master record. This feature is a bit more dangerous than cascading updates, and you may not want to use cascading deletes for most related tables. If a book goes out of print and you stop carrying it, deleting it from the Products table is a bad idea. Consider: What’s supposed to happen to all those matching records in the Order Details table (assuming that you sold some copies of the book)? Don’t delete the Order Details records —because then it looks like you never sold those books. Instead, mark the book as unavailable (in our example, set the Discontinued field to Yes) and leave the records in the tables.

Now you have a fully relational database design. The last step is to clean up the loose ends.

Cleaning up the design

You have tables, you have fields, and you have relationships. What more could you want in a database design? You’re almost done. Look at each field in each of your tables and decide on the following for each field:

Data types: The section, “Data types,” earlier describes the types of information you can store in Base fields. Decide what kinds of information each field contains, how large your Text fields need to be, and what kinds of numbers your Number fields hold. Make sure to use the same data type and length for related fields. For example, if Product Code is a Text field that is 10 characters long in the Products table, make it the same length in the Order Details table. If you use an AutoNumber field as the primary key in a master table, use a Long Integer Number field for the foreign key in related tables. What is a foreign key? A foreign key is a special kind of field in a relational table. A foreign key matches the primary key column of another table and can be used to cross-reference tables or ensure that the value in a child table has a parent in the parent table.
Validation: You can set up validation rules for Text, Number, and Date/Time fields. Think about limits on the legal values for the field. For example, you may want to specify that the Price field in the Products table can’t be over $200, or that the Publication Year field must be between 1500 and 2100. (This rule should work unless you run the bookstore for Hogwarts Academy.)
Defaults: Some fields have the same value for most records. For example, the Discontinued field in the Products table will be NO for most records. (How often would you type in an item that’s already discontinued?) You can set the default value — the value that the field starts out with — to the most common value; you have to change it only for the records that have a different value.
Indexes: If you plan to sort your table or search for records based on the values in a field, tell Base to maintain an index for the field. Like the index of a book, a database index helps you (or Base) find information; Base stores information about the field to speed up searches. Base automatically indexes primary key fields and foreign key fields, but you can designate additional fields to be indexed.

That’s it! You’re done designing your database!

Tips for Choosing Field Types

Here are some guidelines for choosing field types.

Choosing between Text and Yes/No fields
Fields that can have only two values (such as Yes and No, True and False, or On and Off) are also called Boolean or logical values. You can store Boolean information in a one-letter Text field, using Y and N. But if you use a Yes/No field, Base can display the information on forms as a check box, option button, or toggle button.
Another advantage of going the Yes/No field route is that you can easily switch between displaying the field as Yes and No, True and False, or On and Off by changing the Format property for the field. Using a custom format, you can choose any two text values to display instead of Yes and No. You can display the values Discontinued and Available for a Yes/No field.

Choosing between Text and Memo fields
Text fields are limited to 255 characters — if you need more than that, use a Memo field.

An Base Memo field can contain over 65,000 characters of textual information — but the extra elbow room costs you some versatility. You can’t index Memo fields — and they can’t serve as primary or foreign keys.

If you plan to sort or search your records using the contents of this field —or use the information in it to relate one table to another — a Text field is usually your best bet.
Some database designers avoid the Memo field altogether, because they find that databases with Memo fields are more likely to get corrupted (become unreadable by Base). The same is true of like-OLE Object fields (used for storing pictures, spreadsheets, documents, and other large objects) — your database may get indigestion.

Choosing between Text and Number (or Currency) fields
Base displays and sorts Number and Currency fields differently from Text fields. Here are the differences:

When displaying a Number or Currency field, Base drops any leading zeros (for example, 08540 becomes 8540 or $8,540).
You can format Number and Currency fields in many ways, giving you control over the number of decimal places, specified currency symbols, and the use of commas. Base can vertically align these fields on the decimal points, which makes columns of numbers easier to read.
Base can calculate totals, subtotals, and averages for Number and Currency fields, as well as doing other numeric calculations.
When sorting a Number or Currency field, values sort from smallest to largest (at least they do when you’re sorting in ascending order). But when you sort a Text field, values are sorted alphabetically — starting at the left end of the field. This difference means that in a Text field, Base sorts 55 before 6, because the 5 character comes before the 6 character.

Secret keys The primary key field for a table doesn’t have to be information that the user sees. In fact, many programmers prefer to use a primary key field that has no other use than to uniquely identify records. If you create an AutoNumber field to act as a primary key field, the user of your database never has to see or type the values of this field.

When you sign in to the Amazon Web site to order a book, you never have to type in your customer number. Instead, you sign in with your e-mail address and Amazon looks up your customer number automatically. Similarly, when you order a book or other merchandise, you never have to type the item number. You just find the item you want and click the Add This Item To My Cart button.

Use Number fields for all numbers except numeric codes (such as ZIP codes or phone numbers), which are described in the next section. Store any number you may want to add to a total in a Number or Currency field. Choose a Currency field for money values.

Storing names, money, codes, and other stuff
Now that you know the concepts and procedure for designing a relational database, here are a few suggestions for choosing field types for your information:

People’s names: For lists of people, creating a Name field and putting full names into it is tempting. Don’t do it: You’ll want to sort records by last name, or create listings with last name first, or otherwise fool with the format of people’s names. Create separate First Name and Last Name fields.
Phone numbers and postcodes: Use Text fields rather than Number fields, even if you plan to type only digits into the field. The test to use is this: Is there any chance that you’d ever want to do math with this information? If the answer is no, then use a Text field.
Money: Use a Currency field rather than a Number field. Calculations with Currency fields are faster than those with most Number fields.
Percentages: To store percentages, such as a discount, create a Number field and enter decimal numbers between 0 and 1 (inclusive) for percentages between 0 and 100. When you create the table, you can format the Number field as a percent. Then, if you enter a value and habit makes you type 33%, Base converts the value automatically to 0.33.
Pictures: Base allows you to store pictures in a field — specifically, you use an OLE Object field — but unless the pictures are small, doing so turns out to be a bad idea. The database reacts to a large OLE object like an anaconda trying to swallow a rhino — and its size balloons. If the pictures are small, go ahead and store them in OLE Object fields so everything that makes up the database is in one file. If your pictures are large, if they change frequently, or if you use them for other purposes and need to store them as separate files anyway, store the pathname that leads to the files containing the pictures. In the bookstore example earlier, the Products table includes a Cover Photo field. Instead of making that field into an OLE Object field, you can store all the cover pictures in a separate folder on the hard drive — and store file names for each picture in a Text field. If the pictures are in various folders, store the entire pathname in the field, as in the following example: D:\Bookstore\Database\Products\Iliad.jpg
Calculations: Don’t create a field that stores the results of calculations that use other fields in the same table. Fields should contain only raw data — Base can do the calculations later. The problem with storing calculated values (other than just plain wasting storage space) is that if the numbers on which the calculation was based happen to change, the calculation is then wrong — which fouls up any calculations or reports based on it. In the bookstore database example, in the Order Details table, you may want to add a Total Cost field to contain the Quantity field multiplied by the Price Each field. However, if the customer decides to change the quantity of items purchased, the calculated amount is then wrong. A better approach is to allow Base to do the work at the last minute — Base can multiply, apply discounts, and sum up totals when you display information or print reports.
Codes: Decide on the formats to use for phone numbers, invoice numbers, credit card numbers, purchase order numbers, and other codes. Decide whether to use all capital letters, and whether to include or omit dashes and spaces. If you ask Base to search for someone with a credit card number 9999–8888–7777–6666 and the card number is stored as 9999888877776666, the search won’t find the record.

Storing Single Facts

   Some pieces of information exist all by themselves. They aren’t part of a list —there’s just one item. For example, the name of your organization is a single piece of information, and so is the pathname to the location of your database.
   If you want these pieces of information to appear on any reports, forms, or queries, or used in calculations or importing, typing them willy-nilly into said reports, queries, or other Base objects is tempting — but in practice this turns out to be a lousy idea.
   Here’s the problem: What happens when one of these facts changes? Suppose that your organization’s name or address changes, or you move your database’s location to another folder on another computer. You sure don’t want to have to root around your database looking for the places where such information appears.
   Instead, create a table called Constants or Facts (or any name you like) with just one record in it. Create a field for each piece of information you need to store: maybe your table contains Our Name, Our Address, Our City, Our State, Our ZIP, and Our Phone Number fields. Wherever you want this information to appear (reports, mainly), Base can look it up in your table. Then, if something changes (your telephone area code, most likely), you have to update it in only one place!

Creating a Database

Okay, if you still haven’t created your database, now you’re armed with your database design and you’re ready to start. When you set out to create a new Base database, you have two options:

create it from scratch or
use a template

After you have a beautiful database design (allow us to recommend the —ahem — stellar example in this section), you can start with a blank database and create the tables, fields, and relationships. That means running Base without opening an existing database. Follow these steps:

(Todo... expand....desribe the steps...)

Naming things (for serious database designers) If you create a database that is used with larger database systems — such as those running on SQL Server or Oracle — you may want to use a systematic approach to naming the objects in your database.

First, omit all spaces in your table and field names, because some database systems can’t handle them. Even in Base, avoiding spaces means less typing later on when you create queries, forms, reports, macros, and Basic modules. That’s because you won’t have to enclose your table and field names in square brackets ([ ]).
Also, don’t use words that have specialized meanings to Base, including these words: Name, Date, Word, Value, Table, Field, and Form. You can actually confuse Base. It’s not a pretty sight. If you really want to impress your programming friends, consider using prefixes on all your object names to show what kind of object you’re naming. Here’s a set of commonly used prefixes. For example, a serious programmer might rename the Products table as tblProducts. Fewer programmers use prefixes for fields, to show the data type of each field. If you want to, and if you want to read more about the Reddick VBA Naming Conventions from which these prefixes come, go to the www.xoc.net Web site and click the links for RVBA Conventions.

Tables

Creating and Modifying Tables

Tables are the most basic building block in your database — they hold the data that you need to save and to analyze. Creating tables and entering data may not be the most glamorous thing you do with your database, but having well-designed tables and correctly entered data makes your database as useful as possible.
Before you begin putting data in tables, however, you need to consider the design of your database.

About Table Views

Default's Base opens with Forms View. To display an already existing table, find the Tables heading at the top of the list in the Database Pane, followed in Tables Pane (right down) by the name of all the tables in the database .

Before open a table click once [after have click Documents (default is None) Preview button in right] the name of the table you want to display, and the table appears in a preview pane.
Now double-click (or right-click on table's name ➪ Open or trough the toolbar's table's subsection click the Open button) the name of a (just existing) table you want to display, and the table appears in Datasheet view. Datasheet view is used for entering and viewing data;
The other view probably you are interested is the Design view (right-click on table's name ➪ Edit or trough the toolbar table's subsection click Edit button). Design view is used for refining field definitions and table properties.

Datasheet view
A datasheet shows you data. Datasheet view is similar to a spreadsheet — it displays your data in rows and columns. Rows are the records; columns are the fields. Use a datasheet to view, enter, edit, and delete data. In Datasheet view, you can't create and delete fields neither check spelling (like in Access), but you can sort and filter, find data.
In Base (differently than Access) a Datasheet view exist only after you created its structure through design view.

Design view
In Design view, you don’t see any data; instead, you define and edit field names and specify the type of data each field holds (you construct the table's structure ). You can also provide a field description. Design view also contains field properties — more advanced ways to define fields and help make sure that data entry is accurate.

Saving Your Table

As soon as you enter data, Base saves it after you press Enter. Why do you need to save your tables? That’s easy: In order to save both the structure of the table and its field definitions. What you save when you save a table is:

the table definition, which includes how the table looks in Datasheet view (such as the size and order of the columns) and
the information in Design view (the field names, data types, descriptions, and field properties).

Save a table design by using one of these methods:

Click the Save button on the toolbar
Press Ctrl+S.
Close Design view and click the Yes button when Base asks whether you want to save the table (Better you must save a table before close the entire Base application).

Then, in the Save As dialog box, provide a name that describes the data stored in the datasheet. Chances are you’ll use a table whenever you create other database objects; naming each table descriptively saves you time when you’re looking for the data you need later.

Creating Tables for Your Data

Before you create a table to hold your data, take some time to consider the design of your database — that is, what fields and tables you need — so that your data is well-organized and easy to analyze. After you figure out how to organize your data, you’re ready to sit down with Base and create tables. Maybe you must import data.
You need to create a database to hold your tables. To create a table, first open the database that you want to hold the table. If you have just created a brand-new database, as soon as you have named the database you will see an empty table in Design view to fill in. If you are adding a table to an existing database, using the first columnar group of buttons (the Database group) move (right) on

Create table in design view or
Use wizard to create table or
Create view by specifying existing tables

Notice that you have two options for creating tables from scratch — Wizard
and Table Design. Unfortunately don't exist the Access's Table Templates (Displays a list of table templates to choose from). Instead we have Wizard mode to get help creating a table something similar to Access's Field Templates — predefined fields you can put in any table.

Creating tables using Design view
Design view is a good place to create a table if you know a lot about the type of data you put in the table — and you want the fields you create to be designed for the data you have to put into them. If you’re creating the table but don’t yet have any data, Design view is the perfect place to start. Follow these steps to create a table in design view:

Click Create table in Design view in Base's Tasks pane. Base opens a blank table in Design view. Notice the flashing cursor in the first row of the Field Name column.
Type the name of the first field. Press Tab to move to the Field Type column. Notice that once you type the first letter in field name the field properties for the field fill in automatically to a default value, and the field type is set by default to the Text option.
Select a data type from the Data Type drop-down list. Common choices are Text, Memo, Number, Date/Time, and Currency.
Type a description of the field in the Description column. (This is optional, but we recommend it.) The description can be especially useful if many people use the database, or if you may not use the database for a while. Use the Description column to explain exactly how you intend the field to be used(is the corresponding comment for a variable in programming languages).
Define additional fields in the table by repeating Steps 2 through 4. You can use Tab to move to the next row where you enter the next field name.
Click the Save button or press Ctrl+S to save the table. Type a descriptive name in the Name field and press Enter.
When Access asks whether you want to define a primary key, choose Yes or No.
Don’t worry; whatever you choose, you can change later.If you choose to create a primary key now, Base creates a new, numbered field that gives each record a unique number(AutoValue field, defined as the primary key). If you want to skip this step, be sure you define a primary key manually when you know which field(s) you want to use to uniquely identify each record.

After you have defined the fields in Design view, you have the option of displaying the table in Datasheet view and entering data. However, you also have the option of entering data through a form.

Refining Your Table Using Design View

   Design view is the place to go when you want to be really specific about what you want a field to hold. Design view also provides some tools you use to make sure that the data entered in a field is what you want it to be.
   The top part of the Design View window lists the fields in the table, their data type, and a description, if one has been added.
   The bottom part of the Design View window displays field properties —configuration information about the current field. If you’re a novice Base
user, don’t worry about field properties. You don’t have to do anything with them at all; if you do need them at some point, however, we tell you exactly how to use them.

Choosing field names
   When you create fields, give at least a couple of seconds of thought to the name you give them. Although you can change a field name, thinking of the name as permanent is safer. Pick a name that is descriptive, not too long, and easy to figure out. You often see the name without the description when you are building other objects, so naming fields well now saves you time later.

Some fields are used to connect tables — for instance, in your Holiday Gifts database you may have a person’s name (or some other unique identifier) in the table for listing addresses, as well as the table for listing the gift(s) you give them each year. Try to use the same name for fields that appear in multiple tables when the field is, in fact, the same. If the field is similar but not identical, give it a different name.
Starting every name with a number or a letter, and keeping names to 64 characters or fewer, is a good idea.
If you are even thinking of using your database in a real SQL environment, don’t use spaces in your field names. SQL does not like spaces.

Changing a field name
   In the database-building process, changing field names is easier if you do it sooner rather than later — that is, before you use the field name a zillion times in tables, queries, forms, reports, and in code. Keep a table of old and new names in case any problems crop up.
   You can rename a field in a single table, but if you use the field in other places in the database, be sure to do changes even there.
   Rename : type a new name, and press Enter. In Design view, simply edit the current name.

Copying a field
You can copy a field definition easily — be aware that you only copy the definition, not the data. You can even copy a field definition from one table to another — which is an easy way to be sure that related fields have the same definition. Remember, however, that usually only one field needs the primary
key designation; be sure to remove it from the other field.
To copy a field, follow these steps:

Click the record selector (the gray box to the left of the field name) to select the field.
Press Ctrl+C or click the Copy button in menu bar.
Move the cursor to an empty row in the table into which you want to copy the field.
Press Ctrl+V or click the Paste button in menu bar.
Type a new name in the Field Name field, if necessary, and press Enter. The field title is highlighted, so when you type a new name, you replace the old name.

Moving a field
To move a field (only to last position), select the row by clicking the record selector — you can select multiple rows by Shift + dragging the row selectors. Then CTRL+X (Cut) and then Ctrl+V(past) in the new position(row). Rows below the cursor are pushed up to tap the hole.

Adding a field
If you want to add a field in the middle of a table in Design view, place the cursor only in the last position where the new field is forced to appear

Deleting a field
You can delete a field in Design view. Deleting a field deletes the field definition and all the data stored in the field. Follow these steps to delete a field:

Select the field by clicking the record selector (the gray box to the left of the field name).
Right click + Delete. If you do a mistake and delete the field you can ever press undo button(or Ctrl+Z). Istead after you save the table field's delete becomes permanent.

Choosing a data type
Base provides twenty data types for you to choose from. Choose the data type that best describes the data you want to store in the field and that works with the type of analysis you need to use the field for. For instance, storing phone numbers in a text field works fine because you probably never need to add or subtract numbers. Prices, however, should be stored in a Number or Currency field so you can add, subtract, or even multiply them by the number of units ordered and create an invoice.

A few fields need data type that may not be obvious, mainly telephone numbers and ZIP codes and other such fields. Generally, even though these fields store numbers, you want to set these fields to text data type. Doing so allows you to store leading zeros (so that 021538 doesn’t appear as 21538) and add characters such as dashes and parentheses.

When to use AutoValue fields
AutoNumber(AutoValue) fields have one and only one purpose: to act as the primary key field for tables that don’t have an existing field that uniquely identifies each record. Don’t use AutoNumber fields for anything else. In fact, most Base database designers use AutoNumber fields to create primary key fields — and then make sure those key fields never appear on forms and reports.
Here’s why the key fields are often hidden: You have no control over the numbers that Base issues when numbering your records. If you start adding a record and then cancel it, Base may decide that particular number is already used — and skip it the next time you add a record. You can’t change the AutoNumber field’s value. If you need a series of numbers to not end up with holes (skipped numbers), then don’t use an AutoNumber field. If you use an AutoNumber field to keep track of invoices, and it issues your invoice numbers, you end up with skipped invoice numbers. If this isn’t a problem for you, fine — make the Invoice Number category an AutoNumber field and print it on your invoices. But if missing invoice numbers is a problem, use a regular Number field for your invoice numbers and don’t use the unique AutoNumber field on forms and reports. You may want to use the Index field property setting set to Yes (No Duplicates) if you want to make sure that each value in the field is unique. If you want to start numbering invoices at 1001 rather than 1, create an Invoice Number field.

If you want to get fancy, create a macro that automatically fills in the next invoice number in the sequence. But if an incorrect check number gets entered by mistake, you can go back and make changes without changing the value of the primary key field.

Formatting fields with Field properties

   Field properties are generally used for formatting fields. They can also be used to validate data.
   Field properties are defined for each field (not surprisingly!). You can only see the field properties for one field at a time. To see the field properties for a field, select the field in the top half of the Design View window. You can select the field by clicking the record selector (the gray box to the left of the row) or by clicking anywhere in the row. The selected field has a little green triangle arrow to its left. Select a new field to see a whole different set of field properties. The field properties you see depend on the data type of the field — for instance, you won’t see the Decimal Places property for a Text field.
   Click a field property to see a short description to the right — that tells you if it’s a formatting property or a data-validation property (some properties can be used in both ways).
   How do you use field properties to format a field(press ... button in Format example) ? For number fields, you can define the number of decimal places you want to display, leading zeros, negative numbers in red, thousand separation, etc. For text fields, you can tell Base to change the text alignment. You can even use the Format property to add extra characters to a Text or Memo field.
   You can change numbers to display with a currency symbol, in percentages, or in the comma number format. You can also increase or decrease the number of decimal points displayed using the Increase Decimals and Decrease Decimals buttons.
   The Unique check box when checked requires that every value in the field be unique, that is, not repeated in any other record. The Is Required check box makes the field a required entry, and the user cannot continue to the next record until a value is entered for the field.

Formatting Number and Currency fields
You can use the Format code and Format properties together to define how fields display. The common formats for Number and Currency fields are built right (next Category column) into Base — you can choose from those listed in Table 1-3.

The Format code property can affect the format.
You can define your own number format using the following symbols:

# Displays a value if one is entered for that place
0 Displays a 0 if no value appears in that place
. Displays a decimal point
, Displays a comma
$ (or € etc) Displays the currency symbol
% Displays the number in percent format
E+00 Displays the number in scientific notation

   To create a number format with comma separators and three decimal places,
type the following: ###,##0.000
   You can define a numeric format so the format depends on the value. You can define formats for positive and negative numbers, for zero, and for null values (when no value is entered). To use this feature, enter a four-part format into the Format code property, with the parts separated by commas. The first part is for positive numbers, the second for negatives, the third if the value is 0, and the fourth if the value is null (for example, #, ##0; (#,##0); “—”; “none”). Using this type of format, you can display positive and negative numbers in different colors, if you like, such as positive in green and negative in red. Put the desired color in square brackets in the correct section of the expression. The available colors are Black, Blue, Green, Cyan, Red, Magenta, Yellow, and White. In Forms, conditional formatting is available and you can specify an expression to determine format — for instance, displaying unshipped status in red.
   To store percentages, such as a discount, create a Number field with a Single field size (to keep the size of the field small — see the next section) and enter numbers between 0 and 1 (inclusive) for percentages between 0 and 100. When you create the table, you can format the Number field as a percent. When you enter a value, you type 33% and Base converts the value to 0.33.

Setting the length

Using the length property correctly can keep your database efficient; doing so keeps the field size as small as is practical — making for a smaller, more compact database.

For Text fields, the length property can also help you screen out incorrect data — if you know that you only need (say) four characters in a certain field, then set the field size to 4. Anything longer produces an error message.

Using the length property for any of your Number fields is a little more complicated, but again, using the shortest practical field size makes your database more efficient.

Table 1-4 shows your choices for the field size of a Number field (these are listed from the smallest amount of space required to store each value to the largest).

The default field size for Text fields is 255; for Number fields, it’s Long Integer. You can change the default size on the Base Options dialog box. You can change a field size after you enter data, but if you shrink the size, any Text data longer than the new setting is truncated and any Number data that doesn’t meet the requirements is rounded (if you choose an Integer setting) or converted to a Null setting if the value is too large or small for the new setting.

Formatting Date/Time fields
Base provides the most common formats for dates and times — click Date in the Category field property to see the available formats in the Format field. You can also create your own Date/Time format (for online help, press help button or use the Help button on the toolbar) that provides all the codes you need. Combine them in the same way you combine the text or number codes to define a format.

Formatting Text fields
Use the Format code and Format properties together to format Text fields. The Format code property limits each entry to the number of characters you specify. You can change the field size from a smaller size to a larger size with no problems. If you change a larger size (say, 20) to a smaller size (say, 10), you lose characters past the 10th character. You enter symbols into the Format property in a kind of code:

(todo ....)

Defining the Primary Key

The primary key is a field in each table that uniquely identifies each record in the field. Base needs a Primary key. It is necessary for edit/populate the table in datasheet niew(otherwise you can not). The simplest primary key field is a counter with a value of one for the first record, two for the second record, and so on. You can create a counter field by using an AutoValue field. If you allow Base to create a primary key for you, it creates an AutoNumber field.
Another example of a primary key is a Social Security number in a table where each record contains information about a single person and each person is listed only once in the table. Sometimes each record may be uniquely identified by the combination of two fields, such as an item number and the manufacturer. Note that first names and last names may not always be unique! After you define a field as a primary key, Base prevents you from entering a new record with the same primary key value. When in doubt, an AutoNumber field is a good bet for a primary key, but the AutoNumber field doesn’t allow Base to help you avoid repeating data as another field does. Follow these steps to create a primary key:

Display the table in Design view.
Click in the row containing the primary key field, or select the row by clicking the record selector. To select multiple rows to create a multiple field primary key, click the first record selector, and then Ctrl+click the record selectors you want for any additional fields.
right-click the row selector and select Primary Key. ---Important: If the Primary key must by an AutoNumber value before right-click select Field Type ➪ Integer otherwise AutoValue property is always off--- Base displays the gold key symbol in the record selector for the field. The primary key field has to uniquely identify each record.

Indexing Fields

When you index a field, Base sorts and finds records faster using the Index field. An index can be based on a single field or on multiple fields. The primary key field in a table gets indexed automatically, and you can choose other fields to index as well.

Although indexing speeds up many operations, it slows down some action queries because Base may need to update the indexes as the action is performed.

To index a field,

Tools ➪ Index Design
In Indexes window you can see th primary key just indexed automatically by Base clik on New index button on the up-left give it a name and choose the field(under index field column) you want to index of the Yes values for the field’s Indexed property.
You can set Unique : Indexes the field and doesn’t allow you to input the same value for more than one record. The primary key automatically gets this value. Or leave it unset: Indexes the field, and allows you to input the same
You can set the sort order
Click save current index button

The Indexes window displays all the fields in the table that are indexes, their default sort order (which you can change), and their index properties. The index properties are as follows:

Primary: You see Primary Key (under index field) when the field is the primary key for the table, the name of the field otherwise.
Unique: i set when the value of the field for each must be unique, unset otherwise.

Printing Table Designs

Printing the Design view of your table is not as easy as clicking the Print button — as you may have noticed already, the Print button is not available when Design view is displayed. Luckily, you can workaround by do sreenshots and next printing them. Unfortunately actually don't exist a tool like Access's Database Documenter

Entering and Editing Data in Datasheets

All the data in your database is stored in tables, probably in more than one of them. Tables have two views: Design view and Datasheet view, where you see the data in the table.
Although you can work in Base without ever looking at a boring datasheet, you should know your way around one, just in case you need to look at one. Not only do datasheets provide an unadorned view (like a form do) of the data in your table, you can do quite a lot of work in a datasheet. If you enter a lot of data, you
may find that you like entering it into a datasheet rather than a form. Using a datasheet in Base, you can:

modify and delete data
change the look of the datasheet by moving columns around
change column width and row height to fit your data

    Datasheets are one way to view tables. You can also view queries with datasheets — you see the result of a query in a datasheet. Everything you can do with a table you can also do with a query in Datasheet view.
   You can easily view the contents of a table, add new records to the table, and make changes to the data using Datasheet view — the view that shows data exactly as it is stored: in rows (records) and columns (fields). Opening a table in Datasheet view is easy. Just double-click the table’s name in the Tables Pane.

Looking at a Datasheet

A datasheet displays data in a table — it has rows (records), columns (fields), and cells that hold individual pieces of data.
   The important buttons in Datasheet view live on toolbar, shorkeys, mouse (contestual right-click): to cut and paste, change the appearance of data (font, color, justification, and so on.), filter the data displayed, and find the specific data you’re looking for.

Respectively to Access Base lack tools for manipulating the database through the datasheet — use it to add and rename fields, change the data type and format of a field, and view relationships and object dependencies, spell cheching. If there is something you want to do to the datasheet that you might otherwise do in Design view, these tool for doing it is probably on the Datasheet tab.

Navigating the Data

Moving around in a datasheet is pretty straightforward.

Use the vertical scrollbar or the Page Up and Page Down keys to move quickly up and down the datasheet (from record to record).
Use the horizontal scrollbar to move from left to right, and
press Enter or Tab to move the cursor from field to field.
If you know the number of the record you want (for example, the fourth record in the table), type the record number into the Record box at the bottom of the datasheet to jump straight to the fourth record. Record numbers are relative — records are not assigned a permanent record number. But when you want to go to the fourth record listed on the page, type 4 in the Record box and then press Enter.
Sorting the datasheet so that records appear in a different order means that the record that is fourth changes.

You can move around in a datasheet three different ways:

Mouse: Click a cell or use the scrollbars.
Keys: Use Page Up and Page Down and the other keys in Table 2-1.
Buttons: Click the VCR-like record-navigation buttons at the bottom-left of the datasheet (refer to Figure 2-1), or you can click the New Record button(yellow light) in the toolbar to jump to the end of your listings.

Adding and Editing Records

To create a new record, start typing in a blank row.
To move to a blank row, click theNew Record button —nestled with the record navigation buttons at the bottom-left of the datasheet. Type your data and press Enter or Tab to move to the next field. When you get to the last field of a record and press Tab or Enter, Base automatically moves you to the first field of a new record.
As you enter data, you may come across fields that are check boxes or drop-down lists. You can easily use the mouse to change a check-box setting or select from a list, but you can also use the keyboard — in these ways, for example: Press the spacebar to change a check-box setting from checked to unchecked or Press F4 to see a drop-down list, press the ↓ key to select your choice, and then press Enter.
If you change your mind about your entry, press the Esc key to cancel it. If you already pressed Enter, you can undo the last entry by clicking the Undo button (a button on the toolbar or Ctrl+z shortkey) Table 2-2 lists all the keystrokes you ever want to use as you enter and edit data(Substitute Ctrl+ - with Ctrl + spacebar).

Editing the data you have
Editing is pretty straightforward. To edit data, simply place your cursor in the cell containing the data you want to change, use the Ctrl+Backspace or Delete

keys to get rid of unwanted stuff and then type in your replacement stuff. Or, if you already selected (highlighted) the text or a value, whatever you type replaces the selection. If you don’t want to replace a selection, press → or click in the cell to deselect and display the cursor.

Use these tricks when selecting text:

Double-click to select a word or value.
Click at the beginning of what you want to select, press the Shift key, and then click at the end(or press →) of what you want to select.

You can make any changes, and then press Enter or click OK to return to the datasheet. Table 2-4 lists keystrokes you can use while in editing mode.

Base lacks Access's features like Hyperlinks and Attachment data types and Spelling checking

Deleting records
It’s inevitable that sometimes you want to delete data. Before you do that, however, here’s a word to the wise . . . Deleted data cannot be recovered using the Undo button! With that caveat firmly in mind, you can delete a record by following these steps:

Select the record you want to delete by clicking the record selector to the left of it (or by putting the cursor anywhere in the record). Remember that a record is a whole row of data.
Press the Delete key or click the Delete Record button. Base asks you if you are sure you want to delete the data.
Click the Yes button to delete.

The row you select is deleted, and the data below the deleted row moves up to fill the space.

Entering special characters
Occasionally, you may need to enter characters that aren’t on your keyboard. Base provide an easy way to do that right-clik ➪ Special Character (or Ctrl+Shift+S). Otherwise, follow these steps:

Choose Start➪All Programs➪Accessories➪System Tools➪Character Map. The Character Map appears. You see a grid of characters. The drop-down list at the top of the box lists the fonts. The box at the top is where the characters you select (in Step 3) appear.
Browse to find the character you need. Each font has a different set of characters, so you may need to browse through the fonts to find the character you want. Use the vertical scroll bar to see all the characters within a font.
Double-click the character or select it and click the Select button to display it in the Characters to Copy box. Repeat Step 3 until you have all the characters you need.
Click the Copy button. The contents of the Characters to Copy box copy to the Windows Clipboard.
Return to Base and click the Paste button or press Ctrl+V. If you don’t see the character you copied, you may have to format it with the font you selected in Character Map.

Formatting a Datasheet

Datasheets can’t provide the good-looking output you get with a report or a form, but you can make some changes to make a datasheet more readable and attractive. The formatting options are available on the record selector: right-click ➪ table format (character dialog box appears)
Format changes usually cannot be undone using the Undo button or Ctrl+Z. You can undo changes by closing the table without saving, but of course you lose all the formatting and design changes you made since the last time you saved the table.

Formatting a field
Field formats are covered in detail in the previous section, but notice that you (differently than Access) can't format fields from the datasheet — you must be in Design view.

Changing the font
In an Base datasheet, the font and font size of all the data are the same —you can’t change the font for just some of the data. Change the font by using the row selector ➪ right-click ➪ Table format. Appears Character dialog box and 2 tabs within it Font and Font effects.
Select Font tab to change the font, font style, and font size. Instead select Font effects to underline text by using the Underline buton in the Effects box. The Color option changes the color of the data in the datasheet.

In Base you can't take advantage of rich text like in memo's data type fields in Access

Changing row height
   You change the row height in one of two ways — with the mouse or with the
Row Height dialog box. You only have to change the height of one row — all the rest change to match. All the rows change to the same height; you can’t just change one.
   Changing row height with the mouse is very similar to changing column width: Move the mouse pointer to the record selectors until the pointer turns into a double-headed arrow (shown in the margin). Then drag up (to make the row shorter) or down (to make the row taller).
   Alternatively, right-click a record selector and select the Row Height option from the shortcut menu to display the Row Height dialog box. Enter the row height in points (there are 72 points in an inch). The Standard Height check box formats the row height at the standard height for the font size that you have chosen (the point size of the font, plus a cushion for the top and the bottom of the row).

Inserting and deleting columns
Remember, columns are fields, so when you insert a column you are adding a new field, and when you delete a column you delete the field and all its data. You can add and remove fields in Design view — that’s covered earlier. As we have said in Base (differently than in Access) you can't modify the table's structure in Datasheet view you must go necessarily in Design view. So you can't insert, delete a field (column head) in Datasheet view.

Hiding columns
If you want to hide a column in a datasheet (perhaps the data is sensitive), select the column or columns, right-click the selected field name(s), and choose Hide Columns. To display hidden columns, right-click any field name and choose Show Columns. A sublist appears, where you can choose which columns to redisplay ALL or fields that has been hidden.

Actually Base lack Access's Freezing columns feature: When you’re working with a wide datasheet, you may want to freeze one or more columns so they don’t scroll off the left side of your screen.

Sorting, Finding, and Filtering Data

A datasheet is a good place to start analyzing your data, especially if you only need to look at the data in one table. Within a datasheet, you can sort (alphabetize) — using any field and filter — to find records that are alike or that meet simple criteria. And if you’re looking at a datasheet generated by a query, these datasheet tools may be just what you need to find the data you want without redefining the query.

Sorting the Rows of a Datasheet

   You may enter data randomly, but it doesn’t have to stay that way. Use the Sort buttons to sort the records (rows) into an order that makes sense. Before you sort, decide which field you want to sort, and then place your cursor somewhere in that field. Then use one of the two Sort buttons on the toolbarto sort the datasheet.
   When you add a new record to a sorted datasheet, the datasheet does not automatically resort itself — you have to use the sort button again to sort the new records in with the already existing records.
If you have an order that you want to be able to return to (for instance, the order in which the records were entered), it’s a good idea to have a field that you can sort on when you want to re-create that order.
   An AutoNumber field often serves that purpose, but if that won’t work for the order you want, consider adding a field that you can sort on to get your data in order. Don’t let the order of records be a hidden clue to your data —include that information explicitly in a field.

Sort-order oddities
When sorting a Number or Currency field, values sort from smallest to largest (at least, they do when you are sorting in ascending order). But when you sort a Text field, values are sorted alphabetically, starting at the left end of the field. This difference between the two fields means that in a Text field, Access sorts 55 before 6, because the 5 character comes before the 6 character. For example, Base sorts the same list of numbers in Number and Text fields like this:

Number Sort                  Text Sort
1                                         1
2                                         11
5                                         2
11                                       21
21                                       44
44                                       5
If you need to sort the numbers in a text field into numerical order, Base .... Todo
Sometimes you need to know exactly how Base sorts blanks and special characters. The sort order, in ascending order, looks like this:
blanks (null)
space
special characters such as !, “, #, %, &, (, comma, period, [, ^, `, ~ (in that order, incidentally)
letters (Base does not distinguish between uppercase and lowercase letters when sorting) numbers

If you need to know how Access sorts some characters that aren’t listed here, make a test into numerical order, Access online help has table with the characters you need to sort, and sort them!

When you sort a Number field in ascending order, Base lists records from the smallest number to the largest. When you sort a Text field in ascending order, records are alphabetized from A to Z. When you sort a Date field in ascending order, records are listed from oldest date to most recent date. Descending order is the opposite in all three cases: largest-to-smallest number, Z to A, or most recent to oldest date.

Finding Data

   Do you like the quick-and-dirty approach, or are you more thoughtful and refined? Base accommodates both personalities. To search quickly for data in a datasheet, use the Search box at the bottom of the datasheet; Type your search text into the box and with no further ado, Base takes you to the first instance of the text. You don’t even have to press Enter. In fact, as you type, Base is moving the focus in the datasheet to the first instance of the text you’re typing. To find the next instance, press Enter. Continue at will!
   If you want to be more specific about what you’re looking for, you may prefer the Find dialog box( Ctrl+F ). Base even takes the text you typed in the Search box and puts it into the Find and Replace box automatically, so you have it as a starting point. If you want to look within a single field, put the cursor anywhere in that field’s column before you begin the search.
Using the Find dialog box for quick-and-dirty searches is as easy:

Press Ctrl+F to display the Find dialog box.
Type what you’re looking for in the Search for box.
Press Enter or click the Find Next button.

Base highlights the first instance of the Search for text. Quick-and-dirty may work just fine for you, but you need to know about a few refinements to the Find dialog box — such as telling Base to limit its search to particular places. The default settings on the Find dialog box tell Base to search the field the cursor is in, and to match your search term word for word. You may find, however, that other options in the dialog box make it easier to find exactly what you’re looking for. Keep reading to find out more!

The Find and Replace dialog box and its options
If you don’t know how to use the options in the Find dialog box, it won’t help you much with finding what you’re looking for. So a guided tour is in order.
The Find dialog box has the following options:

Search for: Here’s where you type in the text or value that you’re looking for.
Where to search: Here’s where you tell Base where to look — a field, or the whole table.
Settings: Choose how the search results match the Search for text. You can choose from the following options: Any Part of Field, Whole Field, or Start of Field, End of Field. The Any Part of Field option finds the most instances. If you search for Flamingo using the Any Part of Field option, Base finds Lawn Flamingo. The Whole Field option only finds cells that match the whole word, Flamingo — it does not find Lawn Flamingo. The Start of Field option finds cells that begin with Flamingo, such as Flamingos.
Find Next: Enter to Finds the next instance of the Find What text.
Match Case: Match the case of the text — if you want to find THIS but not This, use the Match Case option.
Search Fields as Formatted: Finds data according to how it looks, rather than how it was entered. If you use an input mask on a telephone number field (for example), you may input ten digits one after another, but they appear with parentheses around the area code and a hyphen after the exchange. If you use the Search Fields as Formatted option, you can search for (508) to find phone numbers in the 508 area code.

The broadest search uses the following options: Where to search (the whole table), Match Any Part of Field, Search All, and deselect Match Case. Other choices in the Look In, Match, and Search options narrow the search — and may miss particular instances of the Where to search text. That’s not necessarily a bad thing, by the way — especially if you have a very clear idea of where you want to find what you’re looking for.

Filtering using criteria on multiple fields
   When you have criteria for multiple fields, you can simply apply the filters to the various fields using the techniques you’ve already learned, and Base will display only the records that meet all the criteria.
   Another choice, which is more flexible, is to use the Filter/Sort button feature
to find the records you need. Base displays a dialog box to specify the criteria you want to use to filter your data.
   When you filter by Filter/Sort button, you not only get to use multiple criteria, you also get to choose how the data filters through whatever multiple criteria you set up. Do you want a record to meet all the criteria before it shows up on-screen? Or is just meeting one criterion enough to display the record on the filtered datasheet? The following two operators are what you use to tell your criteria how they should act together:
✦ And: The criteria act together hand in glove — a record has to pass all
     criteria in order to display on the filtered datasheet.
✦ Or: A record has to pass only one criterion in order for it to display on
     the filtered datasheet.

➪

Resources

Start your travel with OOo's Base webpage
Continue with Base's Wiki
Official OOo Base Extention Repository
HSQLDB engine: Features, Documentation, Software using HSQLDB
OOo API (allows to program the office in different programming languages (e.g. C++, Java, Python, CLI, StarBasic, JavaScript, OLE) by using the OpenOffice.org Software Development Kit (SDK). It allows to use OpenOffice.org as service provider in other applications, extend it with new functionality or simply customize and control OpenOffice.org)
Microsoft Office Access 2007 All-in-One Desk Reference For Dummies Wiley Publishing(2007) by Alan Simpson, Margaret Levine Young, Alison Barrows, April Wells, Jim McCarter ISBN-13: 978-0-470-03649-5

CSE, Software Development

Useful Free Resources

Monday, February 15, 2010

Base --- OOo desktop database application

Intro

Tables

Sorting, Finding, and Filtering Data

Resources

No comments:

Post a Comment