Counting repeated items withing a recordset.

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I want to be able to count the number of times the same
key field shows up on a recordset created by a query for
data analysis purposes. (I need to know how mahy times we
use a particular vendor within an inputted date range.) I
believe this can be accomplished by ordering the records
by ascending/descending key field values, and then
incrementing a counter as I move down the recordset until
a new key field value is found, rinse and repeat until
EOF. But I was wondering if there was a simpler and/or
more efficient way to accomplish this. Any advice would
be appreciated.
 
You could do this in query based on your original query. The SQL might look
something like this:

SELECT
[Your Original Query].[Your Key Field],
Count(*) AS [Frequency]
FROM
[Your Original Query]
GROUP BY
[Your Original Query].[Your Key Field]
 
You could create a query with a Grouping on VendorName and a Count of
VendorName ...

With your query opened in Design View:

1. Drag the VendorName field to the first row of the first column
2. Right click anywhere in that column and select Totals from the popup
menu
3. You will see a new row added to the query's design: Total: - the
Total: row for this column will say "Group By"
4. Drag the Vendor Name field to the next column
5. In the Total: row, select "Count"
6. To set criteria, drag your date field or fields to new columns and, in
the Total: row, select "Where"
7. Insert the date criteria you desire.


hth,
 
Back
Top