Results 1 to 2 of 2
Thread: Record Counts (97/2000)
2001-07-13, 11:40 #1
- Join Date
- Jun 2001
- Kenton, Delaware, USA
- 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.
2001-07-13, 13:23 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- 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
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