Aggregation function issue in Access

[Fais]

Hello,
i have 6 columns in my table and i need to run a query as mentioned below but i get an error message while running, please assist. thanks.

select
column1,
trim(column2) as column2_trim, ---uses a combination of trim, instr and mid functions to drive a text from a field
count(column2_trim), ---- i want to do a count on the previous column


column3,
column4,
sum(column5),
ABS(column5)
from table1
where column1 = 'sample1'
group by
column1,
column3,
column4;


i get the error:
you tried to execute a query that does not include the specified expression 'column2_trim' as part of an aggregate function.

can you please point me to what im doing wrong? thanks.

[lfpm062010]

Suggestion Not tested.

Change count(column2_trim) to count(column2).

[Fais]

Hi, i have tried that as well, it still doesn't work.

[lfpm062010]

Did you try group by trim(column2)?

[Fais]

Hello, i am trying to achieve the following in Access 2010. Please assist.

Open date is calculated based on order date and ship date, how can i put together a query which achieves this output?

Open_Date	Count	Amount	Absolute_Amount
0 -> 30	0	0.00	0.00
31 -> 90	3	33359	33359
91 -> 180	1	(4773)	4773

[June7]

What is name of the query were Open_Date is calculated?

SELECT Open_Date, Count(*) AS CountRecs, Sum(Amount) AS SumAmt FROM queryname GROUP BY Open_Date;

[Fais]

Thank you for the answer, i want all of this calculated in one query. so 1 query should be able to generate the above table, how to do that?
'0 > 30'
'31 >90'
'91 > 180'

[June7]

Maybe:

SELECT {calculation} AS Open_Date, Count(*) AS CountRecs, Sum(Amount) AS SumAmt FROM queryname GROUP BY {calculation};

Substitute your expression in place of {calculation}.