Total Quantity for Multiple Products

[shamrog12]

My situation explained:

I have 15 fields I need to use information from (3 rows of 5):

Product01, Product02, Product03, Product04, Product05

Quantity01, Quantity02 ... Quantity05

ID01, ID02, ... ID05

My fields are set up like this:

| Product01 | Quantity01 | ID 01 |

| Product02 | Quantity02 | ID 02 |

| Product03 | Quantity03 | ID 03 |

| Product04 | Quantity04 | ID 04 |

| Product05 | Quantity05 | ID 05 |

A possible scenerio would look like this:

| Baseball Glove | 20 | BG001 |

| Football | 5 | FB001

| Volleyball Net | 20 | VB001

With hundreds of records with these fields and information on them, I need to find out how I can display the total quantity of BG001 sold throughout all the records and the total quantity of FB001 sold throughout all the records and same for VB001.

I have NO CLUE how to do this and I've been trying to figure it out for days now with absolutely ZERO luck. Please help!

[Geeksharka]

The best way to do this is to split the tables up, and then relate them to each other.

One table would be a Product List, with one record per Product Type (each one with a unique ID#).

The other table would be the Line Items, with each record representing a Product, and the Quantity (QTY) sold at that time (line item). Each record is a Line Item, and has a Product ID on it, and a QTY -- much like the fields you mentioned.

In the Product List table, you set up a portal (based on Product ID#) to look at all the Line Items with the same Product ID#. For example, Product ID#002 will show all the line items where Product #002 was sold _and_ the QTY per sale (or Line Item).

Now, in the Product Table, you can create a Calc field using the SUM function to add up all the QTY fields in the portal relationship. This will show you the total sales to date by Product ID.

This can be eventually worked out to be an inventory system too once you close the loop between Deliveries and Sales

Hope this helps.