Create a view for this.
CREATE SQL VIEW MyView AS ;
SELECT Parts.PART_NO, SUM(Quant_Table.QTY) ;
FROM Parts JOIN Quant_Table ON Parts.Part_No = Quant_Table.Part_No ;
GROUP BY Parts.PART_NO ;
ORDER BY Parts.PART_NO
You can test the query by omitting the first line and just running the SQL query:
SELECT Parts.PART_NO, SUM(Quant_Table.QTY) ;
FROM Parts JOIN Quant_Table ON Parts.Part_No = Quant_Table.Part_No ;
GROUP BY Parts.PART_NO ;
ORDER BY Parts.PART_NO
>I need to create a calculated field in one table based on SUM of another field in another table.
>Example: Parts Table: PART_NO, PART_DESCR, etc.
> Quantity Table: PART_NO, QTY, UNIT_PRICE
>
>Since Quantity Table can have more than one record for the same part (different unit price), I need to create a calculated field in Parts Table which will sum QTY for the part in Quantity Table.
>I need this field for use with SFReports so that user can create a report for Parts Table and show Qty for each part.
>
>How would you do it, create this type of calculated field in SDT? Any other suggestions would be greately appreciated.
>
>TIA,
>Dmitry
Mark McCasland
Midlothian, TX USA