Using MS Access/Access "Count" function


I have about 1300 length measurements of fish (taken in millimeters) as part of a data project and entered into Access.  One of the eventual goals is to create a bar graph (using Excel) of the number of fish in each 2 cm group.  Using Access's Integer function I've taken the length data and set up the appropriate 1 cm and 2 cm group for each entry.


450 mm = 45 cm gp (1 cm) or 44-46 cm gp (2 cm gp)

The Access query columns have a data ID #, fish weight (grams), fish length (mm), fish 1 cm gp, fish 2 cm gp.  Typical entries are:

100, 2520, 450, 45, 44
101, 2620, 475, 47, 46
102, 3110, 522, 52, 52 etc.

Now I want to count the number of fish in each 2 cm gp so that my data looks like:

30cm: 27
32cm: 36
34cm: 41, etc.  

The data low end would be 200 mm (20 cm gp) and go to about 660 mm (66 cm gp)

Is there a single command or equation that will provide a count for each of the categories? I guess I don't understand the COUNT function options very well, because so far my only solution has been to make a report, sorting by each group and then using COUNT for that group.  This would mean manually reentering each group data into the Excel spreadsheet -- it would almost be easier to simply make a manual count from the original query, except that additions or changes would be awkward.

This should be very easy with a Group By query. In Query Design mode, First add your 2 cm group field to the grid, then your DataID field. Press the Sigma icon on the ribbon to turn the query into a Group By. This adds a Group By row to he grid. Set the first (group) column to Group By and the second to Count. Run the query and you will get a count for each group.

Hope this helps,

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.