How can I filter groups in a report ?

[Roger]

I have a daily status report which shows transactions and their date grouped
by [client name]. The transactions cover a range of different types of
transactions including "account closed" which can also be shown (as one of
the types of transactions). Is it possible to have a condition on the
[client name] group which filters out the whole group if "account closed"
appears as one of the transactions for that particular client (it is usually
the last in terms of date/time) ?

thanks for your help ...... Roger

 

[Marshall Barton]

I have a daily status report which shows transactions and their date grouped
by [client name]. The transactions cover a range of different types of
transactions including "account closed" which can also be shown (as one of
the types of transactions). Is it possible to have a condition on the
[client name] group which filters out the whole group if "account closed"
appears as one of the transactions for that particular client (it is usually
the last in terms of date/time) ?

The criteria for that is difficult for me to describe in the
query design grid. In SQL view the Where clause would
include a condition like:

Not Exists(SELECT 1 FROM thetable As X
WHERE X.client = thetable.client
And X.transtype = "account closed")

But, things could get a whole lot more complex than that if
you can have a client that closes their account and then
reopens it, etc. It would greatly simplify things if each
account id was permantly retired when an acount was closed
and a new account id was created when a client established a
new account. Think about these extended situations ...

 

[Roger]

Thanks for your reply. Initially I had a look at trying to do it as part of
the query, but came to the conclusion that it was too hard. Of course, I can
filter out the actual "account closed" transaction, but not all the other
transactions for the same client ... hence my look at whether doing it in
the Report design was easier ! Obviously not ! The status report is all
about getting a list of current clients (where the account is not closed and
they being filtered out somehow) where nothing has happened in the last
month so that someone can telephone or email them as a followup. Am I
missing something ... it seems to be an easy thing, but perhaps not in
Access.

.... Roger

I have a daily status report which shows transactions and their date
grouped
by [client name]. The transactions cover a range of different types of
transactions including "account closed" which can also be shown (as one of
the types of transactions). Is it possible to have a condition on the
[client name] group which filters out the whole group if "account closed"
appears as one of the transactions for that particular client (it is
usually
the last in terms of date/time) ?

The criteria for that is difficult for me to describe in the
query design grid. In SQL view the Where clause would
include a condition like:

Not Exists(SELECT 1 FROM thetable As X
WHERE X.client = thetable.client
And X.transtype = "account closed")

But, things could get a whole lot more complex than that if
you can have a client that closes their account and then
reopens it, etc. It would greatly simplify things if each
account id was permantly retired when an acount was closed
and a new account id was created when a client established a
new account. Think about these extended situations ...

 

[Marshall Barton]

Easy depends on who is doing it. I think a subquery is a
fairly easy and standard way of doing what you asked. Of
course, if you are not familiar with using SQL, it may seem
difficult to you. Did you try it?
--
Marsh
MVP [MS Access]

Thanks for your reply. Initially I had a look at trying to do it as part of
the query, but came to the conclusion that it was too hard. Of course, I can
filter out the actual "account closed" transaction, but not all the other
transactions for the same client ... hence my look at whether doing it in
the Report design was easier ! Obviously not ! The status report is all
about getting a list of current clients (where the account is not closed and
they being filtered out somehow) where nothing has happened in the last
month so that someone can telephone or email them as a followup. Am I
missing something ... it seems to be an easy thing, but perhaps not in
Access.

I have a daily status report which shows transactions and their date
grouped
by [client name]. The transactions cover a range of different types of
transactions including "account closed" which can also be shown (as one of
the types of transactions). Is it possible to have a condition on the
[client name] group which filters out the whole group if "account closed"
appears as one of the transactions for that particular client (it is
usually
the last in terms of date/time) ?

The criteria for that is difficult for me to describe in the
query design grid. In SQL view the Where clause would
include a condition like:

Not Exists(SELECT 1 FROM thetable As X
WHERE X.client = thetable.client
And X.transtype = "account closed")

But, things could get a whole lot more complex than that if
you can have a client that closes their account and then
reopens it, etc. It would greatly simplify things if each
account id was permantly retired when an acount was closed
and a new account id was created when a client established a
new account. Think about these extended situations ...

 

[Roger]

Yes, it all depends on the knowledge one has. I have only a limited
knowledge of SQL ... I didn't try it simply because I couldn't fully
understand it. To solve my problem I have now created a new "status" table
with two records/options relating to "dossier (or account) open" and
"dossier closed", and linked to the dossier (client) number. When the
dossier/account for the client is closed and this is input into the actions
table I have an on-event macro that will set the status in the "status"
field in the Status table as closed (this simply saves having another field
on the input form which may confuse users). I can then filter the dossiers
where the account is closed out at the query stage using the "status" field.
It may not be the most sophisticated way of doing it, but from my point of
view it is simple and I believe I can make it work okay.

thanks for your help .. Roger

Easy depends on who is doing it. I think a subquery is a
fairly easy and standard way of doing what you asked. Of
course, if you are not familiar with using SQL, it may seem
difficult to you. Did you try it?
--
Marsh
MVP [MS Access]

Thanks for your reply. Initially I had a look at trying to do it as part
of
the query, but came to the conclusion that it was too hard. Of course, I
can
filter out the actual "account closed" transaction, but not all the other
transactions for the same client ... hence my look at whether doing it in
the Report design was easier ! Obviously not ! The status report is all
about getting a list of current clients (where the account is not closed
and
they being filtered out somehow) where nothing has happened in the last
month so that someone can telephone or email them as a followup. Am I
missing something ... it seems to be an easy thing, but perhaps not in
Access.

Roger wrote:
I have a daily status report which shows transactions and their date
grouped
by [client name]. The transactions cover a range of different types of
transactions including "account closed" which can also be shown (as one
of
the types of transactions). Is it possible to have a condition on the
[client name] group which filters out the whole group if "account
closed"
appears as one of the transactions for that particular client (it is
usually
the last in terms of date/time) ?


The criteria for that is difficult for me to describe in the
query design grid. In SQL view the Where clause would
include a condition like:

Not Exists(SELECT 1 FROM thetable As X
WHERE X.client = thetable.client
And X.transtype = "account closed")

But, things could get a whole lot more complex than that if
you can have a client that closes their account and then
reopens it, etc. It would greatly simplify things if each
account id was permantly retired when an acount was closed
and a new account id was created when a client established a
new account. Think about these extended situations ...

 

[Marshall Barton]

Yes, it all depends on the knowledge one has. I have only a limited
knowledge of SQL ... I didn't try it simply because I couldn't fully
understand it. To solve my problem I have now created a new "status" table
with two records/options relating to "dossier (or account) open" and
"dossier closed", and linked to the dossier (client) number. When the
dossier/account for the client is closed and this is input into the actions
table I have an on-event macro that will set the status in the "status"
field in the Status table as closed (this simply saves having another field
on the input form which may confuse users). I can then filter the dossiers
where the account is closed out at the query stage using the "status" field.
It may not be the most sophisticated way of doing it, but from my point of
view it is simple and I believe I can make it work okay.

Actually, I kind of like that idea. Using a Join on indexed
fields is probably faster than using an Exists predicate.