How to combine two different tables data in into a single report or query

[shery1995]

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]

Hi. Maybe you can consider using a SubReport to show additional clients on your Report.

 

[plog]

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]

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

 

[theDBguy]

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.

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]

Hi. If you're referring to your Query6. Give this one a try too.

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.

 

[theDBguy]

Thank you so much it works fine.

You're welcome. Glad we could assist. Good luck with your project.