Can I create a "sum" on a built expression in a query?

[emanna]

Okay, lets see if I can explain this.....

I have a table that contains information on lets say paint colors. So the
information in the table would something like this:

date sold color sold by
6-14-06 cotton candy j. doe
5-30-06 sky blue j. smith
etc.

Of course there would be multiple entries of the same color w/ multiple
entries for seller.

I would then want to run a report by seller with a total for each color and
then a total of all colors sold by employee.

In my query, the color field is a text, so I cannot put in "sum" in the
"total" box. An expression (Expr 1) has been created to "count" the colors
that were inputed in the table.

So when i pull up the report it looks like this

j. smith
red 2
cotton candy 3
sky blue 1
grass green 4

Now the problem comes in when i want to do a total of all the colors that j.
smith sold. I cannot get a sum to work. I have tried "=SumofExpr1" but when
running the report, it then asks for the above "=SumofExpr1" as a parameter.

I am assuming it is doing this b/c i can't mark "sum" in the total box for
the expression b/c "expression" is marked.

Does that make sense? If so, I'd love to hear how I can make this work.

 

[Joan Wild]

Sounds like you have the query running the way you want
Group by seller, Group by colour, Count on colour

You can't sum in the query, however do it on the report.

In the group footer for the seller, use a textbox and set it's control
source to
=Sum(CountOfColour)
Substitute the actual name of the column in the query for 'CountOfColor'