Hi http://www.access-programmers.co.uk/ubb/smile.gif

I've got a query which gives me output like:

Stage CountofStage
1a 5
1b 3
2a 6
2b 4
3a 2

Instead of this I want an output in a grouping manner like

Stage CountofStage
1 8 (5+3)
2 10
3 2

i think that maybe Substring fuction will work..but dont know how to...n one thing is that Stage will never be greater than 9 like u'll never have a stage like 10a

Cheers!
Aqif

To begin with, you should reconsider your table design. Since the number and letter parts of Stage are separate data items they should be stored in separate columns. Combining multiple pieces of data in a single field is a violation of First Normal Form. There are several articles on database normalization available from the Microsoft support website. Reading them will help you with future design descisions.

To work with what you have, you can use the Val() function to extract only the numeric portion of the Stage field.

Select Val(Stage) As NumStage, Sum(CountOfStage) As SumCount
From YourTable
Group by Val(Stage);