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
