| Every data source has one or more tables. It's just a way of dividing and organizing data. About Tables Sometimes you might just have one data source with all the data together. If you're just keeping your mailing list in one data source and you only send a letter once a month to everyone on that list, you don't need to go any further. However, what if you realize after a while that you only want to send your holiday newsletter to friends and family, and your daily "I'm still unemployed, is anyone hiring?" letters to professional acquaintances , and your triweekly amusing short stories to the other members of the Fertile, Minnesota Writers' Club? Then it's time for tables or queries. About tables When you do anything with a data source, you have to pick the table or query to use; if you didn't make any tables, your data source is automatically given a table that just contains everything in the data source. You can only use one table or query; you can't select both the jobsearch table and the stories table from the peopleIsendlettersto data source. You can use tables or queries Queries and tables are interchangeable for the purposes of using their data in documents. Tables Tables are just subdivisions of the data source. -
When you create a data source based on text files, everything in the directory you specify becomes a table. So if you set up the Contacts data source and point it at your bulging C:\mycontacts directory where the separate lists of people you send letters to are, you get three tables: holiday , jobsearch , and stories . -
When you create a data source based on a spreadsheet, every sheet in the spreadsheet is a table. -
For all the other databases, it depends on the database structure you set up. So now, consider whether you'll need to structure your text files, spreadsheets, or other data differently, for the tables you might need, and subdivide and organize your data sources as necessary. Queries Queries are subsections of your data too, but they're created differently. You create a query for a particular table. -
"Give me the phone numbers of all the people in the hiring table of the Contacts data source who live in Colorado and whose emails end in @ enron.com " would be a good query. Assuming that you have fields in your hiring table for phone numbers, state, and emails. And assuming that you're not bothering to ask them for a job. -
You set up queries either using SQL to write it out, or using the Query window. To create queries, see Creating Queries on page 914. Creating New Tables for Spreadsheet and Text File Data Sources You can add tables to text file and spreadsheet data sources just by creating more files or sheets.  Then open the data source definition window again for the data source and be sure in the Table tab that every table is selected that you want available for use. Creating New Tables and Fields for Any Data Source Except Text Files and Spreadsheets -
Choose View > Data Sources. -
Select the data source you want to add a table to and expand it so the Tables category shows. -
Right-click on Tables and choose New Table Design, as shown at right.  -
The Table Design window will appear. This is where you design the table as well as each field. In the following examples starting with Figure 35-42, the example of the existing StarOffice bibliography data source is used. A new table is added to it to track information about promoting books of authors in the bibliography. Figure 35-42. A new table for bibliography  -
The Table Design window shown in Figure 35-42 is fairly intuitive. Briefly, just enter the names of the fields you want in the table (there's a limit on the number of characters in the field name ), select the type of data, and add comments if you want. In the lower area, specify the field length, the default value if any, and format if any. For some fields like decimals, you'll have more options. Table 35-2 describes the field types you can select. Table 35-2. Field types | Field type | Length limit | Description | | Yes/No (BOOL) | 1 | Stores a value of 0 (no) or 1 (yes). | | Memo (LONG VARCHAR) | 65,535 | Use this for long text fields like comments. | | Text (fix) (CHAR) | 254 | Use this for shorter text fields. The entire 254 characters are stored regardless of whether you've put data in them. | | Decimal (DECIMAL) | 20 | Use this for whole or partial numbers; anything you'll need to express a decimal value for. You can specify the number of figures to the right of the decimal points, such as two for storing dollars. The numbers to the right of the decimal point are included in the total of 20 you're allowed. Format it by clicking the browse icon and selecting the format in the windows in Figure 35-44. | | Text (VARCHAR) | 254 | Use this for any text field like a name, book title, etc. | | Date (DATE) | N/A | Format the date by clicking the browse icon and selecting the format in the window in Figure 35-43. | Figure 35-44. Formatting the new field (other styles)  Figure 35-43. Formatting the new field (dates)  -
Use the windows in Figure 35-43 and Figure 35-44 for guidance in creating the fields. Figure 35-43 shows the Field Format window where you can specify how each date field is formatted. Open it by clicking the browse icon next to the Format field in the Table Design window. Figure 35-44 shows the Field Format window where you can specify how each non-date field is formatted. Open it by clicking the browse icon next to the Format field in the Table Design window. -
You'll need a key for the table. Click the Index Design icon on the object bar and make the appropriate entries in the Index Design window.  Figure 35-45 shows the Index Design window. Set up the index field or fields, then click OK. Figure 35-45. Index design field  -
Click the Save icon when you're done and name the table in the window that appears.   Renaming Just right-click on a table and choose Rename, then type the new name.  Be sure not to violate the naming requirements of your database. Copying Tables to Another Data Source You can copy tables between data sources of the same type. Just right-click on the table and choose Copy Table, then go to the other data source, right-click on the Tables heading of that data source, and choose Paste Table. In the Copy Table windows that appear, shown in Figure 35-46, select the appropriate options. Click Next to select the fields to include, then click Next again to specify the field options for each field. Click Create to finish pasting the table. Figure 35-46. Specifying how to paste a table into another data source  Modifying a Table or Field -
Choose View > Data Sources. -
Select the data source you want to add a table to and expand it so the Tables category shows. -
Right-click on Tables and choose New Table Design, as shown at right.  -
In the table design window, make the necessary changes. Refer to Creating New Tables and Fields for Any Data Source Except Text Files and Spreadsheets on page 907 for more information. -
Then click the Save icon on the object bar to save changes. Using the Execute SQL Statement Window In the data source viewer window, right click on any table and choose SQL. The Execute SQL Statement will appear.  This window lets you use SQL for data source administration. The command entered does not lead to the display of filtered database content. It's only for commands like Grant, Create Table, Drop Table, and so on. The data source must support these SQL commandsdBase, for example, cannot execute all the commands mentioned here for SQL. The status window indicates if the command has been successfully executed. To use SQL in a query, right-click on the Query option below the data source, and choose the New SQL Command option. |
