?
Solved

Crystal Report 8.5

Posted on 2009-02-12
19
Medium Priority
?
749 Views
Last Modified: 2013-11-15
How to join in a report the two different table in one database?
0
Comment
Question by:Whing Dela Cruz
  • 8
  • 7
  • 3
18 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 23653760
You can use the Crystal Database wizard....and drag the linking field to the other table's linking field.

Or you can use SQL Syntax to do this...and then just use the SQL Script at the ADD Command line near the datasource......???

What exactly are you trying to do?

M
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 600 total points
ID: 23653773
The easy way is to select the tables in the DATABASE EXPERT and use the JOIN tab to link the tables.

mlmcc
0
 

Author Comment

by:Whing Dela Cruz
ID: 23653884
Hi! I tried to link the two tables but a message says " Unable to smart link unlinked Tables. Foreign key relationships may not be present for the tables. or the database driver may not support the retrieval of this information.

I try to use ado but i do know the procedure. Can you give me some ideas on how to do it.
is it possible to view the report between the two tables withno relations to each other?
I'm  vb6 and crystal 8.5 beginner. Thanks
0
Technology Partners: 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 17

Accepted Solution

by:
MIKE earned 900 total points
ID: 23653901
The linking fields may NOT be the same data TYPES....are they the same?

Don't use SMART LINK either...manually link them....SMART LINK is an option that will TRY and link the tables based on data field NAME...and it is more often than not WRONG.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 23654016
To turn smart linking off use the FILE --> REPORT OPTIONS or FILE --> OPTIONS

mlmcc
0
 

Author Comment

by:Whing Dela Cruz
ID: 23654165
They are the same database Fields. Their name are MyIncome and MyExpenses  I manually link them and successfully done. I try to drag and paste the field under MyIncome and it is successfully done. the field was able to display. I add the field under Myexpense now all field will not display anymore. no message appear...
What shall i do now?
0
 
LVL 17

Expert Comment

by:MIKE
ID: 23654191
Thats because NONE linked.

So the link is not a MATCH...in my opinion. Are you sure these are linkable fields...even their names are NOT seeming to be linkable.... INCOME vs EXPENSES... are totally separate....

IS there an ID field...to use? What are all the fields in each table please?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 23654193
Do you have records in both tables?

mlmcc
0
 

Author Comment

by:Whing Dela Cruz
ID: 23654254
Expenses
P_code   P_Description        P_date     P_Category         P_OutTo       P_Amount    P_Type

Income
TheXcode    TheIncomeDes    D_Date     TheIncomSource     TheIncomeAmount

They Dont have a common things!

Both tables had records already....

0
 
LVL 17

Expert Comment

by:MIKE
ID: 23654299
These tables are NOT linkable then. Meaning the data can not be linked in a meaning full way.

In normal Finance Accounting,...one positive and one negative. EXPENSES are DEDUCTED from INCOME.

You must have a field in EACH table that you can link on.

What kind of report are you trying to produce...something like a FINANCIAL STATEMENT or BALANCE SHEET?

I mean you can do a UNION ALL on these two tables in SQL and have the columns LINE UP with each other....???

For example:

select p_code, p_description, p_date, p_category, p_outto, p_amount, p_type
from yourExpenseTable

UNIONALL

select thexcode, theincomedes, d_date, theincomesource, 'fillerfield1',theincomeamount, 'fillerfield 2'
from yourIncomeTable

This will produce a SINGLE table of data...but again....will this be MEANINGFUL to you in reporting?

M
0
 

Author Comment

by:Whing Dela Cruz
ID: 23654425
That's really what i trying for.. I  want to produce a report  income vs expenses. Can you tel me the Procedure? Do i need to add a field both tables that are common to each other?
if there's a field that are common to each other can i really  make the solution income vs expenses?
I'm very sorry but i don't understand the given instructions about Sql Line Up..
Thanks to you...
0
 
LVL 17

Expert Comment

by:MIKE
ID: 23654453
What is your database...what type is it...is it SQL Server or what?

You can definitely use the UNION ALL sql command above....but you'll need to add this sql script at the ADD COMMAND LINE that is found near where you assign your datasource.

So what databbase are you using?
0
 

Author Comment

by:Whing Dela Cruz
ID: 23654475
I'm using MSSQLServer
0
 
LVL 17

Expert Comment

by:MIKE
ID: 23654478
You can do this in many, many different ways..and so the way to handle this would greatly depend on what kind of report you are trying to achieve.

0
 

Author Comment

by:Whing Dela Cruz
ID: 23654537
In my problem above, Can you give me idea or procedure  in order for me to  achieved my desire report . Or
What is your  advice for me?
0
 
LVL 17

Expert Comment

by:MIKE
ID: 23654713
This is tough to say, since I don't really know all of your reporting requirements.

From what you've said and what I understand so far,...I would advise:

IF you want an FINANCIAL STATEMENT type of report that compares INCOME with EXPENSES....then I think you can use the UNION ALL command to pull all of the Income and Expense data into ONE table. Then use Crystal to summarize the data by CODE.

It appears that your data fields have a CODE of some sort to categorize the Income and the Expense....once you UNION ALL the two tables,..then add the SQL Script to the Crystal Report ADD COMMAND....and it will use the script as a single table....then build your report...using formulas etc... to create the groupings you need.

I'll need more info from you regarding the type of reoprt you want before i can offer more.

Please post a sample of the type of report you want to achieve...
0
 

Author Comment

by:Whing Dela Cruz
ID: 23655151
Ok thanks
0
 

Author Closing Comment

by:Whing Dela Cruz
ID: 31546173
Thanks a lot and More power of both of you
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month15 days, 17 hours left to enroll

850 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