Totals if fist 2 character match.

[dbazzelle]

Hello I have some data that looks something like this:

00text
00text
01text
03text
etc...

text=any alpha numeric value.

what i need to do is a total (count) of those values if the first two charecters match.

in this example i would need:

00=2
01=1
03=1

Thanks,
David

 

[Juan Pablo González]

I think something like this works:

SELECT Left(Field, 2), COUNT(*) FROM TheTable GROUP BY Left(Field, 2)

 

[CT Witter]

Go Into the Design view of the query. Add the Table that contains your data. Click on the sigma (E) symbol to add the total line to the grid.
Place this in the first grid.

Field: Field2: Left([FieldwithData],2)
Total: GroupBy

Place this in the second grid:

Field: Count: [Field2]
Total: Count

In SQL it is:

SELECT Left([FieldwithData],2) AS Field2, Count([Field2]) AS [Count]
FROM Table
GROUP BY Left([FieldwithData],2);


HTH,
CT