Problem with missing data with linked tables.

[castle63]

I am having a problem linking tables in such a way that I get ALL the data. I have four tables (Member, supplier, sales and rebates)

I want to see all activity for a member (what rebates they got and what sales they had as well). Basically several rows for a member that says:

Supplier...purchases...rebate amount
ABC $5 $2
DEF $5
GHI $15

I am missing the "GHI" row. I can't seem to link the tables in such a way to include data when there is no rebates. Any suggestions?

I am using Crystal reports 8.5.

 

[lbass]

How are your tables currently linked? You definitely need to use a left join FROM the driving table. You should also identify what tables your report fields are coming from by using the convention {table.field} so we can tell how the linking should be done.

You should also not use any selection criteria on the outer joined tables. Please provide your record selection criteria.

-LB

 

[castle63]

Thanks for the post lbass. Glad to see I wasn't the only one working on Saturday.

My linking/SQL is as follows. I did an Inner Join to match the supplier and member tables. And Left outer joins to "try" and get the sales figures and rebate numbers.

SELECT
RebateJournal."documentdate", RebateJournal."Supplier number", RebateJournal."amount",
Member."Member number", Member."Name",
Supplier."Name1",
Sales2004."amount"
FROM
{ oj (("OMNI"."dbo"."RebateJournal" RebateJournal INNER JOIN "OMNI"."dbo"."Supplier" Supplier ON
RebateJournal."Supplier number" = Supplier."Supplier number")
INNER JOIN "OMNI"."dbo"."Member" Member ON
RebateJournal."Member number" = Member."Member number")
LEFT OUTER JOIN "OMNI"."dbo"."Sales2004" Sales2004 ON
RebateJournal."Member number" = Sales2004."memnum" AND
RebateJournal."Supplier number" = Sales2004."supnum"}

I know I can make this work (by adding a rebate of $1 for every suppplier/member and then using formulas to fix any totals) but hopefully you have more suggestions.

Geoff

 

[lbass]

I can't really follow the linking in the SQL statement, but you should have something like:

Member -> equal-> Suppl
| \
| lo \ lo
| > Rebate
v
Sales

Since there could be both sales without rebates and rebates without sales (according to your example), you need a left join to each of those tables.

-LB

 

[castle63]

This is what I have

Two equal....
Rebate (member#) --> Member (member#)
Rebate (Supplier#) --> Supplier (supplier#)

Two Left Outers

Rebate (member#) --> Sales (member#)
Rebate (Supplier#) --> Sales (supplier#)

 

[lbass]

I'm having a little trouble conceptualizing what a "member" is in relation to a "supplier", so I'm not sure my logic is correct here, but you could try something like:

Supplier.Supplier# ->lo->Sales.Supplier#
Sales.Member#->lo->Member.Member#
->lo->Rebate.Supplier#
Rebate.Member#->lo->Member1.Member#

...where Member1 is an alias for the Member table added a second time, and where all joins are left outer (because all joins to the right of a left join must be left outer).

-LB