Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2002
    Posts
    168

    Unanswered: Simple Group By Query

    I have a table like this :

    ID Jan Feb Mar
    01 100 200 300
    01 400 500 600

    I want to group by ID, sum jan,feb, mar and rename the
    sums as jan99, feb99 and mar99. And then create an
    expression Q1_sales which is sum of jan99, feb99 and
    mar99. Also, I need to filter it so only ID with Q1_sales
    > 0 is displayed. I managed to do all of these, except the
    last step. Everytime I run it, it always asks for
    jan99,feb99 and mar99. It seems that I cannot use aliases
    in the criteria. The final result should be like this :

    ID Jan Feb Mar Q1_sales
    01 500 700 900 2100

    There are 2 things I can get around this problem. First,
    change the having criteria in SQL to Sum(jan)+Sum(feb)+Sum
    (mar)>0 OR create a second query and filter Q1_sales > 0.

    Is there an easier way to do this than (1 query and no
    need to play around with SQL) ?


    Thanks

  2. #2
    Join Date
    Apr 2002
    Posts
    168
    Sorry, the final result should be :

    ID jan99 feb99 mar99 Q1_sales

  3. #3
    Join Date
    Jan 2003
    Posts
    1

    Cool

    assume the table name is sam
    The following query will server your purpose, i think

    SELECT sam.ID, Sum(sam.JAN) AS jan99, Sum(sam.FEB) AS feb99, Sum(sam.MAR) AS mar99, Sum([sam].[JAN]+[sam].[FEB]+[sam].[MAR]) AS Q1_SALE
    FROM sam
    GROUP BY sam.ID
    HAVING (((Sum([sam].[JAN]+[sam].[FEB]+[sam].[MAR]))>0));

    It is ok
    Last edited by sarguru; 01-31-03 at 00:23.

  4. #4
    Join Date
    Apr 2002
    Posts
    168
    Thanks, your answer is correct, in fact, that is what I did before.

    The only problem is that I can't do this easily in the query grid, since I can't put >0 in the Q1_sales grid. I need to teach inexperienced user about this which is not interested in coding SQL. That's why I did it in 2 steps/queries where the second query just filters Q1_sales > 0. The Q1_sales formula is jan99 + feb99 + mar99. So Access does not allow me to use aliases in the having criteria.

    Any other way to overcome this problem ?

  5. #5
    Join Date
    Apr 2002
    Posts
    168
    I mean to overcome using only 1 query.

  6. #6
    Join Date
    Jan 2003
    Posts
    46
    Originally posted by milan
    I mean to overcome using only 1 query.
    Please, save yourself the headache.

    Presumably any query output for a production report will be presented to the user via a report. So put Access's abilities to their best use with reports' innate sorting/grouping capabilities.

    Also, breaking down large, complex queries into simpler ones usually results in better performance by sequencially reducing the record set SQL has to run through.

    Cheers, Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •