Grouping

[Guest]

I hv data in a table as below

Type Amt
------ -----
1 100.00
2 150.00
1 200.00
3 300.00

I want to group say type 1 into group A, 2 into group B and 3 into group C
and i expect to get a summary as below

Group A 300.00
Group B 150.00
Group C 300.00

How should i go about it, do i need to create new field, what expression
should i use to get the sum amt as above

Thanks You

 

[Brendan Reynolds]

A query such as this will do it ...

SELECT Choose(NZ([Type]),"Group A","Group B","Group C") AS Expr1,
Sum(tblTest.Amt) AS SumOfAmt
FROM tblTest
GROUP BY Choose(NZ([Type]),"Group A","Group B","Group C");

Change 'tblTest' to the name of your table. You can also change 'Expr1' and
'SumOfAmt' to the headings you want to display.

Here's a link to the on-line help topic on the Choose function ...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctchoose.asp