Count with Criteria in Access

Question

I have what I am sure is the same question as user "shortma" posted in this post from November 2012, but I can't figure it out and decided to post new instead of into an old thread. I'm trying to understand the Partition function, but I'm a bit lost -- is it only possible by writing SQL directly?

In the Access Query builder, here's what I have (but it doesn't work).  I want to count the number of records that fall into these ranges.  Click here to see full image.

Why wouldn't this work?  Am I misunderstanding how to use the count function with criteria?  It doesn't even work for a single range (deleting the right two agent time fields from this query).

Answer 1

As written this would retrieve no records, since it would display only those records where the count is <12 and at the same time is between 12 and 30, and also between 30 and 60; criteria on the same row are "anded" together, so that all must be true for the record to be displayed.

If you want to use Partition()  - or perhaps simpler in this case Switch() - in a query you would need to add it as a calculated field in the Field row of the query grid, or put it in the Select clause of a SQL query. You may be able to get what you want with something like

SELECT Switch([AgentTime]< 12, "Under 12", [AgentTime] <= 30, "Between 12 and 30", [AgentTime] <= 60, "Between 30 and 60", True, "Over 60") AS AgentTimeBlock, Count(*) FROM yourtable WHERE [Date] BETWEEN #8/6/2013# AND #9/5/2013# GROUP BY  Switch([AgentTime]< 12, "Under 12", [AgentTime] <= 30, "Between 12 and 30", [AgentTime] <= 60, "Between 30 and 60", True, "Over 60");

Answer 2

I would do this differently. I would start with a query that added a calculated column that used the Switch or Nested IIFs to assign a value to each record according to the group it falls into So you would have a resulting recordset that looks like so:

RecordID   CallDate    TimeGroup

1               8/6/13       A

2               8/6/13       B

etc.

where A, B etc represents the group

then use that query in a Group By query that groups by CallDate and TimeGroup and counts the grounds. So the results would look like

CallDate     TimeGroup  Counts

8/6/13          A                5

8/6/13          B                7

etc.

Answer 3

Thanks John, that certainly helps knowing that the criterias are 'anded' as you described.

I will try that switch function in a calculated field, it looks intuitive enough.  Appreciate the pointer.

Answer 4

As I interpret this, you are asking for a count,

WHERE (AgentTime<12) AND (AgentTime BETWEEN 12 AND 30) AND (AgentTime BETWEEN 30 AND 60)

... which can never be true due to the disparity of the math. In order to get three different counts in a single SELECT return you would have to nest separate counts within an encompassing SELECT. I don't how you would accomplish that with Design View but here is the SQL,

SELECT * FROM

(SELECT Count(cd.AgentTime) FROM CallDetail_2013 AS cd WHERE (cd.AgentDate Between #8/6/2013# And #9/5/2013#) AND cd.AgentTime<12) AS [Less12],

(SELECT Count(cd.AgentTime) FROM CallDetail_2013 AS cd WHERE (cd.AgentDate Between #8/6/2013# And #9/5/2013#) AND cd.AgentTime BETWEEN 12 AND 29) AS [12to29],

(SELECT Count(cd.AgentTime) FROM CallDetail_2013 AS cd WHERE (cd.AgentDate Between #8/6/2013# And #9/5/2013#) AND cd.AgentTime BETWEEN 30 AND 60) AS [30to60]

Note that I've changed 12 AND 30 to 12 AND **29******to avoid overlap.

Answer 5

I think everyone is telling me "just learn to write the query statement, Shawn!"