We help IT Professionals succeed at work.

How can I count number of a certain field value per month?

I have an Access database with almost 3 million records and I have little experience summarizing data in Access. There are 6 fields per record. Field A has dates in YYYY-MM-DD format and Field F has a numeric value or is blank. I would like to find out how many times the value 59 appears in Field F of a record in a given month, as well as the total number of records in each month. Your help would be greatly appreciated.
Comment
Watch Question

Nick WolfEverything IT

Author

Commented:
I should add that I am using Access 2003. I can use Access 2010 if this process would be easier with that version.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
version does not matter.

if the field of interest is an integer or long integer, you could do something like:

SELECT Format([DateField], "yyyy-mm") as YrMo
          , Count(*) as RecCount
          , Sum(iif([Field F] = 59), 1, 0) as Count59
FROM yourTable
GROUP BY Format([DateField], "yyyy-mm")

If the datatype of [Field F] is decimal, double, or single you might have difficulty with an exact
comparison.  In that case you might do something like:

SELECT Format([DateField], "yyyy-mm") as YrMo
           , Count(*) as RecCount
           , Sum(iif(Abs([Field F] - 59) < 0.005, 1, 0)) as Count59
FROM yourTable
GROUP BY Format([DateField], "yyyy-mm")

Dale
Nick WolfEverything IT

Author

Commented:
What am I doing wrong? First three lines are red, it highlights Format and says "Compile error: Expected: Case."

SELECT Format([SVC Date], "yyyy-mm") as YrMo
          , Count(*) as RecCount
          , Sum(iif([PROC_CD Mod] = 59), 1, 0) as Count59
FROM Combined
GROUP BY Format([SVC Date], "yyyy-mm")
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Did you try this in a query, or a VBA function?

Is the [SVC Date] field a text or date datatype?  I know you said it is formatted as "YYYY-MM-DD" but that could be a display format for a date field, or it could be the way it was written as a text field.  The format function call should work either way, which is why I'm inclined to think that you tried this in VBA, not as the SQL in a query.

Dale
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
nicholasjwolf,

Perhaps if you posted some sample data, and the exact results you are looking form,...this would be clearer
Nick WolfEverything IT

Author

Commented:
You're right, I tried as a VBA funtion. All fields are "Text" and when I try to change to date or integer I get an error that there is not enough memory or disk space. I tried in a query and received "Wrong number of arguments used with function in query expression 'Sum(iif([PROC_CD Mod] = 59), 1, 0)'"
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Sorry,  I had one of the ) in the wrong position.  Try:

Sum(iif([PROC_CD Mod] = 59, 1, 0))'"