grouping for subtotals

[Judy]

I have a document tracking database to track each document through it's
progress from authoring through final approval. I have fields set for each
step in the progression; set up as date fields (showing what date each
document got to that step).

I need to get a subtotal by each chapter of how many documents are in each
phase; my problem is this query:

Chapter field = Group By
Collaboration field (date the document entered collaboration) = criteria Is
Not Null
New field using Total by Chapter: =Count([chapter])

The results give me the total number of documents for each chapter, but they
are grouped by the date. e.g.
Chapter 1 11/19/08 = 19 documents
Chapter 1 11/29/08 = 10 documents
and so on; I only want the chapter subtotal; in the e.g. above that would be
29 documents.

Can someone tell me what's wrong with my query please?

 

[Tom van Stiphout]

On Sun, 1 Feb 2009 17:55:01 -0800, Judy

Uncheck the Show box of the CollaborationDate column.

-Tom.
Microsoft Access MVP

 

[Tom van Stiphout]

On Sun, 01 Feb 2009 21:38:04 -0700, Tom van Stiphout

Hmmm, maybe that wasn't it. At least in A2007 you can't check the Show
box for a Where column.
Here is an equivalent query using the A2007 Northwind database:
SELECT Orders.[Customer ID], Count(Orders.[Order ID]) AS [CountOfOrder
ID]
FROM Orders
WHERE (((Orders.[Shipped Date]) Is Not Null))
GROUP BY Orders.[Customer ID];

Pretend that Customer ID is you Chapter, Shipped Date is your
CollaborationDate, and Count(Order ID) is your count. Note that this
query does not display dates (is not grouping by date) and thus you
get the sum over all dates.

-Tom.
Microsoft Access MVP

On Sun, 1 Feb 2009 17:55:01 -0800, Judy

Uncheck the Show box of the CollaborationDate column.

-Tom.
Microsoft Access MVP

I have a document tracking database to track each document through it's
progress from authoring through final approval. I have fields set for each
step in the progression; set up as date fields (showing what date each
document got to that step).

I need to get a subtotal by each chapter of how many documents are in each
phase; my problem is this query:

Chapter field = Group By
Collaboration field (date the document entered collaboration) = criteria Is
Not Null
New field using Total by Chapter: =Count([chapter])

The results give me the total number of documents for each chapter, but they
are grouped by the date. e.g.
Chapter 1 11/19/08 = 19 documents
Chapter 1 11/29/08 = 10 documents
and so on; I only want the chapter subtotal; in the e.g. above that would be
29 documents.

Can someone tell me what's wrong with my query please?

 

[Ken Sheridan]

It depends whether you want to return the individual dates or just the
chapters. If not do as Tom says, e.g.

SELECT Chapter,
COUNT(*) AS ChapterTotal
FROM Documents
GROUP BY Chapter;

If you also want to show the dates (and presumably the step reached at each
date) then you'd need to use a subquery to return the total per chapter, e.g.

SELECT Chapter, DateReached, Step,
(SELECT COUNT(*)
FROM Documents AS D2
WHERE D2.Chapter = D1.Chapter)
AS ChapterTotal
FROM Documents AS D1
ORDER BY Chapter, DateReached;

The way this works is that each instance of the Documents table is
differentiated by the aliases D1 and D2, which enables the subquery to be
correlated with the outer query on Chapter and count the number of rows per
chapter.

You could of course do this very simply in a report without any subquery,
grouping or ordering in a query. Just group the report first on Chapter,
then on DateReached but give only the Chapter group a group header and
footer. Put the Chapter control in the group header, the DateReached and
Step controls in the detail section and an unbound text box control in the
group footer with a ControlSource property of:

=Count(*)

If you want a grand total as well you can put another text box with the same
ControlSource property in the report footer.

Ken Sheridan
Stafford, England

 

[John Spencer (MVP)]

SELECT Chapter, Count(Collaboration) as TheCount
FROM SomeTable
GROUP BY Chapter

By the way if you have a field for each step then you probably have a database
design problem. You should have a table something along the lines of

ChapterSteps (Table Name)
DocumentID
ChapterNumber
StepName
StepDate


If you do have one date field in the record for each step you should be able
to do something like the following. COUNT adds one to the count for each
non-null field so if you count the dates in the fields you don't need to use a
where clause to screen out the nulls.

SELECT Chapter
, Count(Collaboration) as CountCollab
, Count(StepOther) as CountStepOther
, Count(NextStep) as CountNextStep
FROM [Your Table]
GROUP BY Chapter

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County