Calculate item totals across different fields

[ddicreative]

I hope this is the correct section to post this in, sorry if it is not. This is my first post on these forums.

Here's my situation:

I have to separate databases, one which contains a listing of all possible products([color:green]Products), the other is for a product assortment([color:orange]Assortment).

The [color:green]Products database consists of numerous records of individual items, their [color:purple]item#, price, and a product image. Examples of [color:purple]item #s are: 0001, 0002, 0003, 0004, 0005, etc...

[color:orange]Assortment consists of a table with four item fields ([color:green]Item1, [color:green]Item2, [color:green]Item3, [color:green]Item4) a quantity field ([color:blue]QTY), and an Assortment Number field ([color:blue]ast#). [color:blue]QTY is used to hold the total quantity of a particular assortment. A new assortment is created when a user creates a new record and creates a number in the [color:blue]ast# field, and enters product numbers in the [color:purple]item #s for [color:green]Item1, [color:green]Item2, [color:green]Item3, and [color:green]Item4, and enters the [color:blue]QTY of this assortment.

Let's say I have 3 total assortment records: [color:blue]A001, [color:blue]A002, [color:blue]A003. Assortment [color:blue]A001 contains the following items:

[color:green]Item1=[color:purple]item#(0001)

[color:green]Item2=[color:purple]item#(0002)

[color:green]Item3=[color:purple]item#(0003)

[color:green]Item4=[color:purple]item#(0004)

[color:blue]QTY=350

Assortment [color:blue]A002 contains:

[color:green]Item1=[color:purple]item#(0003)

[color:green]Item2=[color:purple]item#(0002)

[color:green]Item3=[color:purple]item#(0004)

[color:green]Item4=[color:purple]item#(0005)

[color:blue]QTY=225

And assortment [color:blue]A003 contains:

[color:green]Item1=[color:purple]item#(0005)

[color:green]Item2=[color:purple]item#(0001)

[color:green]Item3=[color:purple]item#(0002)

[color:green]Item4=[color:purple]item#(0004)

[color:blue]QTY=500

As you can see in the example, [color:purple]item#(0001) belongs to [color:green]Item1 in record [color:blue]A001, but [color:green]Item2 in record [color:blue]A003. What I need to calculate is the total quantity for each [color:purple]item# used, regardless of which [color:green]Item it falls under.

I've tried using a Summary field, but it only takes the highest quantity from a field. Example: [color:purple]item#(0002) is located in [color:green]Item2 twice, but the summary would only return a quantity of 850, not 1075.

Anyone have any suggestions? I hope I've made this clear enough for you to get the idea.

Thanks

Edited June 25, 2009 by Guest

[comment]

This is a structural issue: the relationship between Products and Assortments is many-to-many, and there should be a join table in-between them.

With some limitations, you could perhaps manage somehow by using repeating fields in the Assortments table. Then you could sum the total quantity over the relationship - but with little control over which assortments were included in this total.

NOTE:

This is assuming there's always only one item of a product in an assortment.

Edited June 25, 2009 by Guest
Added note

[ddicreative]

Since I don't need to know which assortments were included in the totals, the Repeating Field option sounds like it may work. I'll just have to do some reading up on join tables and repeating fields, since I know nothing about either lol.

Per your note: You are correct to assume that a product will only appear once in any given assortment.

And thanks!

Edited June 25, 2009 by Guest
Saw new note.