Business Solutions:  Counts in Access: How Many Veterans In The File?

        by James D. Duncan, CPA   May 2005

           
      In the current project, I have many opportunities to count the number of records that meet certain criteria. One of the questions is “how many persons in the file have veteran status?” Simple enough question. Open a new query, select the design view, double-click on the Table that has the information you want to count and close the “Close the show Table” window.
       Double-click on the field you want to count. In this example, that is a field named NsoVeteransStatus. Double-click on it again so you have it on the grid twice. Next I double-clicked on the NsoEntryDate field because the date is restricted to being between 07/01/2004 and 03/31/2005. You may want the whole file, I just needed between those dates.
       In the second field, enter “=y” in the criteria line and click on the “show box” to remove the field from showing. In the criteria for the date, I entered “Between #7/1/2004# And #3/31/2005#”. This will only select the records between these dates. Later, I plan to make this called from a form and will use the date entered in the form. Click on the “Show criteria” on the second and third columns so that these do not show on the query.
      You should have a screen that looks similar to the screen below.

 

       Now click on the Totals icon on the tool bar. Next change the “Total” field on the first column to read “Count”.

 

      Change the “Total” on the first column to read “Count” and on the third field to read “Where”.
Now run the query and you get your answer.

 

This computes the count of the number of records that have the veterans status checked in the master record. This works for any field for which you need to know how many records there are that meet the criteria.
      You can copy the SQL to a form for a field in a report like last month or just print the computation and write in the answer on the questionnaire. You can get as fancy as you have time for.

Take some time and volunteer to help the community. The United Way Agencies and their recipients are a great place to start. They need the help and you get the Access practice. I want to give a special thanks to Mrs. Chamberlain for her patience on this project. Her hours and devotion to her job and those in her care are way above and beyond the call. These wonderful people are few and far between.

 

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James.