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

Sorry, the final result should be :

ID jan99 feb99 mar99 Q1_sales

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

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 ?

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