Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recurrences of values in a field (2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recurrences of values in a field (2000)

    You asked a similar question in <post#=278195>post 278195</post#>. You can do this kind of thing in Totals queries.
    <UL><LI>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.
    <LI>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.
    <LI>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?

Posting Permissions

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