Query Help

[Chad]

I have a table that contains a date field. The date is
formated as such (mm/dd/yy) Is there a query that I
could create that would COUNT the total number for each
month? If so could you please point me in the right
direction?

Thanks,
Chad

 

[Duane Hookom]

It makes no difference how your date field is formatted. A date is a date is
a date. To count records by month
SELECT Format([DateField],"yyyymm") as YM, Count(*) as NumOf
FROM tblYourTable
GROUP BY Format([DateField],"yyyymm");

 

[Chris Kennedy]

Hi,

Thank you for using the Microsoft Access Newsgroups.

You wrote:
"I have a table that contains a date field. The date is formated as such
(mm/dd/yy) Is there a query that I could create that would COUNT the total
number for each month?"

I take that your table is structured something like this:

ID DateField
1 02/05/03
2 02/15/03
3 02/20/02
4 07/15/03

Then you could do something like this in your Query:

Field: Expr1: Format([DateField],"mm") ID
Total: GroupBy
Count

Results
============
Expr1 CountOfRecord
---------- -----------------------
02 3
07 1


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."