Re: Group by, Then Count, Then write out results
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Wed, 18 Jun 2008 13:33:45 -0400
Mel via AccessMonster.com wrote:
I'm not sure how to do this. I need to create an exception report.Create a new query in design view without selecting any tables, switch
So, I need to create a query for the report.
The query must
1. search for all accounts that have the "Yes" flag
2. then it must group them by account #
3. then it must check for status within that account # group
3a. if the flag is "Yes" and any of the statuses = open, then this
is Not an exception (i can move to next group of account #'s
But,
3b. if the flag is "Yes" and all fo the statuses = closed for that
account# group, then this is an exception
3c. write this record out to a table or query (flag, account#, status,
custName)
(Yes, acct#1110, Closed, ABC Company)
3d. then move to the next account # group and do 3b & 3c again, until
end of file
4. The info in #3c above is used for the exception report.
I'm not sure if 3c above should be a query or write it to a table
that will be deleted after the report is ran.
it to SQL View and paste the following in. Correct the table name and
switch back to design view or run it.
Select flag,account#, "Closed" as [AcctStatus], custName
from accounts
where flag="Yes"
group by flag, account#, custName
having min(status)="closed" and max(status) = "closed"
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
.
- Follow-Ups:
- Re: Group by, Then Count, Then write out results
- From: Mel via AccessMonster.com
- Re: Group by, Then Count, Then write out results
- References:
- Group by, Then Count, Then write out results
- From: Mel via AccessMonster.com
- Group by, Then Count, Then write out results
- Prev by Date: Re: Parameter in Crosstab Query
- Next by Date: RE: Crosstab Query Question
- Previous by thread: Group by, Then Count, Then write out results
- Next by thread: Re: Group by, Then Count, Then write out results
- Index(es):
Relevant Pages
|
