Total transactions by month

  • Thread starter Thread starter FJB
  • Start date Start date
F

FJB

I have searched the newsgroup and do not find exactly what I am looking
for so I thought I'd ask a very elementary question.

There table has 3970 records for the time period January through July
2005. I would like to know how many records per month. I have separated
the date field into month and year expressions, but cannot seem to get
the seven numbers I need. Any help would be greatly appreciated.

Thanks
 
FJB said:
I have searched the newsgroup and do not find exactly what I am
looking for so I thought I'd ask a very elementary question.

There table has 3970 records for the time period January through July
2005. I would like to know how many records per month. I have
separated the date field into month and year expressions, but cannot
seem to get the seven numbers I need. Any help would be greatly
appreciated.

Thanks
Click to expand...

SELECT Format(DateFieldName, "yyyymm") AS YrMnth, Count(*) as Qty
FROM TableName
GROUP BY Format(DateFieldName, "yyyymm")
 
I entered the first line as "SELECT Format(Date, "yyyymm") AS YrMnth,
Count(*) as Qty" (Date is the DateFieldName) and receive an error
message "The syntax of the subquery in this expression in incorrect". I
failed to mention I am using Access 2002 (XP) if that makes a
difference.
 
FJB said:
I entered the first line as "SELECT Format(Date, "yyyymm") AS YrMnth,
Count(*) as Qty" (Date is the DateFieldName) and receive an error
message "The syntax of the subquery in this expression in incorrect".
I failed to mention I am using Access 2002 (XP) if that makes a
difference.
Click to expand...

"first line"? What I posted you would paste into the SQL view of a new query
and then change the names of the fields and table to match yours.

Also, you should change the name of your field "Date" as that is a reserved word
in Access. As written Access would likely think you meant the function named
"Date" rather than your field named "Date".
 
Back
Top