Your design is not clear to me.
On the Vendor layout in question, the 50 products are listed. Every product the vendor has sold is indicated via a checkbox.
How did you list the products? Is each product a different record? Did you use a portal or do you have one field with 50 products in a value list and a check box format? Or perhaps you have a list view?
What you describe can be done with a summary report so that you can get both subtotals for each vendor broken down by product, but it requires a particular structure to your tables to pull off without unecessary complications.
I'm sorry I wasn't clear. Each vendor record shows all 50 products; the products do not have individual records. On the vendor records, the products are presented as a list with a checkbox beside each one, but not as a value list -- each one is a separate field. No portal.
Then this design will make your reporting needs complex. It would make for a much more flexible approach if you used a related table with one record for each vendor's product instead of a set of individual fields in the same record.
Thank you for your reply. Do you mean create a table with 50 records, one for each product? Since I already have fields for each product in another table, how would I handle that?
You would create a table of related records with whatever number of records you need to list the products for each vendor. One vendor might supply 5 products, another might supply 75 and the same setup still works.
To move the data from your individual fields into new records in such a related table would require a script that loops through each vendor record and each of these fields, creating the needed related record for each such field that is not empty. This would be a one time update and then you'd need to modify layouts and other aspects of your design to take advantage of the new, much more flexible structure.