Creating an aggregate query in MS-Access

I would like to create a query in Microsoft Access that includes some columns from the Intake table and Student tables (not aggregate columns) and a single column that has the count  of the StudentIDs in the Contact table+ the count of the StudentIDs in the Coaching Session Notes table plus the count of the StudentIDs in the Coaching table (see attached file). Is it possible to create such a query and if so can I create it using the Query Design window?
Aggregate-Query.jpg
geeta_m9Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Simon BallConnect With a Mentor Commented:
i have provided an mdb with an example of the 2nd technique - see the query "qry_StudentID_Count_sum" in the attached MDB Database3.mdb
0
 
Simon BallCommented:
tes it by adding a column for each of your aggregations first, once its working correctly like that.  make an expression column ( select expression instead of groupby in the" total:" field, and add in the code which will be something like:

count([contact table]![studentid])+count([coaching session notes]![student_id]+count([coaching table]![studentid])

if you cannot get that to work, you could do a lazt way around, write a query for each of those three tables which has only group by student id and count of student id

then pull those three queries into a new query, join on student id and create an expression field to sum the three counts into a "newsum" field ( and show student id).... then save that query, drag it into the query3 above and link it to student table on student id, and display the "newsum" field.
0
 
geeta_m9Author Commented:
Hi Sudonim, would I need to create a new mdb for this? I guess my question is how do I incorporate all of this into an existing mdb and ensure that the queries are executed in the proper order?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Simon BallCommented:
if you make 3 queries and then drag them all into a new query as if they were tables, access wil do it in the proper order for you.

you then drag tht query into the query above to get the counts...

you dont need a new mdb, just make some new queries in your current db...always make regular backups though.
0
 
geeta_m9Author Commented:
Can you show me how to do this, i.e. putting all three queries into one query?
0
 
Simon BallCommented:
sorry for the delay and yes.

have a look in the db i provided.

i made three queries,  then i made a new query, and where it asks you to select tables, i selected the query tab  image 1, adding queries as data sources in a query
and selected the three queries and hit "add

which pulls them into the query...

then i linked them all on student id:
0
 
Simon BallCommented:
like this:
excelaggregate2.jpg
0
 
Simon BallCommented:
i also pulled in the student table and joined on student id, and did a sum expressioni, in the database3 example above, the query is called qry_StudentID_Count_sum

its sql is as follows ( in case you need to copy the expression code into a new query

 
SELECT qry_CoachingCount.studentID, [ctcount]+[contactcount]+[csncount] AS newsum
FROM [student table] INNER JOIN ((qry_CoachingCount INNER JOIN qry_ContactCOunt ON qry_CoachingCount.studentID = qry_ContactCOunt.studentid) INNER JOIN qryCSNCOUNT ON qry_ContactCOunt.studentid = qryCSNCOUNT.studentID) ON [student table].studentID = qry_CoachingCount.studentID;

Open in new window

0
 
geeta_m9Author Commented:
Thanks
0
 
Simon BallCommented:
does that give you enough info to complete?
0
All Courses

From novice to tech pro — start learning today.