AnsweredAssumed Answered

Sum of selected records of a child table

Question asked by alaghi on Jan 26, 2009
Latest reply on Jan 28, 2009 by ninja


Sum of selected records of a child table




I have a money control database, with a movement table (with 1 record per transaction) and another tables that groups each transaction.


I have a table called "items", with a calculated field called "Quantity in stock". This database is small, and I wish to sum all the "quantity" field in movement table to get the total in stock.


In other words, I need to "translate" the SQL statement bellow to File Maker understands it:


select sum(a.Quantity)

from movement a, items b

where b.id_items = a.id_items             -- primary key and foreign key

and a.transaction_type in ("sell","buy","borrow")