Making a report with weak-entity relationships

[neclark2]

I'm trying to create a report from my database which contains weak entities.
As an example, lets say that the only two tables that I have are a Lawyers
table and a Clients table. Each Client has exactly one lawyer. Therefore, I
want my report to look like this:

Lawyer 1
Lawyer Name
Email
Firm Name

Client 1
Client Name
Email

Client 2
Client Name
Email

Client 3
Client Name
Email

========================

Lawyer 2
Lawyer Name
Email
Firm Name

Client 3
Client Name
Email

Client 4
Client Name
Email


I know that this requires multiple queries but I'm not sure how to do it in
Access...using a single query just results in this:

Lawyer 1
Lawyer Name
Email
Firm Name

Client 1
Client Name
Email
--------------------------------------
Lawyer 1
Lawyer Name
Email
Firm Name

Client 2
Client Name
Email
----------------------------------------
Lawyer 1
Lawyer Name
Email
Firm Name

Client 3
Client Name
Email


This is clearly not what I want as it contains alot of redundant data and is
not an intuitive representation of my database. Any help would be greatly
appreciated!!

Nick

 

[Marshall Barton]

I'm trying to create a report from my database which contains weak entities.
As an example, lets say that the only two tables that I have are a Lawyers
table and a Clients table. Each Client has exactly one lawyer. Therefore, I
want my report to look like this:

Lawyer 1
Lawyer Name
Email
Firm Name

Client 1
Client Name
Email

Client 2
Client Name
Email

Client 3
Client Name
Email

========================

Lawyer 2
Lawyer Name
Email
Firm Name

Client 3
Client Name
Email

Client 4
Client Name
Email


I know that this requires multiple queries but I'm not sure how to do it in
Access...using a single query just results in this:

Lawyer 1
Lawyer Name
Email
Firm Name

Client 1
Client Name
Email
--------------------------------------
Lawyer 1
Lawyer Name
Email
Firm Name

Client 2
Client Name
Email
----------------------------------------
Lawyer 1
Lawyer Name
Email
Firm Name

Client 3
Client Name
Email


This is clearly not what I want as it contains alot of redundant data and is
not an intuitive representation of my database. Any help would be greatly
appreciated!!

The query you tried will work as is. All you need to do is
use the report's Sorting and Grouping window (View menu) to
create a group on the lawyer field. Set the group's Header
property to Yes and then move the lawyer text boxes to the
group header section.

 

[KARL DEWEY]

One query --
SELECT Lawyers.[Lawyer Name], Lawyers.Email, Lawyers.[Firm Name],
Clients.[Client Name], Clients.Email AS ClientEmail
FROM Lawyers LEFT JOIN Clients ON Lawyers.[Lawyer Name] = Clients.[Lawyer
Name];

In report design view add Sorting and Grouping and select [Lawyer Name].

Place lawyer information in header.