Print Page | Close Window

Join or Combine two tables

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10148
Printed Date: 31 Mar 2025 at 12:35am


Topic: Join or Combine two tables
Posted By: ashline
Subject: Join or Combine two tables
Date Posted: 31 May 2010 at 11:05am

I am guessing that this is a simple question but I am new at CR and haven’t been able to figure it out.

 

I have a Database that has two separate tables that have almost identical fields of data.

 

Table1                          Table2

Incident #                    Work Order #

Assigned To                 Assigned To

Incident Type               Work Order

Status                          Status

Open Date                    Open Date

Closed Date                 Closed Date

 

 

I would like to combine the data in a report so I can display both incidents and work orders together.  The good news is that the incidents and work orders use different number schemes so having an incident and work order with the same number won’t happen.

 

Any point in the right direction would be great!

 

 


Replies:
Posted By: lockwelle
Date Posted: 01 Jun 2010 at 3:24am
Crystal is going to want to know how the 2 tables relate to each other.  If there is no relation, and the report is structured as all Incidents then all Work Orders, you could just create a subreport, but if you want to see if a workorder has been created for an incident, then you would need to join the tables somehow, and depending on what the requirements of the report are...All incidents and any work orders related them, all work orders and the incidents related to them, all work order/incidents that have a incident/work order will determine if you need to change the link from the default of inner join to left or right outer join.
 
HTH

Posted By: ashline
Date Posted: 01 Jun 2010 at 6:47am

The two tables really don’t relate to each other.  My challenge is we have departments that work on items in the incident table and the work order table.  I am trying to create a view or a report that list “all their work” which would be items from both tables.  If I understand sub reports correctly they are just like hyperlinks to another report correct? 

I was hoping I would be able to combine the two tables in one report but I may not be able to do that.


Thanks for the help

Posted By: lockwelle
Date Posted: 02 Jun 2010 at 3:18am
well, if you are looking at what items the department has worked on, then there should be a table or something that denotes who worked on what...that can be you relation.
 
subreports can be hyperlinks, but usually they just 'appear' as part of the report.  A subreport is basically a report inside of another report and is usually linked in some way...it might be as loose as a date range to show work that occurred inside of the given range.  They don't have to use the same fields or layouts.  They can impact performance as each line that subreport is on will hit the database again. The standard example is if you 100 lines of detail, and each line has a subreport, the report will hit the database 101 times, but in this case it would appear to be twice, which isn't too bad.
 
The assigned to: value can be the link  as long as the values are consistent across the tables.
 
HTH


Print Page | Close Window