Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal Report...

Posted on 2000-04-09
16
Medium Priority
?
328 Views
Last Modified: 2012-08-14
I want to retrieve data from more than one table in crystal report.How can i do that ?Both table related to each other through "Id_no".

I am eagerly waiting for your reply.Please soon reply me soon.

Thanks,
bhavesh
0
Comment
Question by:bhavesh55
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 1

Expert Comment

by:ndb
ID: 2698367
Via the Visual Linking expert.
Just drag the Id_no field from the first table to the other.
0
 

Author Comment

by:bhavesh55
ID: 2698409
ndb,
Both of this tables are already linked through "Id_no" field.I mean it already shows linking between them when i try to look from the visual linking expert.

But i have a problem.Like i have 2 tables:
1) Subcase
2) Contact

I put some of the fields from only "subcase" table than it looks ok.I mean it retrieves appropriate values.

But than after i try to put "contact" table's field in design mode and try to take a look for "preview" of that report than it doesn't show me anything.

So please let me know if you have any idea.

I am eagerly waiting for your reply.
Thanks,
bhavesh
0
 
LVL 4

Expert Comment

by:wileecoy
ID: 2698417
bhavesh55,

Are you using the Select Expert? Or does your report have to meet certain criteria?

If so, make sure that the criteria can be met.  It is easy to set up the criteria so that no records match - and thus, your report is blank.

If you do have criteria, try leaving fields from both tables and then removing the criteria to see if you get all the records.  Then, as they work, add back the criteria one-by-one.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:ndb
ID: 2698436
Might sound stupid but.
You should change the direction of the arrow in CR linking expert.
0
 

Expert Comment

by:AshokKumar
ID: 2699037
Go to the visual linking expert. U'll get the view of tow tables. Just Click or the Id_No field of Subcase table and drag and drop at Id_No field of Contact table. A link will be created and then, right click on the link. U'll get some options like left outer join, right outer join, equal join etc. Based upon your requirement choose the option. In your case i think u have to click on the Equal Join option and then clikc OK.

Now REFRESH the report. It should do the trick.

Get back to me
0
 

Accepted Solution

by:
scrumper64 earned 200 total points
ID: 2700339
I've had trouble with Crystal Reports where if I create a report with many tables and one of those tables is not on the report, the table is lost from the SQL.
The problem can be fixed by editing the SQL directly in Crystal (i.e. not using the Visual Linker.
To avoid this: My advice is to make sure a field from each table is put on the report when you create it.  Any fields you don't want can be made invisible.
When I write Crystal Reports I generally use Stored Procedures, there are no problems them.

** BEER, SEX AND BRISTOL CITY **
0
 

Author Comment

by:bhavesh55
ID: 2700624
Ashokkumar and other Experts,
I still have the same problem.

In Visual linking expert,it shows me link between 2 tables "subcase" and "contact" through field "objid".

When i insert "Database Fields" from "subcase" table than it shows me all records in the preview of that report perfectly.

But if i insert any field of "contact" table than after i try to show preview of that report and it doesn't show me anything.

Let me know how do i solve this ?

One more thing is that how do i use relationship in crystal.Like there is "x" relationship name between "subcase and case" table and there is "y" relationship name between "case and contact" table.so how do i reach from "subcase to contact" through that relationship name in crystal.

In short, let me know how do i retrieve records into crystal report from 2 different table using "objid"(one common field in both the tables through which they relate to each other).

I am eagerly waiting for your reply.

Thanks for your time in advance.
Thanks,
bhavesh

0
 

Author Comment

by:bhavesh55
ID: 2700636
scrumper64 ,

You told me to write sequel statement in crystal.How do i wrote below statement in crystal and where.Please let me know.

SQL Statement:

Select first_name
from contact
Where subcase.objid = contact.objid

Thanks for your time in advance.

Thanks,
bhavesh55
0
 

Expert Comment

by:scrumper64
ID: 2700710
SQL should be:

Select first_name, *field*
from contact, subcase
Where subcase.objid = contact.objid

make sure you have fields from both tables after the select, i.e. if first_name is from subcase then *field* should be from contract.

If the link between subcase and contract is optional then try:

Select first_name, *field*
from contact
left outer join subcase on subcase.objid = contact.objid


0
 

Author Comment

by:bhavesh55
ID: 2700716
scrumper64 ,

You told me to write sequel statement in crystal.How do i wrote below statement in crystal and where.Please let me know.

SQL Statement:

Select first_name
from contact
Where subcase.objid = contact.objid

Thanks for your time in advance.

Thanks,
bhavesh55
0
 

Author Comment

by:bhavesh55
ID: 2700744
scrumper64 ,
When i wrote your 2nd sequel statement
in "sql expression Editor" than it gives me

"General Sql Server error:....."

Please Let me know how do i handle it step by step ?

Thanks,
bhavesh55
0
 

Expert Comment

by:scrumper64
ID: 2700851
Maybe you're using a crappy database like Access, I don't think it likes outer joins.
It's probably best to start with a new report and make sure you display fields from both tables as I said in the Proposed Answer.
0
 

Author Comment

by:bhavesh55
ID: 2700923
scrumper64
It already displays me value when i right click(In right click menu, i choose Browse field Data) on it after puting it into the report's detail section.But it didn't show me at the time of previewing the report.


So let me know if you have any idea.

Thanks,
bhavesh55

0
 

Expert Comment

by:AshokKumar
ID: 2703181
bhavesh55:

If you r using Sql Server then do this:
Create a View

Create View <ViewName> As

Select <Select Field Lists>

From SubCase A, Contacts B Where
A.ObjId = B.ObjId

Now, instead of calling two tables call this view alone. Your problem will be over. If using Access create the same above SQL statement as .Sql File and call it in the report. It should solve your problem. Even if it doesn't solve your problem, then send me your email id.
0
 

Expert Comment

by:AshokKumar
ID: 2703190
bhavesh55:

If you r using Sql Server then do this:
Create a View

Create View <ViewName> As

Select <Select Field Lists>

From SubCase A, Contacts B Where
A.ObjId = B.ObjId

Now, instead of calling two tables call this view alone. Your problem will be over. If using Access create the same above SQL statement as .Sql File and call it in the report. It should solve your problem. Even if it doesn't solve your problem, then send me your email id.
0
 

Expert Comment

by:AshokKumar
ID: 2703198
bhavesh55:

If you r using Sql Server then do this:
Create a View

Create View <ViewName> As

Select <Select Field Lists>

From SubCase A, Contacts B Where
A.ObjId = B.ObjId

Now, instead of calling two tables call this view alone. Your problem will be over. If using Access create the same above SQL statement as .Sql File and call it in the report. It should solve your problem. Even if it doesn't solve your problem, then send me your email id.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question