create a query/solution where a client is returned in a number ofother queries

#11

As I don't understand how you "apply a rule" in a query, I am not certain,
but I'd think you ought to be able to use a single query joining the entire
tables to create the intersection result that I described. If you can, then
it would be trivially easy, then to report, grouping on client to produce a
report that looks like

Client Rule Broken

Client01
1
8
23

Client02
2
17
43

but to get either of the formats you show, you'll need to do a Crosstab
Query on the query which produces the junction result-set or jump throough
some hoops in formatting a report. Note: you are now running just a couple
or three Queries, not one per client nor one per rule, and, thus, you won't
need a Union Query.

If you want to use what you already have to get the report format you show
from your Union Query, you can probably just use it as the data source for a
Crosstab. Crosstab Queries and Reports for which a Crosstab Query is the
Record Source can be "a little quirky" and have some limits, so I am always
reluctant to give "how-to" advice in a newsgroup post -- they are irritating
enough when you have the database itself right in front of you.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"colmkav" <colmjkav (AT) yahoo (DOT) co.uk> wrote

Quote:

On Thursday, June 28, 2012 2:57:41 AM UTC+2, Access Developer wrote:
I understand you to say you have clients and rules, and that either no,
one,
or multiple rules may apply to each client (and that multiple clients may
be
associated with each rule) -- a many-to-many relationship. You want to
be
able to query and, for a given client, see what rules apply to that
client.
(You'd like that to all appear on a single line, with client and a check
for
each applicable rule. Frankly, the presentation may be more work than
grouping the client and fules.)

tblClients - identifies the client and associated information, contains
ClientID - unique identifier for this client, primary key
other information about client, e.g., name
tblRules - identifies the rule and any associated information, contains
Ruleid - unique identifier for this rule, primary key
tblClientsRules - identifies a client and an associated rule, also
called a junction or intersection table, defining the
many-to-many relationship, contains
ClientID - client to which this record applies
RuleID -- rule to which this record applies
together, these are the primary key for the

If all you need is to identify the rules that apply to each client, then
use
a report, group on client, and show a detail line for each rule.

If you feel compelled to make it look like a spreadsheet, use a Crosstab
Query on the tblClientsRules, and create a report on the Crosstab Query.
(I'll let someone else pursue that, if you decide you/your users can't
live
without it.)

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"colmkav" wrote in message
news:9ac1be03-3e77-49ed-9b41-c1393c29b3b7 (AT) googlegroups (DOT) com...
Hi, I have one table (called CLIENTS) with all the clients in them plus
a
series of "rule" tables (eg rule01, rule02) with a selection of clients
in
each of them (determined according to whether they pass a given rule). I
want to create a new table/resultset which contains all the clients and
specifies which of the rule tables it appears in.

What is the best way to achieve this?

thx
Colm

Probably need to explain better. I have a group of clients with data such
as no of transactions. I have a set of rules which are applied on all
clients. One such rule might be no of Transactions > 100.

I apply each rule (via a separate query) on the universe of clients to
give a result set for each rule. I put this into a table eg TblRule01. I
have done a union query to get all the clients that are returned by one or
more of the queries.

I now want to create a summary report stating which rules these clients
appeared in the results for eg had transactions > 100.

I am flexible how this summary report should look like.

Could look like:

Client-id Rules broken
Client1 1,4,8
Client2 2
Client3 12, 17

OR

Client-id Rule1 Rule2 Rule3 Rule4.... Rule17
Client1 Y N N Y N
Client2 N Y N N N
Client3 N N N N ... Y

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

create a query/solution where a client is returned in a number ofother queries

comp.databases.ms-access comp.databases.ms-access

Re: create a query/solution where a client is returned in a number of other queries - 06-28-2012 , 11:53 AM