Function to Sum Values With Same Characteristics

[Michael C]

Hi. I have bunch of products that I often must group
together when I perform calculations. For example:

Product1, Product2, Product3 are part of Group1
Product4, Product5, Product6 are part of Group2
Product7, Product8, Product9 are part of Group3

In reality, there are about 10-15 products in each group,
so I'm trying to create a module for the first time that
will always make the calculations for me.

I'm trying to make some kind of function like:

Group1 = Product1 + Product2 + Product3

....so that when I run a query/form/report, I can just
write FunctionGroup1 and it will know to sum the sales
dollars for Product1, Product2, Product3. Any thoughts?
Thanks!!!

 

[John Vinson]

Hi. I have bunch of products that I often must group
together when I perform calculations. For example:

Product1, Product2, Product3 are part of Group1
Product4, Product5, Product6 are part of Group2
Product7, Product8, Product9 are part of Group3

You need another Table: Groups. It should have a unique group number
(or it could be a text string if you want, "Group1") and any other
fields you need to describe the group as a whole.

Then you need a new field in your Product table containing the group
number for that product (i.e. Product4 would have "2" or "Group2" in
this field. This "foreign key" is a link to the Groups table.

You will now be able to create Totals queries grouping by the Group
field; use the field as a criterion to display just the Group2
products, etc.

 

[Michael C]

John, Thanks for the response. I'm currently doing this
the way you had suggested but I'm looking for a way that
will allow me to simplify the myriad calculations I do in
queries/forms/reports into reusable modules. I know
there's a way to do this but it's that first time of
setting it up I'm having difficulty. If I can figure out
how to write some function modules once, I think I can
use that as a template going forward. I'll do some
digging online and see what I can come up with. Thanks
again for the suggestion.

 

[John Vinson]

...so that when I run a query/form/report, I can just
write FunctionGroup1 and it will know to sum the sales
dollars for Product1, Product2, Product3. Any thoughts?

It's not a Function that you want - it's a Totals Query, grouping by
Group and summing Sales.