To create a custom Crystal report, it is often necessary to create more than one query to export all of the required data. Some information you need may exist only in a vendor query and other information may exist only in an invoice query. To ensure the report's consistency, include data fields in both queries to provide all the data required for the report. Also, include fields that are common to each query so you can link the two, or more, databases created by the queries after the export.
To be as efficient as possible, databases should not have redundant fields. However, it is often necessary to export duplicate fields when information from two or more source queries is needed to create a custom report. To get all the information you need from a query and eliminate redundancy, linking is essential. Linking data fields makes the data in one table (exported query) accessible to data in another.
Step 1: Create the first query. Select the field you will use to link to the second query. Select a field that uniquely identifies a record, such as Project ID or Vendor ID. Right-click on the field and select Change.
In the screen that appears, mark the Index this field when exporting to MDB format checkbox. In this query, the Vendor ID field was indexed.
Step 2: Select File, Export from the menu bar to export the query. Select the Crystal Reports Database file (MDB) format and specify the path to which you want to save the file. Click OK.
Step 3: Create your second query in the same manner, and index the Vendor ID field. The vendor's bank name and routing number were not available fields in the invoice query so this second query was necessary to get all the information you need for the report.
Export the second query in the same manner as the first.
Step 4: Link the queries in Crystal Reports. In Crystal Reports, click Custom to select Custom Report and Data File on the Report Gallery screen. On the Choose Database File screen, double-click on the vendor.mdb file exported from the query. The fields exported from the query are now available for use in building your report.
Select Database, Add Database to Report from the menu bar.
Double-click on the second vendor query exported. The Visual Linking Expert appears. All fields required for the report are now available for use in Crystal Reports, without redundancy.
You can configure Crystal Reports to use "Smart Linking" to link two tables automatically. However, this utility is based upon a number of assumptions so be cautious if you use it. To link two tables manually, click on the field in the first table and drag the cursor to the applicable field in the second table. The link appears between the two tables.
If you need still more information for a complex report, you can create as many databases as necessary, but only one field/table from each database/query/export file can be linked to another specific database.