Group by prefix characters, then sum another column

[Williams]

Does any one know how to use the Totals feature to group by the first
character in a field, then sum the values in another column that share a
common prefix? I need to do this in a query.

I have one column that has codes such as P001, P002, P003, U001, U002, U003.
I want to group all of the 'P' records together, then sum another column. I
want to do the same for the 'U' records.

help is appreciated.

 

[Marshall Barton]

Does any one know how to use the Totals feature to group by the first
character in a field, then sum the values in another column that share a
common prefix? I need to do this in a query.

I have one column that has codes such as P001, P002, P003, U001, U002, U003.
I want to group all of the 'P' records together, then sum another column. I
want to do the same for the 'U' records.

You need to use a calculated field to get the first
character, then you can group on it:

SELECT Left([a field], 1) As firstchar,
Sum([another column]) As Total
FROM [some table]
GROUP BY Left([a field], 1)

 

[John Spencer]

You would use
Left(SomeField,1)
as a calculated field and group on the calculation. You could not include the
field in the query or that would defeat the grouping on just the first character.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County