[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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
0
geeta_m9
Asked:
geeta_m9
  • 7
  • 3
1 Solution
 
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
 
Simon BallCommented:
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
 
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now