I need to write a report that includes a  one to many relationship on two tables

I started simple access the table with one (inventory) I link it to a table with a 1 to many relationship (InvMovement)

I group  on and to  sum on qty move  - Life is good

Then I add the warehouse table so I can figure out how many items I have on hand. Yup its a one to many so now I have 2 one to many relationships here  (1 to many to many)

Needless to say my totals are all whacked out. I can get one side or the other to total OK but not both.

Given the tables

INV
1
2

INV Movement
1         10
1         20
2         15

INV  Warehouse     QTY
1            A                  3
1            B                  5
1            C                  2
2            A                  10
2            B                  10

In Crystal when I link them all together I get a complex that looks like this

Inv    Move  WH    QTY
1          10      A      3
1          10      B      5
1          10      C      2
1          20      A      3
1          20      B      5
1          20      C      2
2          15      A      10
2          15      B      10


The report should show

INV    Tot Moved    On hand
1            30                 10
2            15                 20


How would you go about making the report total correctly  I get a total of 90 movements for INV 1 and 20 on hand  

For inv 2 I get total movement of 30 and a qty of 20

Cyrstal defaults the links  but I deleted them all and made them

INV link partno to partno left outer join to INV Movement and
INV link partno to partno left outer join to INV Warehouse

So I think the answer to your question is Yes

If that does not work you can put Inv Warehouse table in a subreport linked to INV by INV, so in the main report you will have INV and Inv Movement, there do a group by INv, put there the INV, the summary by move and the subreport, in the subreport make a gruop by INV as well and put on that gruop the summary by QTY

I may not have a movement for every piece of inventory so wouldn't I  drop records by linking like this ?

  Otto The Subreport worked
Thank you ever so much for reminding me

I am interested in your previous post about linking INV to INV MOVEMENT and INV MOVEMENT to INV WAREHOUSE  - Would that really work? Wouldn't it drop records . I want data for verything in my inventory - if I have no movement would the outer join still bing in the INV record so that I can find anything in the warehouse table - I may have to  experiment with this after I satisfy the client request.




Ooops  may have to switch the link and sub report I think I'm dropping stuff but 'm sooo much closer now


Tanks a again


Edited by DoubleD - 19 Mar 2014 at 7:13am