Count records based on part of field only

[bigalpha]

We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.

example:
For today, we would use 4085-0001, 4085-0002, 4085-0003, etc.
For yesterday: 4084-0001, 4084-0002, etc.

I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
4084-0001
4084-0002
4081-0000
4078-0000
4078-0001
4078-0002

I tried formatting the source field with Left -4, but it only shows the first four characters in the result:
4084
4084
4081
4078
4078
4078

How can I count the number of records each day based on only the first four characters? Example:
4084 = 2
4081 = 1
4078 = 3

 

[jdraw]

Why do some sequential numbers start at 0?

SELECT Left(yourFieldName,4), Count( Left(yourFieldName,4))
FROM YourTableName
GROUP BY Left(yourFieldName,4)

 

[bigalpha]

Why do some sequential numbers start at 0?

SELECT Left(yourFieldName,4), Count( Left(yourFieldName,4))
FROM YourTableName
GROUP BY Left(yourFieldName,4)

Thanks, I will try this. The 0000 is a typo. Should read 0001.

 

[bigalpha]

Thanks, your solution worked perfectly. I didn't know that I could structure a query like this. Super helpful!

 

[Galaxiom]

How can I count the number of records each day based on only the first four characters? Example:
4084 = 2
4081 = 1
4078 = 3

The requirement to deal with one section of the string strongly suggests that the two parts should be held in separate fields in the table.

 

[bigalpha]

The requirement to deal with one section of the string strongly suggests that the two parts should be held in separate fields in the table.

Yeah, I thought about that too. Until yesterday, the entire string was basically only used as a unique tracking number. Now there is a requirement to show how many numbers (the sequential part) were created on each day for each week.

 

[Galaxiom]

Would there be a date field too? If so it could be used to get that information.