Database Relation issue

[Former Member]

Guys,

I have got one more problem.

My database relation detail

Table.field Link Table.field

Project.JobID ==> Project_cost.JobID

Project.JobID ==> ItempurchasesLines.JobID

Project_cost.AccountNumber ==> Accounts.AccountNumber

Project_cost.AccountID ==> Item.ExpensesAccountID

Item.ItemID ==> ItempurchasesLines.ItemID

What I want now is, I want a full list of accountID in my report from Project_cost table regardless of any record available in ItempurchaseLines. ItempurchaseLines table is linked through itemID field with Item table and Item table is linked with project_cost through field Project_cost.AccountID ==> Item.ExpensesAccountID.

Thanks

mithani

[former_member11696]

Please re-post if this is still an issue to the Data Connectivity - Crystal Reports Forum or purchase a case and have a dedicated support engineer work with you directly.

First I would post your issue to the other database company. It's possible the driver you are using does not support multiple joins or may be limited to ODBC level 2 functionality.

[Former Member]

Your requirment is not clear

[Former Member]

Database Link

Below is a database link where I have mentioned table name.field name and link (==>) table name.field name

Project.JobID ==> Project_cost.JobID

Project.JobID ==> ItempurchasesLines.JobID

Project_cost.AccountNumber ==> Accounts.AccountNumber

Project_cost.AccountID ==> Item.ExpensesAccountID

Item.ItemID ==> ItempurchasesLines.ItemID

I am using inner joint.

I need my report samething like.

JobID----AccountNumber-----ItemID

1001----5001--

---

AAA

1001----5020--

---

BBB

1001----5050--

---

-

There is no purchase on accoutnumber 5050 but sill it is showing in my report. I am sure I have to use outer joint. Where I can show all my account number.

Regards

mithani

[Former Member]

you can suppress detail section, if ItemID is null. in the same structure

In the selection expert of details secion,

Check Suppress-->open the formula editor(click on the X-2 button parallel to the suppress option)

Right,

if isnull(ItemID) then
true
else 
false
save and close

Hope it will help you. If not post your sql query here.

regards,

salah

[Former Member]

salah,

sorry i am not very clear. see below i have taken from crystal report help and that what i need.

The result set from a Left Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table. For instance, you can use a Left Outer join to view all customers and the orders they have placed, but you also get a row for every customer who has not placed any orders. These customers appear at the end of the list with blanks in the fields that would otherwise hold order information:

Customer Table -

---

Customer Table -

---

Orders Table

Customer ID -

---

Customer Name -

---

Order Amount

52 ..............................Allez Distribution.............................$25,141.50

53 .............................BG Mountain Inc..............................$19,164.30

53 ............................. BG Mountain Inc..............................$16,83.60

57 ............................. Hansen MTB Inc..............................$15,716.40

58 ............................. La Bomba de Bicicleta ...................$1,956.20

60 .............................Mountain Toad ................................$24,580.50

62 .............................SFB Inc. ...........................................$7,911.80

63 .............................Sierra Bicycle Group ......................$19,766.20

63 .............................Sierra Bicycle Group ......................$12,763.95

64 .............................Sierra Mountain ................................$8,233.50

54 .............................Bicicletas Aztecas

55 .............................Deely MTB Inc.

regards

mithan

[Former Member]

OK,,

Try with this query,

Select Project.JobID,Accounts.AccountNumber,Item.ItemID 
from project
left outer join  Project_cost on Project.JobID =Project_cost.JobID
left outer join Accounts on Project_cost.AccountNumber = Accounts.AccountNumber
left outer join ItempurchasesLines  on Project.JobID = ItempurchasesLines.JobID
left outer join Item on Item.ItemID = ItempurchasesLines.ItemID

[Former Member]

getting below errors

Failed to retrieve data from the database

Database connector Error : "If tables are already linked then the join type cannot change"

Below is a sql query with all inner join. I have changed from inner to outer but was still showing error.

FACADE
 SELECT "ItemPurchaseLines"."TaxExclusiveTotal", "ItemPurchaseLines"."JobID", "Accounts"."AccountID", "Accounts"."AccountNumber"
 FROM   ("SCHEMA"."MYOB"."Items" "Items" INNER JOIN "SCHEMA"."MYOB"."ItemPurchaseLines" "ItemPurchaseLines" ON "Items"."ItemID"="ItemPurchaseLines"."ItemID") INNER JOIN "SCHEMA"."MYOB"."Accounts" "Accounts" ON "Items"."ExpenseAccountID"="Accounts"."AccountID"
 ORDER BY "ItemPurchaseLines"."JobID", "Accounts"."AccountID"
 EXTERNAL JOIN ItemPurchaseLines.JobID={?MS Access DB: Project_cost.JobID} AND Accounts.AccountNumber={?MS Access DB: Project_cost.AccountNumber}


MS Access DB
 SELECT `Project_cost`.`Type`, `Project_cost`.`Rate`, `PROJECT`.`PROJECT_NAME`, `Project_cost`.`EXPENSES_Code`, `Project_cost`.`Description`, `Project_cost`.`Projected_variation`, `PROJECT`.`REVISED_CONTRACT_VALUE`, `Project_cost`.`AccountNumber`, `Project_cost`.`JobID`
 FROM   `PROJECT` `PROJECT` INNER JOIN `Project_cost` `Project_cost` ON `PROJECT`.`JobID`=`Project_cost`.`JobID`
 WHERE  `Project_cost`.`JobID`={?FACADE: ItemPurchaseLines.JobID} AND `Project_cost`.`AccountNumber`={?FACADE: Accounts.AccountNumber}
 ORDER BY `Project_cost`.`Type`

regards

mithani

[Former Member]

now no error but still not retrieving data from tabel ItemPurchaseLines.

below is an updated sql query

MS Access DB 
SELECT `PROJECT`.`JobID`, `PROJECT`.`PROJECT_NAME` 
FROM   `PROJECT` `PROJECT` 
ORDER BY `PROJECT`.`JobID` 
EXTERNAL JOIN PROJECT.JobID={?FACADE: ItemPurchaseLines.JobID} AND PROJECT.JobID={?MS Access DB: Project_cost.JobID} 


FACADE 
SELECT "ItemPurchaseLines"."TaxExclusiveTotal", "ItemPurchaseLines"."JobID" 
FROM   "SCHEMA"."MYOB"."ItemPurchaseLines" "ItemPurchaseLines" 
WHERE  "ItemPurchaseLines"."JobID"={?MS Access DB: PROJECT.JobID} 


MS Access DB 
SELECT `Project_cost`.`Type`, `Project_cost`.`EXPENSES_CODE_NUM`, `Project_cost`.`EXPENSES_Code`, `Project_cost`.`Description`, `Project_cost`.`Rate`, `Project_cost`.`AccountNumber`, `Project_cost`.`JobID` 
FROM   `Project_cost` `Project_cost` 
WHERE  `Project_cost`.`JobID`={?MS Access DB: PROJECT.JobID} 
ORDER BY `Project_cost`.`Type`, `Project_cost`.`EXPENSES_CODE_NUM` 
EXTERNAL JOIN Project_cost.AccountNumber={?FACADE: Accounts.AccountNumber} 


FACADE 
SELECT "Accounts"."AccountNumber" 
FROM   "SCHEMA"."MYOB"."Accounts" "Accounts" 
WHERE  "Accounts"."AccountNumber"={?MS Access DB: Project_cost.AccountNumber}

ItempurchaseLines table contains all purchase transaction.

My report would look something like that:

Group header 1 "JobID" from Project table

Group header 2 "Type" from Project_cost table

Group header 3 "Project_cost.Expenses_code--

---

Project_cost.description--

---

ItemPurchaseLines.TaxExclusiveTotal etc"

now I can see all expenses code, description but still not getting any purchases from ItemPurchaseLines.

Regards

Mithani

Edited by: mithani2747 on Sep 4, 2009 6:00 AM

Edited by: mithani2747 on Sep 4, 2009 6:01 AM

[Former Member]

any help would really appreciated.

regards

mtihani