Summary Query Glitches

A

apollo8359

I have a summary query where the number of items for each person is counted.
The problem is I need to filter further the query by date and status (open or
closed) If a person has ten items, and four are open, with two created on the
same date, the query returns
Bill 6/30/2006 2 Open
Bill 6/29/2006 1 Open
Bill 6/28/2006 1 Open

what I want is Bill 4, but if I take out the date and open status, I
get Bill 10, which is all his items. Someone out there must have run into
this before, so I am asking for any help you can give.
Thanks,
Michael
 
M

menscandidus

I am naming your fields in "TableN" so I can clarify what I am saying
Name
Date
Number
Status

Try in you summary query to specify the fields in such a manner

Field: Name Date Number: Count(*) Status
Table: TableN TableN TableN TableN
Total: GroupBy Max Expression GroupBy
Sort:
Show: X X X X
Criteria:

This should allow you to do what you are looking to do...

Another alternative is to remove the date field and keep the rest the same.
 
A

apollo8359

Thank you for your suggestion, but I am not sure maxing the date will solve
this. The date is a start and stop issue, like >= 1 April 2006 and <= 30 June
2006 to get the second quarter items. We then need two summary reports, one
for open and one for closed items. I may need an interum query to handle the
date and status, then have another query that totals those results. I was
just hoping that there was a way to do a summary without certain columns of
information having an effect on the numbers.
Thanks,
Michael
 
M

menscandidus

Ok, an easy way I found of doing this (if we are on the same train of
thought) was in the following manner. I created three queries. The first
looks for the start date (select query), the second end date (select query) ,
and the third will find the number of open and closed items (summary query).

First Query:
Field: Name Date
Status
Table: TableN TableN
TableN
Sort:
Show: X X
X
Criteria: >= format([Enter Start Date],"dd/mm/yyyy")

Second Query:
Field: Name Date
Status
Table: Query1 Query1
Query1
Sort:
Show: X X
X
Criteria: <= format([Enter End Date],"dd/mm/yyyy")

Third Query:
Field: Name Number: Count(*) Status
Table: Query2 Query2 Query2
Total: GroupBy Expression GroupBy
Sort:
Show: X X X
Criteria:

There are ways of making it more compact, but I can't recall off the top of
my head (through the use of SQL). Sry for any innaccuracies I am doing this
from memory since I have to borrow internet right now...

I hope this works for you.
 
Top