| Author |
Message |
PLTyler
Newbie
Joined: 03 Jan 2009
Online Status: Offline
Posts: 24
|
   Topic: Table Join Issue
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
Edited by PLTyler - 14 Nov 2012 at 11:51pm
|
 IP Logged |
|
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
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?
|
|
IP Logged |
|
PLTyler
Newbie
Joined: 03 Jan 2009
Online Status: Offline
Posts: 24
|
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
|
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
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
|
|
IP Logged |
|
PLTyler
Newbie
Joined: 03 Jan 2009
Online Status: Offline
Posts: 24
|
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
|
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 15 Nov 2012 at 4:58am |
|
http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx
may help
|
|
IP Logged |
|
PLTyler
Newbie
Joined: 03 Jan 2009
Online Status: Offline
Posts: 24
|
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.
|
|
IP Logged |
|
PLTyler
Newbie
Joined: 03 Jan 2009
Online Status: Offline
Posts: 24
|
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
|
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
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
|
|
IP Logged |
|
PLTyler
Newbie
Joined: 03 Jan 2009
Online Status: Offline
Posts: 24
|
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
|
|
IP Logged |
|
|
|
