Joins on multiple (similar) tables

[T3leTech]

Using CR 8.0.

I am trying to create a report that includes connections to three tables that are essentially identical (there is one field that is different, but all other fields are the same in all three tables). However, I cannot figure out how to "join" the three tables in a report. What I'm trying to do is find all instances of a field name that exists in the three tables based on date/time criteria. I'm really not even sure what the SQL query would be, which isn't helping either.

Because I'm not sure where to start, I don't know what information to make available. Maybe just starting with how to setup a report with multiple tables would be a good way to go.

Thanks for any help!

--Telephony Tech

 

[synapsevampire]

Crystal uses a single data source per report.

The option might be to use a Union Query, but since you simply spoke of theory rather than posting example data and expected output, we can only guess what is meant by essentially identical.

To create a Union query, place one table in the report and copy the SQL from the Database->Show SQL Query.

Type the word Union

Paste in the SQL

Change the table names to the next table

Again type the word Union

Paste in the SQL

Change the table names to the next table

Now you have all 3 tables combined into one data source.

Try reading up on Unions. I just answered another question about these.

If this doesn't help, rather than trying to use text to describe data, post example data and expected output.

Environment info generally helps as well:

Crystal Version
Database/connectivity used

-k

 

[T3leTech]

The Crystal version is posted at the top of my original question. DB connection is ODBC.

The three tables are identical in structure. The only difference is the name of the tables, and the information stored in one field. The three tables are:
A_CallHistory, B_CallHistory and C_CallHistory. The field that has different information in it, depending on which table it is stored in is: I3_RowID.
In table A_CallHistory, the values in I3_RowID are like the following: '12345676_A'.
In table B_CallHistory, the values in I3_RowID are like the following: '1234567_B'.
In table C_CallHistory, the values in I3_RowID are like the following: '1234567_C'.
All other fields store the same information type. Here are the rest of the fields:
WorkflowID
CampaignID
SiteID
CallDate
CallTime
Reason
FinishCode
Length
Agent
CallID
PhoneNumber
CallIDKey
The field that I want to "join" on is Reason. Sample data from this field includes the following: "Busy", "No Answer", "Answering Machine", "Contact", etc. All three tables store Reason values from the same list (i.e., table A_CallHistory will not have a Reason value that the other two tables do not use).

So, an example of what I'm trying to do is collect the number of "Busy" calls from all three tables with date criteria. Additionally, I want to Group by the Reason value in the table(s) in the Crystal Report.



--Telephony Tech

 

[T3leTech]

Anybody have ideas?

--Telephony Tech

 

[synapsevampire]

Did you try the Union query as I had suggested, or?

This is database dependent, ODBC isn't a database, it's just the type of connectivity.

-k

 

[T3leTech]

I would need the syntax of the union query before I could try it. Can you provide an example?

--Telephony Tech

 

[T3leTech]

Synapsevampire,

You were absolutely correct! I figured out how to use a Union query to create a view of the three tables, and the reports have never been so easy.

Thanks for your help!

--Telephony Tech