Summary Query Glitches

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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.
 
Back
Top