DCount in Totals Query!

[Caryisms]

Can someone help me write a DCount function in a totals query? I want to count the number of times 5 values (1, 2, 3, 4, 5) appear in a field grouped by an ID(another field). Thanks!

 

[rac2]

Don't see why you need DCount for this.


SELECT idColumn, COUNT(*)
FROM aTable
WHERE numberColumn BETWEEN 1 AND 5
GROUP BY idColumn

or

SELECT idCol, numberColumn, COUNT(*)
FROM aTable
WHERE numberColumn BETWEEN 1 AND 5
GROUP BY idColumn, numberColumn

depending on whether you want a breakdown of ids by the five values.

 

[Caryisms]

Thanks rac! I need to roll up respondents of a survey per center and count the number of times a value appears for reporting. Can you explain a point and click solution?

 

[rac2]

Oh boy, that is not so easy. I'll try.


Create a new query in design view. Select the table. Drag the idColumn and numberColumn into the design grid. Drag the numberColumn into the third column and again into the fourth column.

On the tool bar there is an icon I would describe as a capital sigma, when you mouse over it says Totals. Click that and a new row will appear in the design grid for Totals. Each column has a cell with the words Group by. Click in that cell under the second numberColumn, that is in the third column. You will have a drop-down menu, select Count.

In the fourth column, Totals row click the drop-down menu and select Where. Uncheck Display. Right click in the cell on the Criteria row to bring up a context menu. Select Build... with the magic wand icon. Say the magic words and shazamm! Sorry, I couldn't resist.

Select Build. This brings up a dialog box named Expression Builder. Double-click tables. Select the table. Select the numberColumn. Click paste. Click on Operators. Select Comparison, select Between. Click paste. Now you must edit the expression. Replace the <<Expr>> with 1 and 5. Click OK.

Close and save.

That is what I love about the phrase, &quot;Just point and click.&quot;