Customers served by month in last 12 months 1

[PhoenixDon]

A clerk in my organization wants a report that shows how many customers she serviced in the last month. She doesn't want the current month, just the previous 12 months, regardless of year. I don't want her to have to enter anything. Am I going to have to right a loop and capture the current date, then DateAdd to each month? Something like this but without the loop.

SELECT Count([Customer Log].Name) AS CountOfName
FROM [Customer Log]
HAVING ((([Customer Log].[Date Seen])>=#1/1/2007# And ([Customer Log].[Date Seen])<=#1/31/2007#));


Suggestions appreciated.
Don in Phoneix

 

[SgtJarrow]

SELECT Count([Customer Log].Name) AS CountOfName, Format([Date Seen], "mmmm")
FROM [Customer Log]
HAVING ((([Customer Log].[Date Seen]) Between DateAdd("m", -12, Date()) And Date()))
GROUP BY Format([Date Seen], "mmmm");

Should give all records in the past 12 months and have the grouped by month.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

Got the Group By line in the wrong spot...should be:

SELECT Count([Customer Log].Name) AS CountOfName, Format([Date Seen],"mmmm") AS Expr1
FROM [Customer Log]
GROUP BY Format([Date Seen],"mmmm")
HAVING ((([Customer Log].[Date Seen]) Between DateAdd("m",-12,Date()) And Date()));


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

Man...having a bad day. This one worked against a test db I created.

SELECT Count([Customer Log].Name) AS CountOfName, Format([Date Seen],"mmmm") AS Expr1
FROM [Customer Log]
WHERE [Customer Log].[Date Seen] Between DateAdd("m",-12,Date()) And Date()
GROUP BY Format([Date Seen],"mmmm")

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PhoenixDon]

Sweet! I thought for sure I was going to have to write more code than that. Great tip ... star worthy!

 

[SgtJarrow]

Reviewing this, there may be one issue you need to confirm. Depending on the day this is run, the number for the current month may not be correct. This is because, for example, today is February 8. All records for February of this year before February 8 will be in February's count. But so will all records in February of last year from February 9 to the end of the month. In order to fix this, if you need it use this:

SELECT Count([Customer Log].Name) AS CountOfName, Format([Date Seen],"mmmm yyyy") AS Expr1
FROM [Customer Log]
WHERE [Customer Log].[Date Seen] Between DateAdd("m",-12,Date()) And Date()
GROUP BY Format([Date Seen],"mmmm yyyy")

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PhoenixDon]

Yep, that will work. Now the final part of the puzzle ... sorting from latest to ealiest. I tried this:

SortMonth: Format([Date Seen],"m")

and made it the first field displayed, sort ascending, but is interpreted as text rather than a number.

 

[SgtJarrow]

This includes the year code stuff I mentioned in my last post and sorts properly.

SELECT Count([Customer Log].Name) AS CountOfName, Format([Date Seen],"mmmm yyyy") AS Expr1
FROM [Customer Log]
WHERE ((([Customer Log].[Date Seen]) Between DateAdd("m",-12,Date()) And Date()))
GROUP BY Format([Date Seen],"mmmm yyyy"), Year([Date Seen]), Month([Date Seen])
ORDER BY Year([Date Seen]), Month([Date Seen]);

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PhoenixDon]

Perfect! Thanks much.