473,320 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 1721
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,501 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

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

Similar topics

3
by: hi | last post by:
Hi, I've created a form, on the bottom it has the navigation buttons to move to the next/previous records. However, the button for "new record" is greyed out (i can't select it). How do I make...
3
by: Joe | last post by:
Hi there, question regarding Access 2000 - locking records: I want to be able to lock a record (or several records) on a form so that particular record can not be edited until I undo the lock (im...
1
by: John Hunter | last post by:
I've recently had a nasty problem with the "Invalid reference to the property Form" error in subforms - nasty because it doesn't seem to consistently happen to all forms which contain the same...
2
by: R. Hopping | last post by:
Is it possible to prevent the addition of new records but still be able to edit existing records on a form?
1
by: NumberCruncher | last post by:
Hi All, I am struggling with setting up my first system of tables, forms,and reports, and could use your help! I am setting up a database to keep track of the production of a produced item. The...
3
by: melnhed | last post by:
---Report the current filtered records from a Form--- Hello All, I've seen this topic discussed before, but the solution described then doesn't work in my particular case. My Config: ...
16
by: radio1 | last post by:
---Report the current filtered records from a Form (in an ADP project)--- Hello All, I've seen this topic discussed before, but the solution described then doesn't work in my particular case. ...
5
by: Soccer5 | last post by:
Trying to Count records on a report that meet a certain criteria. Have a text box in the Report Footer that has the following in the Control Source: =Count(="S") This does not work. It...
4
by: ske | last post by:
Hi I've inherited an Access database where there is a form setup which prints out a Job Sheet. This works fine with one record. However I need to set it to loop through several records and print...
5
by: Newbie23 | last post by:
Hi All. Was wondering if anyone could help me with this. Currently I have a form that creates customer details (Account Number, Name, Contact Address etc) when I hit the 'Next' cmd button it takes...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.