Using the Sum function for repeating fields

[Mariano]

I have a project that I am working on for placing Purchase orders. One Table is named Purchase Orders, another is Item Master, and the third is Vendor master.

On the Purchase Order Table the Product Id (product being ordered) is a repeating (10 times)field, as is the product description, Qty, unit price, and extended price.

In the Item Master, each product is its own record with its pricing info, quantity currently in inventory, vendor product is bought from,etc. I also have a field labeled "On Order." Item Master and Purchase Order tables are related by the Product ID. What I would like to do is get the total quantity of a particular product on order in the Purchase Order Table to be displayed in the "On Order" field in the Item Master. So if there are 10 orders for product 'ABCD' all for 5 units, I would like "On Order" to return 50.

Any ideas?

Mark

[T-Square]

Go relational! Create a table for LineItems, with fields for Purchase Order#, Item#, QTY, UnitPrice and ExtPrice.

Summary fields in the Purchase Order and Item Master tables based on related ExtPrice and QTY fields (respectively) will give you totals for the particular entities (POs one the one hand, and Items on the other).

HTH,

David

P.S.-- The Item Description field is not necessary in the LineItems table, since this info can be derived from the Item master table base on the Item# field.

[Mariano]

David-

Thanks for the reply...

I am not totally following what the summary fields would display on the purchase order table.

How would it know to only summarize by product, not all the products on the PO?

Mark

[Mariano]

I did a summary field in a sub-summary part on the Item Master that is looking up quantity on the Purchase Orders. What it is returning is the quantity of the product when it is on the first line of the PO. If it is in any of the repeating fields (PO lines 2-10) of the product ID, the quantity is not being added.

Mark

[bruceR]

The solution proposed by T-Square is dependent on dumping the repeating fields and going to a proper relational design, as he recommended. You really need to do that.