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) ?
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
GROUP BY sam.ID
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.