Thank you for your post.
It sounds like you have the CId connected between the two tables, but it concerns me that most Competitors may have multiple X items. That means, there is some duplication somewhere. You may want to consider creating a third table that holds the transactions (let's call it TRANSACTIONS table). For example, a Competitor may order item X one week, and then order item X another week. These are two separate transactions, and you probably don't want to duplicate the record in the AllItems table, and you don't want to duplicate the Competitor record.
In the third table TRANSACTIONS, include the CId field into the Competitor table, and you may want to change the key field in the AllItems table to a PId (Product ID) and link that to a PId field in TRANSACTIONS. This way, you can select the CId in the Competitor table, and the PId in the AllItems table. Does that make sense?
In TRANSACTIONS, include a Quantity field, because a person may order 5 Widgets in November, and then another 10 Widgets in December. You don't want to put the quantity in the Competitors and/or AllItems table.
To add up this information, you will need a summary field (TOTAL) which adds up the Quantity.
Next, create a report that has a sub-summary when sorted by CId, and include the Name field from the Competitors table. Below that, create a sub-summary when sorted by PId, and include the appropriate information from the AllItems table along with the TOTAL field.
You do not need a Body.
Finally, sort by CId followed by PId and go to Preview. You will now see the name of the Competitor and on the lines below that, each item listed once followed by the total quantity.
Obviously, you do not need a portal for this method.
If you do use the portal method, you cannot filter out the duplicate items. In layout mode, select all the fields, pull down the format menu and select "Set Sliding/Printing..." and select the option to slide up based upon all above, and also check the option to "Also reduce the size of the enclosing part". The Body part will move upwards so that you lose the empty space; even with a portal.
I know there is a lot of information here, and it probably goes against your current design, but think about it. If you need clarification for anything I covered, please let me know.
Sorry for the delay in getting back to you...I have not been able to be online. And thank you too for the detailed response here.
Unfortunately your message begins with an inaccuracy that then leads you astray. :-) You say, "...there is some duplication somewhere" and you then set about to fix that. However, there is no duplication and the data model is correct in this case.
I'm sure too that I'm not representing the problem or the goal in the best way possible, is there something that I can clarify that will help solve the first problem that I have.
Note that I understand the sliding-problem now.
Thank you for your help.
I just solved this problem. While I had setup the relationship between the two databases, I never realized that you can put additional conditions on the relationship. Works great now.
Thank you for your help.