Count: multi-field count

[Steve Scheckel]

Hello there,

I'm a rookie user and needing some help. I need to
develop a counter that counts based upon the input of
three fields and yields the result in the forth field:
Course Name, Course Date, Course Time. Last field would
be the counter. (e.g., Class A, on 1 July 03, @ 1400
hours = number registered) I should be able to answer the
question: "How many students do I have registered
for "Class A" on 1 July 03 at 1400 hours.

I realize that this is easily accomplished in reports;
however, our data entry person needs this counter on a
pop up form; so, that he can track the counter as he
makes entries.

Thanks for any help w/ this.

 

[valentine]

You can do this either in Design View or SQL view of you
query. Given design view is easier:

Create New Query
Add all three fields from the database
Click the "Totals" button on the toolbar.
Select groupBy in "Total:" row in the details for all
fields but the one you want to count.
Select "Sum" in "Total:" row for the number of students
field.
Run the query.

or here's the SQL:

Select ClassField, DateField, TimeField, Sum
(NumberOfStudentsField) from YourTable group by
ClassField, DateField, TimeField;

Replace the fields with your own field names

Hope this helps.

 

[Michel Walsh]

Hi,


A total query? Click on the summation button on the toolbar, the one with a capital Sigma, a kind
of M rotated by 90 degree, and a new line, total, appear in the grid.

Drag each of the three fields in the grid, keep the proposed GroupBy.

Drag any of the field (again) in a fourth column, this time, change the GroupBy to COUNT.

That's all.


Hoping it may help,
Vanderghast, Access MVP