Solved

Creating an aggregate query in MS-Access

Posted on 2011-09-30
10
290 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
  • 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 500 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now