Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Major Design Issue

    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!!

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    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:
    Code:
    SELECT [Acct Status], Count([Acct Status]) as CountOfAcct_Status
    FROM Table1
    WHERE ([Acct Date] BETWEEN #1/1/2004# And #1/30/2004#)
    GROUP BY [Acct Status];

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    I got that to work but I'm afraid I hit a road block. Here is what my query looks like.

    Date_Sold Acct Status CountMvp
    8/27/2003 3 1
    8/27/2003 3 1
    8/28/2003 dfw
    8/29/2003 3 1
    8/29/2003 3 1
    8/29/2003 4
    9/2/2003 3 2
    9/3/2003 3 2
    9/3/2003 wpb

    This is what I want. I wrote this function in a report to Count all of the 1's in the CountMvp Column.

    =Count([CountMvp] Like "1") but for some reason I get the wrong number. Is that the right format to count on a report?

  4. #4
    Join Date
    Oct 2002
    Location
    Florida
    Posts
    7
    There is something wrong with your Group Query. You should not be geting duplicate values. example;

    Date_Sold Acct Status CountMvp
    8/27/2003 3 1 HERE
    8/27/2003 3 1 AND HERE
    8/28/2003 dfw
    8/29/2003 3 1 HERE
    8/29/2003 3 1 AND HERE
    8/29/2003 4
    9/2/2003 3 2
    9/3/2003 3 2
    9/3/2003 wpb

    What is the exact SQL Statement you are running?

    Give about 10 Lines of unalter data also would help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •