Solved

Creating an aggregate query in MS-Access

Posted on 2011-09-30
10
302 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

825 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