Group by, Then Count, Then write out results

[Mel via AccessMonster.com]

I'm not sure how to do this. I need to create an exception report. 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.

And not sure how to run and group them in 2 thru 3c.

Please help.

Thanks,
Mel

 

[Bob Barrows [MVP]]

I'm not sure how to do this. I need to create an exception report.
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.

Create a new query in design view without selecting any tables, switch
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"

 

[Mel via AccessMonster.com]

Thanks!! Works like a charm.

Mel

I'm not sure how to do this. I need to create an exception report.
So, I need to create a query for the report.

[quoted text clipped - 20 lines]

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.

Create a new query in design view without selecting any tables, switch
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"