Can you "add" values from two tables into one field?

[susiesunshine]

I have separate tables for archived records and active records. They have the same fields. For example, I want the case id fields combined into one case id field. Is there a way for Crystal to read archive case id and active case id as one field, case id?

 

[dgillz]

This is called concatenation. Write a formula like the following:

{table1.Field1}+{table2.Field1} Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[rogar]

Create one report on your active records table and an identical report on the archived data table. Go to Database/Show SQL Query in the second report and copy the entire SQL statement to the clipboard. Then go to the Database/Show SQL Query in the first report. Type "UNION" after the last line of the Query and paste the second report query below that. If the two queries are identical in layout and field names, CR should accept and run the UNION query. The data will be combined into one data set which you can group snd sort as if it was from one table.

 

[susiesunshine]

I think my wording of "add" is misleading. I want the report to treat cases from the first database and the second database as if they were coming from the same database. Records will be different in each database, but they both have an arrival and resolved time. Can I say resolved time minus arrival time is open days and have it look at records from both databases?

 

[rogar]

If you want the data from the two tables in separate rows in the report data set, use the UNION method I described previously. If you want data for the same CaseID from both tables to be in the same row in the report data set, you can join the two tables on {CaseID} in the Visual Linking Expert. I am not clear about what you are trying to do. Are the arrival and resolved times in the same record or in different records?

 

[susiesunshine]

Rogar, the times are in different records.

 

[rogar]

If there are two records for a CaseID or if the Arrival is always the earliest and Resolved is always the latest time, use the UNION method, group by CaseID, and in the group footer, calculate {OpenDays} = Maximum({Time}) - Minimum({Time})