Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unanswered: Statistics from One Table?

    I am trying to summarize and count specific information from one table. Can I do this in one query?

    I want it to return a sum of all the numeric values in COL A.
    A count of all the text = Pending in COL B
    A count of all the text = Information in COL C

    When I structure the query, I get only a count of COL A and it seems to ignore COL B.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What does your query look like now? I see iif() statements in your immediate future...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Hey Teddy, thanks, you may be right, but I made more of a mess with the iif statements!

    Here is the SQL:
    SELECT Count(Shipped.POUNDS) AS CountOfPOUNDS, Count(Shipped.[SHIP SPECIAL]) AS [CountOfSHIP SPECIAL], Count(Shipped.TRANSPORT) AS CountOfTRANSPORT
    FROM Shipped
    HAVING ((Shipped.[SHIP SPECIAL]="Pending") AND (Shipped.TRANSPORT="Information"));

    I am expecting it to return something like this:

    CountofPounds=12,000
    CountofShippedSpecial=200
    CountofTransport=390

    but I get either all zeros or 12,000 in EACH of the above, obviously only looking at the first of my criteria....

    I even thought of just a form with the statistics from this table,
    would it be in a TEXT box? with an iif statement in each text box as you suggested? If that is better solution, tell me how!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That makes sense. Count returns the number of records for a given field. Take a look at your "HAVING" clause. You are counting the number of records which have [SHIP SPECIAL] = "Pending" AS WELL AS Shipped.TRANSPORT = "Information". you are getting the number of records that match both criteria, naturally the number of records returned is going to be the same for each field.

    Try something more like this:

    SELECT SUM(shipped.pounds), SUM(iif(shipped.[ship special] = "pending", 1, 0)) AS [CountOfSHIP SPECIAL], SUM(iif(shipped.transport = "Information", 1, 0)) AS CountofTRANSPORT

    We're talking three seperately aggregated values here, you have to present it as such.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2003
    Posts
    300
    Yes! That did it.. Thanks!

    I also need the actual ROW count or total records in that table but I guess I would have to make a seperate query for that..

    again-thanks

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Or you could fool access into giving you a value in a field for every record and count against that... count(nz([somefield], "forced value"))
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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