Running Reports from or merging 2 SQL tables

[rradelet]

I am working with Crystal 8.5 & 9.0 on an SQL 2000 database.
There is an "Orders" table and a "PurgedOrders" table. The fields in each table are identical (except that the Purged table has a "PurgeDate" field added.

I want to be able to run reports from the combined data in these two tables. Is there a way to merge or link them within Crystal?

They are both fairly large tables with 163 fields and currently contain over 500,000 records combined.

 

[synapsevampire]

Merge and link are very different things.

Please post example data and expected output.

The methods available differ between CR 8.5 and 9, so specifics might also prove helpful. Generally you're better served to use the more recent version.

You can add both tables to a report and join them (link), or you can create a Union to merge the data into a single recordset.

You've given no indication of what the requirements are, so rather than trying to describe using text, please post an example of the data you have and what you need.

-k

 

[rradelet]

Using Crystal 9.0

A simple Sales by Client report would be a good example.

From the Orders table:

Orders.ClientNo
Orders.OrderAmt
Orders.OrderDate

Select the Orders with Jan 1/04 to Jun 30/04 as the date parameters.

Group by Orders.Client.No and summarize on Orders.OrderAmt.

I have the same from the Purged table, but with different date parameters.

From the "Purged" Orders table:

PurgedOrders.ClientNo
PurgedOrders.OrderAmt
PurgedOrders.OrderDate

Select the Orders with Jul 1/03 to Dec 31/04 as the date parameters.

Group by PurgedOrders.Client.No and summarize on PurgedOrders.OrderAmt.

I would like to be able to run just one report so that I can
retrieve the Sales by Customer for any date range, regardless of whether the orders are in the live Orders table or the Purged Orders table.

 

[synapsevampire]

Consider creating a Add Command (or use a View in the database) to create a Union Query of the data:

select
Orders.ClientNo,
Orders.OrderAmt,
Orders.OrderDate,
'CurrOrders" as OrderType
from orders
union all
select
Orders.ClientNo,
Orders.OrderAmt,
Orders.OrderDate,
'PurgOrders" as OrderType
from
PurOrders

Now you might use parameters against this data based on which OrderType and the appropriate date field.

-k