Here is what I am faced with. I am trying to create a matrix from one column in a table. Here is the detail. The one column, named Acct Status, has 13 differentiating values for words. Ex. the value of 13 would equal CLP3, the value of 4 would equal extension. So i have a bunch of numbers 1-13 in this one column. I need to COUNT not sum these numbers based on a calander year. Ex. there were 4 #12's in the column between 1/1/2004 and 1/30/2004. I need to do this for 13 different numbers. I also need to show it in a report. I could do 156 count function but that seems a little much. What is the best design for this type of work. Hope someone out there knows a better way b/c this could get ugly. Thanks!!
Assuming you have a table named Table1, with at least two fields, Acct Date and Acct Status, and you want the counts for the dates between 1/1/2004 and 1/30/2004, inclusive, try a query something like this:
SELECT [Acct Status], Count([Acct Status]) as CountOfAcct_Status
WHERE ([Acct Date] BETWEEN #1/1/2004# And #1/30/2004#)
GROUP BY [Acct Status];