Reporting on multiple tables

[Chris O''''Neill]

I know this is probably pretty basic stuff, but it's the first time I've ever
done it and I'm confused. Here's the scenario:

I have a table called tblClients that contains information about my clients.
Linked to it is a table called tblFamily which contains information about
the client's family members. There's a one-to-many relationship between
tblClients and tblFamily (i.e. a client can have many family members).

I now want to create a report that lists all the information about a client
and his/her family members. How do I go about doing this? Does anybody have
an online tutorial on how to create this kind of report?

Thanks, in advance, for any assistance.

Regards, Chris

 

[Allen Browne]

1. Create a query using both tables.

2. Right-click the line joining the 2 tables in the upper pane of table
design. Access opens a dialog with 3 options. Choose:
All records from tblClients, and any matches from tblFamily
(If you don't do this, any client without family is excluded.)

3. Create the report based on this query.

4. In report design, create a group header on the ClientID so their name
appears above their family members.

The structure you have may be all you need for your clients, Chris, but in
some scenarios it might be better to put all the people into one table and
then define the relationships that exist between them. For example, a bank
might have accounts for a bloke, his wife, and his kids, so they are clients
in their own right.

If you are interested in persuing how a database might do that, see:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[Chris O''''Neill]

Thank you, Allen, for the reply. And for the suggestions. It's late here,
so I'll look at this tomorrow when I'm more awake. (GRIN!) A couple of
quick comments, though...

1. Create a query using both tables.

2. Right-click the line joining the 2 tables in the upper pane of table
design. Access opens a dialog with 3 options. Choose:
All records from tblClients, and any matches from tblFamily
(If you don't do this, any client without family is excluded.)

Yup, that's one not-a-good-thing that was happening. Thanks!

The structure you have may be all you need for your clients, Chris, but in
some scenarios it might be better to put all the people into one table and
then define the relationships that exist between them. For example, a bank
might have accounts for a bloke, his wife, and his kids, so they are clients
in their own right.

My original design and client and family all in one table with the fields
designed like a spreadsheet... child_1-name, child_2_name, etc. Of course,
it didn't take long for me to realize *that* wasn't going to work.

This design is a vast improvement over that, but I can see your point about
how a family member of a client might also be a client, in which case there's
going to be duplication in tblFamily using this design. (SIGH!) And here I
thought I had it figured out.

If you are interested in persuing how a database might do that, see:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html

I'll have to take a look at that when I'm awake. It only took me one
evening to go from the original design to this one, so *hopefully* it won't
take much to go to an even better design.

Thanks, again, for the help!

Regards, Chris

 

[Chris O''''Neill]

The structure you have may be all you need for your clients, Chris, but in
some scenarios it might be better to put all the people into one table and
then define the relationships that exist between them. For example, a bank
might have accounts for a bloke, his wife, and his kids, so they are clients
in their own right.

If you are interested in persuing how a database might do that, see:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html

Thanks for that link, Allen. I've had a chance to look at it and it's got me
thinking again. Looks like design change number 2,395 might be coming up!
Anyway...

I can see the usefulness of that structure, although I'm not sure I need to
differentiate between types of groups. Going to have to think about that a
bit more.

One question, though... Would you say that "location" is an attribute of
"client" or an attribute of "group"??? Or maybe both???

Regards, Chris

 

[Allen Browne]

One question, though... Would you say that "location" is an attribute of
"client" or an attribute of "group"??? Or maybe both???

It might depend on what you are modelling.

Remember that a client can be a corporate entity (i.e. a client can contain
other clients), so I would be tempted to associate addresses with the
client. Any client can then have their own addresses, as well as addresses
that they inherit from a parent client (which apply to them in that
context.)

 

[Chris O''''Neill]

And then Allen Browne replied:

It might depend on what you are modelling.

Remember that a client can be a corporate entity (i.e. a client can contain
other clients), so I would be tempted to associate addresses with the
client. Any client can then have their own addresses, as well as addresses
that they inherit from a parent client (which apply to them in that
context.)

Hmmm.... can you expand on the concept of inheriting an address?

Here was my thinking that a location (or phone number, and probably other
attributes, too) would be tied to the "group" entity...

I'm a member of the group "my family." That group has a "location" called
"home." I'm also a member of the group called "Employees of XYZ Company" and
that group has a location called "company address." And so forth.

I can see where this breaks down, though, with "XYZ Company" because it's
not necessarily a member of any "group" that would have an associated
location. I suppose it also doesn't work for any client that isn't a member
of some group?

So, how does one physically model an inherited attribute? Is there a
one-to-many relationship between "XYZ Company's" location (the one side) and
the members of the "Employee" group (the many side)?

I think I'm getting a bit confused!

Regards, Chris

 

[Allen Browne]

John is a client
Company XYZ is a client.

John works part time for XYZ, so you might have a group of XYZ employees
where John is a member.

John has an address of his own.
XYZ also has its own address.
In John's role as an employee of XYZ, the XYZ address applies to John.

Perhaps John also works part time for ABC. When you are dealing with John as
an employee of ABC, you could use ABC's address to reach him, or you could
use John's own address. However, it would not be appropriate to use XYZ's
address, because that address is not applicable to dealing with John in his
role with ABC.

Please don't let this sidetrack you from what you need to do. It might be
that this approach is not relevant for what you need. But there are
situations where it is useful to apply the address of a parent entity to an
individual, but only in the context of that individual's connection with the
parent group.

 

[Chris O''''Neill]

Thank you, yet again, for furthering my knowledge and undertanding. This has
been an interesting and informative discussion. Anyway...

John is a client
Company XYZ is a client.

John works part time for XYZ, so you might have a group of XYZ employees
where John is a member.

John has an address of his own.
XYZ also has its own address.
In John's role as an employee of XYZ, the XYZ address applies to John.

Perhaps John also works part time for ABC. When you are dealing with John as
an employee of ABC, you could use ABC's address to reach him, or you could
use John's own address. However, it would not be appropriate to use XYZ's
address, because that address is not applicable to dealing with John in his
role with ABC.

Hmmm.... it almost sounds like "location" should be attached to "role"
rather than client or group. I hadn't even thought of that!

Please don't let this sidetrack you from what you need to do. It might be
that this approach is not relevant for what you need. But there are
situations where it is useful to apply the address of a parent entity to an
individual, but only in the context of that individual's connection with the
parent group.

Actually, it *isn't* relevant to my present development project. However, I
really *do* appreciate the information you have provided even though it is
not currently relevant. I always approach a problem from two angles... what
do I need *now* to deal with the immediate issue, and what can I learn that
will be useful in the future.

Anyway, going back to the original "scenario" that caused me to start this
thread, I have decided to use the basic concept and structure of that page on
your web site you directed me to, but not include the "group type" structure.
At this point, it's not really important to know what type of group(s) a
client belongs to, just that they belong to the group(s). So, once again
thank you for assisting me!

Kindest Regards, Chris