Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by emanna, Jul 28, 2006.

  1. emanna

    emanna
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. Joan Wild

    Joan Wild
    Expand Collapse
    Guest

    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'

    --
    Joan Wild
    Microsoft Access MVP

    emanna wrote:
    > 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.
     

Share This Page