Web Development



Count parts of a string in Access

By zdjbel ·
I am a just a little more than a novice in Access but I can't figure this one out: I have a table in Access where one field (text) of a table contains document numbers. Each entry (a combination of 12 letters and numbers) contains a document type identifier separated by "-", e.g. -A-, -M-, -Y-, etc. I need to put together a query which will give me the total number of documents for each of the 8 document types. Can anyone help me?

Thank you,


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

first attempt

by john.a.wills In reply to Count parts of a string i ...

SELECT count(*),substr(something to find the document type) AS doctype
FROM TheTable
WHERE doctype IN (SELECT DISTINCT substr(...)
FROM TheTable)
GROUP BY doctype;
Try to translate this into the circumstances of your application.

Collapse -


by Maevinn In reply to Count parts of a string i ...

Where in the string is the document type? If it is consistantly say, the 3rd character, you can extract just that using Mid([fieldname],characterposition, characterlength) and count based on that.

Alternatively, I'd suggest adding the document type as a field. It sounds as though it was denormalized a tad too far, since you want to be able to use this value on it's own.

Collapse -

From email exchange

by Maevinn In reply to Placement?

This is based on the consistency of the second and third groups (ie, doesn't matter how many characters are before the first '-' as long as the document type is always the fourth character after the first '-'.) InStr () is being used to locate that first '-' and then Mid() counts over and returns the character. Count() then gives the count for each unique value.

On the test data, this worked.

SELECT Mid([documentNo],InStr([documentNo],'-')+4,1) AS docType, Count(Mid([documentNo],InStr([documentNo],'-')+4,1)) AS docTypeCount
FROM test
GROUP BY Mid([documentNo],InStr([documentNo],'-')+4,1);

Replace documentNo with your column name, and rename the As docType/As docTypeCount, and the From table name, of course.

Please let us know if this works. :)

Collapse -

from the email I received...

by john.a.wills In reply to From email exchange

Maevinn's suggestion is not quite right because it does not include the year, but the query is easily adaptable for that. Create a query in Access, and use the SQL entry page. Please use the Forum rather than email to communicate further difficulties. If it works give one of us a thumb by clicking the button "this was helpful".

Collapse -


by Maevinn In reply to from the email I received ...

huh? Where was that mentioned?

Collapse -

I figured out the year filter...

by zdjbel In reply to Year?

I hope this is the correct channel of communication. I played around with Maevinn's suggestion and got exactly what I was after:

SELECT qWPAccessRptCount3.FY, qWPAccessRptCount3.PrcActionType, Sum(qWPAccessRptCount3.PrcActionCount) AS SumOfPrcActionCount
FROM qWPAccessRptCount3
GROUP BY qWPAccessRptCount3.FY, qWPAccessRptCount3.PrcActionType
HAVING (((qWPAccessRptCount3.FY) Like [Which FY? Enter single FY digit only]));

Thank you both for my first lesson in SQL.


Related Discussions

Related Forums