How to group by date? 1

[sabavno]

Hi,

I have the following data in my table:

DateCreated AssetType LastName
10/31/02 1 K
10/30/02 1 L
10/29/02 1 J
10/29/02 2 P
09/29/02 1 K
09/29/02 2 J

How could get the following result?

Date AssetType Count
October, 2002 1 3
October, 2002 2 1
September, 2002 1 1
September, 2002 2 1

Please help me group this stuff.
Thanks.

 

[SgtJarrow]

Set the date field in the query to:

Format([DateFieldName]), "mmmm yyyy&quot

Leave it as GroupBy
Leave AssetType as GroupBy
Set LastName to Count
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[jebry]

Hi!

You can do this in SQL:

Select Format([DateCreated], "mmmm, yyyy&quot As MonthYear, AssetType, Count([LastName]) From YourTable Group By Format([DateCreated], "mmmm, yyyy&quot, AssetType

hth
Jeff Bridgham
bridgham@purdue.edu

 

[SgtJarrow]

Spot on Jebry! Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[sabavno]

Thanks a lot guys!