469,126 Members | 1,312 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Calculate count of records with a particular property on a form

52
Hi! I'm sure that this is easy, but I just can't solve it. Could you please help?

Access 2010

I have a form that displays all students in my program and all classes that they take, kind of like this:

STUDENT ID CLASS MARK

All I want to do is create a calculated field that displays the total number of classes that student is taking, like this:

STUDENT ID #Classes CLASS MARK
John Doe 2 Math 30 88
John Doe 2 English 30 67
Jane Deer 1 Physics 30 99

and so on. I tried using the COUNT function (it says 1 each time, as these are not grouped), then I tried to do this in the query (same thing), then I tried to first COUNT them SUM them in the query (complains about subqueries and aggregate functions).

Any ideas? Thank you in advance!

Melody
Dec 4 '12 #1

✓ answered by Rabbit

You can either use the DCount() aggregate function or you can join to a subquery containing counts.

5 1489
Rabbit
12,516 Expert Mod 8TB
You can either use the DCount() aggregate function or you can join to a subquery containing counts.
Dec 4 '12 #2
zmbd
5,400 Expert Mod 4TB
Try as Rabbit has suggested. If you're still banging-your-
head-on-the-wall... we'll get thru to the other side of it!

Another hint: You can't use multiple fields in an aggreate query when trying to do a count.
Dec 4 '12 #3
Mmmel
52
Shall try right away! Thanks for the direction, and I'll let you know if my head is getting woozy from all the banging! : )
Dec 4 '12 #4
Mmmel
52
Got it! The syntax took me a minute to figure out, but it's all good. Thank you so much for the suggestions, and so timely too! Much appreciated. : )
Dec 4 '12 #5
Rabbit
12,516 Expert Mod 8TB
Not a problem, good luck!
Dec 4 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Joe | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.