Crystal Report 8.5

How to join in a report the two different table in one database?
Whing Dela CruzAsked:
Who is Participating?
 
MIKESoftware Solutions ConsultantCommented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
mlmccCommented:
The easy way is to select the tables in the DATABASE EXPERT and use the JOIN tab to link the tables.

mlmcc
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Whing Dela CruzAuthor Commented:
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
 
mlmccCommented:
To turn smart linking off use the FILE --> REPORT OPTIONS or FILE --> OPTIONS

mlmcc
0
 
Whing Dela CruzAuthor Commented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
mlmccCommented:
Do you have records in both tables?

mlmcc
0
 
Whing Dela CruzAuthor Commented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
Whing Dela CruzAuthor Commented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
Whing Dela CruzAuthor Commented:
I'm using MSSQLServer
0
 
MIKESoftware Solutions ConsultantCommented:
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
 
Whing Dela CruzAuthor Commented:
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
 
MIKESoftware Solutions ConsultantCommented:
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
 
Whing Dela CruzAuthor Commented:
Ok thanks
0
 
Whing Dela CruzAuthor Commented:
Thanks a lot and More power of both of you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.