Resultset too large when field exists in 2 tables

[Martijnbm]

I'm making a report that should display the number of a given part used in sales and in production.
I am using 3 tables for the report:
table 1 holds part info
table 2 holds sales info
table 3 holds production info

Table 2 and 3 are each linked to table 1.

The input is a part number.
The output should be how many were used in sales and how many in production. Either one works fine but when I use both the sales and production table to the report, I get the same sales record for each production record. Probably because they both have the field Part in them. This way the output is way too big and displays records numerous times. Is this a matter of linking the tables differently, grouping differently or something different altogether?

Attached are a pic of the tables (left 2 are sales, right 2 are production) and a pic of the resultset where for each P-number the same OR-number is displayed.

Thanks in advance!

[Jukka]

Hi,

You should sum the data from the tables you join from your part table. In addition you will loose data if you use an equal join to your two other tables if you don't have a record containing the join field in these tables.

- Jukka

[Martijnbm]

How do I sum the data from the two tables without joining them?

[Martijnbm]

I also tried to make 2 subreports with no other fields in the main report but I have to define all parameters in both subreports and the main report. Perhaps the parameters could be passed to the subreports?! I have no idea how this is done... :(

[Jukka]

Hi,

Let's start working your joining problem. You simply join your tables, but put a sum on the fields from the two other table and create a group by from the fields in your primary table.

select a.s, a.t, a.u, sum(b.v), sum(c.x)
from tab1 a, tab2 b, tab3 c
where
/*insert your join conditions*/
group by a.s, a.t, a.u

The second problem can be solved by sending the main report parameters to the subreport using the edit subreport links dialog.

- Jukka

[Martijnbm]

I talked to someone today who showed me in like 20 seconds how to do it (felt like such a dork). Crystal won't salve the problem. By executing 2 queries in sql and inserting the results in a report the problem is solved. It's like using 2 subreports but then in a stored procedure. It won't work in Crystal because of the joins.

Jukka, I'm gonna give your suggestion a go, cause using a stored proc is not always desired. Thx!