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

Now I need to know how many I have on hand so I link over to my Warehouse and yup its a one to many so now I have 2 one to many relationships here 

Needless to say mt totals are all whacked out

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

Crystal builds

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


Edited by DoubleD - 19 Mar 2014 at 5:04am

Hey thanks for the input. I don't have the ability (or knowledge) to use stored procedures. If I had access rights would create a view with some joins to get the data but they have me locked out.

I basically went with a main report and a subreport  but using 2 subreports also worked.