Solved

Access aggregate query show total for each possible result

Posted on 2012-04-05
2
371 Views
Last Modified: 2012-06-21
Hi! I'm trying to put together an aggregate query in Access. The purpose of the query is to count records in a table based on a criteria. However, I want the query to show the total records where a certain field is true, and the total where it is false. I want these to show up as two separate columns in the query. However, when I include both criteria and run the query, I get no results. How can I fix this?
screenshot
0
Comment
Question by:etech0
2 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 37811847
SELECT Copywriter, CatYear, Sum(IIF([Cat Recd] = True, 1, 0)) as [Yes], Sum(IIF([CatRecd] = False, 1, 0)) as [No]
FROM [CatWeb Work]
WHERE CatYear = 2013
GROUP BY CopyWriter, CatYear
0
 
LVL 10

Author Closing Comment

by:etech0
ID: 37812480
Thanks!
Quick and easy response.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

791 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