LINKING TABLES - REALLY STUMPED 1

[mollie]

Hi -

I'm trying to create a report reading the following tables. (they are dbf files)


icitem sotran artran

item item item
qty qty
extprice extprice

I'm trying to list open order details (sotran) and sales details (artran) using 2 detail lines.

But, the link is screwed up. I've tried left outer, right outer, equal - - getting records in artran when none exist.

Mostly I'm trying to read from the tran tables to the icitem table, but then get hit with multiple starting points.

I'm thinking I need to add icitem twice and link to itself, but haven't gotten that right either.

Could someone lead me through the proper links very specifically - - I'm really stumped!

Thanks

 

[Hattusas]

Create a subreport for artran and use the detail section for the sotran (or just the reverse)

 

[HowardHammerman]

It really depends on your data model. I suspose each
sales order (so) generates an invoice (ar) which relates to items. If you want all so whether or not ar are generated then put so on the left, linking to ar (left outer), linking to items (equal)

Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[mollie]

Thanks for your tips - I still have issues, though. Maybe you know how to resolve.

Hattusus - Yes, I tried sub-reports, but then I don't know how to add together for a total. Meaning, my goal is to be able to add the open order details (sotran) and the shipped order details (artran) together to get a total "usage" by item. Can I do this using a subreport?


Howard - I think if I link the way you say I miss something. I'll get all the sotran, but won't I miss some of the artran? Won't the artran only show up if there's a matching item in sotran with that link? If so, that won't work because there will be items in artran that have shipped that don't also have an open order in sotran to match to an item. Does this make sense?

Thanks

 

[losthorizon]

Thanks to Howard (see above) for defining the terms s.o. and a.r., because, like myself, I'm sure many readers had no idea what mollie was talking about!

 

[mollie]

Hi Losthorizon

You may want to read my post again!

I list each table and its fields - - and the first line after that specifically states what each table is for...

 

[chelseatech]

The problem is caused by using two detail tables for one master table. The SQL rules for joining these tables is to create a report with every combination - so two soTran and 5 artran will create 10 records.

If it an SQL data source, you can create a view to APPEND these two tables to each other.

Select "SOTRAN" as source, item, qty, extprice
from SOTRAN
UNION ALL
Select "ARTRAN" as source, item, qty, extprice
from ARTRAN

Use this view instead of your two tran tables it it works a treat. And it works better than subreports if there is data in the second table for items not in the main report.
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[chelseatech]

The problem is caused by using two detail tables for one master table. The SQL rules for joining these tables is to create a report with every combination - so two soTran and 5 artran will create 10 records.

If it an SQL data source, you can create a view to APPEND these two tables to each other.

Select 'SOTRAN' as source, item, qty, extprice
from SOTRAN
UNION ALL
Select 'ARTRAN' as source, item, qty, extprice
from ARTRAN

Use this view instead of your two tran tables it it works a treat. And it works better than subreports if there is data in the second table for items not in the main report.
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[HowardHammerman]

My good friend Bruce Ferguson at Chelsea is right. You have records in SO that do not have a match in AR and records in AR that do not have a match in SO. So you must to an SQL query with the Union clause.

You can use the Crystal query tool to build the basic query and then modify it with the editor. then run the crystal report from the *.qry file. Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[mollie]

OK - so it took me forever, but I got it running! Yeehaaa.....

Chelseatech, the view worked - - hadn't thought of that.

Howard and Hattusus, thanks for your help also!