How to combine two different tables data in into a single report or query (1 Viewer)

shery1995

Member
Local time
Today, 22:41
Joined
May 29, 2010
Messages
71
I have three tables in my database with following details:

ClientMain ClientMatter AdditionalClient

PK ClientID PK MatterID PK. ClientID
FirstName PK ClientID PK. MatterID
LastName MatterDesc FirstName
LastName

Based on above structure I need to print a contract. If there is no additional Client then contract should be printed based on ClientMain table client details, otherwise include AdditionalClient details if exist. The problem I am facing here, when I join both tables (ClientMain and AdditionalClient) contract prints only names those exists in both tables. If there is only one client which has details in ClientMain table and no additional client in associated table (AdditionalClint) details are not shown up in report or query. If I change the join condition then every row from ClientMain table shows additional client name where that data does not belongs to that particular client. The reason of including ClientID and MatterID in AdditionalClient and making compound key is because of MatterID in ClientMatter table starts its counter from one when ClienID is chaged. Any help suggestions highly appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:41
Joined
Oct 29, 2018
Messages
21,358
Hi. Maybe you can consider using a SubReport to show additional clients on your Report.
 

plog

Banishment Pending
Local time
Today, 17:41
Joined
May 11, 2011
Messages
11,612
People get hung up on Forms and reports because that's the final step of most issues. What you described is a pure data issue so let's focus on tables and queries. The best way to get help with those is to demonstrate your issue with data. Please post 2 sets to illustrate what you have and what you want:

A. Starting sample data from your tables. Include table and field names and enough data to demonstrate all cases.

B. Expected results of A. Post what you want to end up with when you feed in the data from A.

Again, 2 sets of related data (starting and ending). I don't care about what you've tried and how its close. I need to see what you expect to end up with when you feed in the starting data..
 

shery1995

Member
Local time
Today, 22:41
Joined
May 29, 2010
Messages
71
People get hung up on Forms and reports because that's the final step of most issues. What you described is a pure data issue so let's focus on tables and queries. The best way to get help with those is to demonstrate your issue with data. Please post 2 sets to illustrate what you have and what you want:

A. Starting sample data from your tables. Include table and field names and enough data to demonstrate all cases.

B. Expected results of A. Post what you want to end up with when you feed in the data from A.

Again, 2 sets of related data (starting and ending). I don't care about what you've tried and how its close. I need to see what you expect to end up with when you feed in the starting data..
Thank you for your reply. Since my last post I have been trying different things and came up with something what I want to achieve. However, not sure whether it is a correct approach are not. Anyway I am going to attach the file for your review and suggestion. Thank you in advance
 

Attachments

  • PropertyManagement.accdb
    1.1 MB · Views: 498

theDBguy

I’m here to help
Staff member
Local time
Today, 15:41
Joined
Oct 29, 2018
Messages
21,358
Thank you for your reply. Since my last post I have been trying different things and came up with something what I want to achieve. However, not sure whether it is a correct approach are not. Anyway I am going to attach the file for your review and suggestion. Thank you in advance
Hi. If you're referring to your Query6. Give this one a try too.
SQL:
SELECT tblClient.ClientID, tblAdditionalClient.MatterID, tblClient.FirstName, tblClient.MiddleName, tblClient.LastName, tblAdditionalClient.FirstName_1, tblAdditionalClient.MiddleName_1, tblAdditionalClient.LastName_1
FROM tblClient LEFT JOIN tblAdditionalClient ON tblClient.ClientID = tblAdditionalClient.ClientId;
 

shery1995

Member
Local time
Today, 22:41
Joined
May 29, 2010
Messages
71
Hi. If you're referring to your Query6. Give this one a try too.
SQL:
SELECT tblClient.ClientID, tblAdditionalClient.MatterID, tblClient.FirstName, tblClient.MiddleName, tblClient.LastName, tblAdditionalClient.FirstName_1, tblAdditionalClient.MiddleName_1, tblAdditionalClient.LastName_1
FROM tblClient LEFT JOIN tblAdditionalClient ON tblClient.ClientID = tblAdditionalClient.ClientId;
Thank you so much it works fine.
 

Users who are viewing this thread

Top Bottom