recurrences of values in a field (2000)

Topic

New Reply

I am building a query based on a table, and I want to count the recurrences of a certain value in a field…is there an expression for this? also I’d like to be able to count the number of records being queried so I can do a percentage calculation. For example, what percentage of such-and-such field’s entries are “Yes.” Any way I can do this? Thanks.

Reply | Quote

Replies

You asked a similar question in post 278195. You can do this kind of thing in Totals queries.

• First, create a query based on the table. Add one field (any field) of the table to the query grid. Make the table into a Totals query by selecting View | Totals or by clicking the Totals button on the toolbar. Change “Group By” in the Totals row to “Count”. Give this field “NumberOfRecords” as alias by typing NumberOfRecords: before the field name. Close and save this query.
  If you run this query, it will show the number of records.
• Next, create another query based on the table.. Add the field you want to count the number of entries of to the query grid, and add another field (any field) to the query grid. Make this query into a Totals query too. Leave the Totals option of the first field as “Group By”, and change that of the second field to “Count”. Give the second field “Occurrences” as alias by typing Occurrences: before the field name. Close and save this query.
  If you run this query, it will show the number of occurrences of each value of the first field in the query.
• Finally, create a new query based on the two queries you just created (you must switch to the Queries tab of the Add Table dialog to do so). Add the field you grouped by from the second query, and a calculated field
  Percentage: [Occurrences]/[NumberOfRecords]
  With the cursor in this calculated field, activate the Properties window (View | Properties), and set the Format property to Percentage.
  Close and save this query.
  [/list]May I suggest again that you study a book on the basics of Access?

Reply | Quote