Table Join Issue
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18011
Printed Date: 01 May 2024 at 8:25pm
Topic: Table Join Issue
Posted By: PLTyler
Subject: Table Join Issue
Date Posted: 14 Nov 2012 at 11:50pm
Hi, I am using CR XI R2 on Win 7 and am linking an SQL Server 2008 table with an MS Access 2007 table. The tables store budgets in the Access table and expenditures in the SQL Server table and are linked via account and cost center codes.
At the moment if there is no corresponding budget record in the Access table OR no corresponding expenditure record in the SQL Server table the report line is, obviously, not printed.
I need to include ALL rows (as filtered by the WHERE clause). Can I use a Join statement? I read somewhere that MS Access does not support Joins?
Thanks in advance!
Peter Tyler
|
Replies:
Posted By: comatt1
Date Posted: 15 Nov 2012 at 4:03am
within the application, maybe, but you should be able to setup a link to both tables and create links within a sql command.
What do you have so far?
|
Posted By: PLTyler
Date Posted: 15 Nov 2012 at 4:14am
>>What do you have so far?>>
I have a CRXI report linked to both tables but I am losing data from both tables because Crystal (SQL) requires a record to exist in both datasets. So my budget and expenditure totals are wrong because data is not being completely picked up.
Peter Tyler
|
Posted By: comatt1
Date Posted: 15 Nov 2012 at 4:17am
|
UNION them or LEFT OUTER JOIN, do this in a sql command... or go to link table and switch join type
|
Posted By: PLTyler
Date Posted: 15 Nov 2012 at 4:29am
Thanks. I read that I will need a full join as there is data missing from both tables. My concern is more that I read MS Access does not support (Full) Joins.
I will give it a try.
PT
|
Posted By: comatt1
Date Posted: 15 Nov 2012 at 4:58am
http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx
may help
|
Posted By: PLTyler
Date Posted: 15 Nov 2012 at 7:05am
>>http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx
may help>>
Wow! Some good bedtime reading here!
Thanks a lot.
|
Posted By: PLTyler
Date Posted: 16 Nov 2012 at 2:57am
Hello again,
>>what do you have so far?>>
I am listing below the existing SQL from my Crystal XI report
1) CODA2012 //SQL Server table source
SELECT "oas_balance"."curcode", "oas_balance"."cmpcode", "oas_balance"."yr", "oas_balance"."el1", "oas_balance"."el2", "oas_balance"."balcode", "oas_balance"."full_value", "oas_balance"."period", "oas_el1_element"."el1_name" //The expenditure actuals are in this table in field "oas_balance.full_value"
FROM "coda"."dbo"."oas_balance" "oas_balance" INNER JOIN "coda"."dbo"."oas_el1_element" "oas_el1_element" ON ("oas_balance"."el1"="oas_el1_element"."el1_code") AND ("oas_balance"."cmpcode"="oas_el1_element"."el1_cmpcode") //Inner Join to grab account text description from related SQL table
WHERE "oas_balance"."curcode"='CHF' AND "oas_balance"."cmpcode"='LWFEUR2' AND "oas_balance"."el1" LIKE '8%' AND "oas_balance"."yr"=2012 AND "oas_balance"."period"<=12 AND "oas_balance"."el2"='150' AND "oas_balance"."balcode"='ACTUAL' //Expenditure accounts start with "8" and using cost Center "150" for test purposes.
ORDER BY "oas_balance"."el1", "oas_balance"."el2"
EXTERNAL JOIN oas_balance.el1={LWF Apps\MarsFiles\CommonFiles\Budgets.mdb: Budgets.EL1} AND oas_balance.el2={LWF Apps\MarsFiles\CommonFiles\Budgets.mdb: Budgets.EL2}
2) LWF Apps\MarsFiles\CommonFiles\Budgets.mdb //MS Access table source
SELECT `Budgets`.`FULL_VALUE`, `Budgets`.`YR`, `Budgets`.`EL2`, `Budgets`.`EL1` FROM `Budgets` `Budgets` WHERE `Budgets`.`EL1`={?CODA2012: oas_balance.el1} AND `Budgets`.`EL2`={?CODA2012: oas_balance.el2} AND `Budgets`.`YR`=2012
++++++++++++++++++++++++++++++++++++++++
I guess if I were to use the UNION clause the SQL would be (roughly) as follows?
SELECT <<Crystal Report fields>>
FROM (SELECT <<Fields in the SQL Server "Actuals" table>> FROM "coda"."dbo"."oas_balance"
UNION ALL
SELECT <<Fields in the MS Access Budget table>> FROM {LWF Apps\MarsFiles\CommonFiles\Budgets.mdb)
WHERE <<Insert report filters as in original SQL>>
My questions:
+ Do the fields in both UNIONs have to be identical?
+ How do I actually insert the SQL into Crystal reports XI?? Where do I "switch join type" as you wrote?
Data access is via ODBC.
Many thanks for your advice!
Peter Tyler
|
Posted By: comatt1
Date Posted: 16 Nov 2012 at 3:03am
the number of fields, and the type need to be, if there are different types,
cast them as you need
if a number
cast ( number.field as varchar (20) )
like that, but they must be equal number and type
|
Posted By: PLTyler
Date Posted: 16 Nov 2012 at 3:09am
OK but how do I put up the revisd SQL statement in CRXI that will apparently overwrite my Select Expert choices?
Thanks.
Peter Tyler
|
Posted By: PLTyler
Date Posted: 16 Nov 2012 at 3:14am
|
OK, Database Expert - Links. Except my Link Options is greyed out.
|
Posted By: comatt1
Date Posted: 16 Nov 2012 at 3:26am
if using a sql command the links were inherent to the query, you don't use CR smartlinking anymore.
|
Posted By: PLTyler
Date Posted: 16 Nov 2012 at 3:42am
You have lost me here. Where does the new SQL command go - in CRXI - that replaces "CR smartlinking" and the Report Select Expert?
PT
|
|