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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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")
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")
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
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
nicholasjwolf,
Perhaps if you posted some sample data, and the exact results you are looking form,...this would be clearer
Perhaps if you posted some sample data, and the exact results you are looking form,...this would be clearer
ASKER
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)'"
Sorry, I had one of the ) in the wrong position. Try:
Sum(iif([PROC_CD Mod] = 59, 1, 0))'"
Sum(iif([PROC_CD Mod] = 59, 1, 0))'"
ASKER