Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    66

    Unanswered: how to count different value in a cloumns in a report

    i have a report which contains Status cer which have value like

    OK
    NG
    CA
    SCRAP
    PENDING
    BREAK DOWN
    MISSING

    i want total number of "ok", "ng" "ca" ...
    at the end of the report if i use IIf function iam not able to get the sum of the required items

    i used this formula using a text box but i did not get the sum it displaced the
    text =COUNT([STATUSCER]) i think it have taken it as a string

    IIf([STATUSCER]="OK","=COUNT([STATUSCER])"

    pls guide me
    Attached Thumbnails Attached Thumbnails code.JPG  

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    In the query that the form is based on add a new field like this

    OK_Count:IIF([STATUSCER]="ok",1,0)

    Then in the relevant form text box use =Sum([OK_Count])

    Repeat for "ng" and "ca"

    HTH


    MTB

  3. #3
    Join Date
    Sep 2011
    Posts
    66

    Thank you MTB

    thanks for the help i got the report is it possible to do in vba code because i have some 15 different datas like this in a one filed which has to be counted at the end of the report

    can you suggest any idea

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would probably use a subreport based on a totals query:

    SELECT Status, Count(*) AS HowMany
    FROM TableName
    GROUP BY Status

    Perhaps with the same criteria used to filter the main report, if applicable. This method would be more dynamic.
    Paul

  5. #5
    Join Date
    Sep 2011
    Posts
    66

    pls guide iam learning now

    i have attached my database can you guide me how it is done as iam new
    and suggest me any material where i can learn using pdf of other links because i think i will disturb a lot to dbforums site user posting too concerns


    name of report : plan data
    filed name :statuscer
    query name : IMTE MASTER REPP
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can do this in two ways
    one is by running a separate query at the end of the report that populates some controls, or even as a sub report

    the other is to perform your count as you generate the report.

    I don't think there is a special preference for either solution.

    if it was me I'd probably use some VBA... probably because I am a jaded developer who has been using Access since V2 way back when.

    your SQL woudl be something like
    SELECT status, count(Status) as NoItemsByStatus
    FROM [IMTE MASTER]
    group by Status;

    incidentally I think you need to revisit your design
    you are going to have problems with tables if you allow users to enter text values for things like status. a far better approach is to have another table.. say Status Types which has an ID and a description and use that ID column as the foreign key in ITME whatever. define a realtionship between ITME whatever and thje new table and enforce RI
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2011
    Posts
    66

    healdem i will get back by doing changes in design

    healdem i will add a table statuscer a prepare a query and change design and get back to you pls guide me later

  8. #8
    Join Date
    Sep 2011
    Posts
    66

    healdem done changes in thread

    Report based on 3 fields consolidation

Posting Permissions

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