We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Crystal Report 8.5

Medium Priority
849 Views
Last Modified: 2013-11-15
How to join in a report the two different table in one database?
Comment
Watch Question

MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
The easy way is to select the tables in the DATABASE EXPERT and use the JOIN tab to link the tables.

mlmcc

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
Software Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006
Commented:
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.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
To turn smart linking off use the FILE --> REPORT OPTIONS or FILE --> OPTIONS

mlmcc

Author

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?
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Do you have records in both tables?

mlmcc

Author

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....

MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

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...
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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?

Author

Commented:
I'm using MSSQLServer
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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.

Author

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?
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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...

Author

Commented:
Ok thanks

Author

Commented:
Thanks a lot and More power of both of you
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.