Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record Counts (97/2000)

    Excel has several great functions called COUNTA, COUNTIF, COUNTBLANK. Does access have similar functions somewhere?

    Currently I am passing Queried data to excel to perform these functions.
    For instance I need to count how many yes's are in columns for Slurred Speech and Agitated Behavior and several other fields, for a subset of records filtered for a date criteria ( Between 1 Jan 01 and 30 May 01 for instance) and a Field criteria (where O2 levels were less than 95%,a yes or no field).
    I use the use a make table query (because I can't pass interactive query parameters onto excel) to create the subset. I then use an excel workbook and data queries(saved as SQL qry) to link the tables to the worksheet with a refresh capability. On the worksheet I do my counts then make a pivot table and return the information to my reports through a paste link to the worksheet.
    On a related topic I want to be able to pass the "Between Dates" as part of the Report title to identify the report date range. I do one big bunch of these types of reports and I have had to hand craft each report individually.

    Can anyone guide me?
    PS I can use a query to count total records but I need to count records that match different criteria in several different columns and still include all of the records.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Counts (97/2000)

    In Access you do it in SQL rather than with a function. The way I usually do it is to create an expression for each count I want and then sum them in a second query. In other words, Query1 would have expressions like

    SlurredSpeechCount: Abs([SlurredSpeech]=True)
    AgitatedBehaviorCount: Abs([AgitatedBehavior]=True)

    What that does is return a 1 or a zero in each row for each of those expressions, assuming of course that your fields are Yes/No fields rather than text.


    Then Query2 would be a groupby query based on Query1 and would sum SlurredSpeechCount and AgitatedBehaviorCount.

    In Access 2000, you can create pivot tables in Access itself, although they're apparently harder to manipulate there (I haven't tried it myself--no need for them).

    As for your other question, take a look at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=50206&page=&view =&sb=&o=&vc=1>this thread</A> and see if that helps.
    Charlotte

Posting Permissions

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