?
Solved

Creating an aggregate query in MS-Access

Posted on 2011-09-30
10
Medium Priority
?
324 Views
Last Modified: 2012-05-12
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
Comment
Question by:geeta_m9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 36894492
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
 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 36894529
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
 

Author Comment

by:geeta_m9
ID: 36894578
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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 15

Expert Comment

by:Simon Ball
ID: 36894648
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
 

Author Comment

by:geeta_m9
ID: 36897332
Can you show me how to do this, i.e. putting all three queries into one query?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 36906599
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 36906600
like this:
excelaggregate2.jpg
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 36906608
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
 

Author Closing Comment

by:geeta_m9
ID: 36912830
Thanks
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 36915611
does that give you enough info to complete?
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question