Joint Property (1 Viewer)

JithuAccess

Member
Local time
Yesterday, 23:42
Joined
Mar 3, 2020
Messages
297
Hello Guys,

I have three tables:

Table 1
1591133714885.png


Table 2
1591133747226.png


Table 3
1591133770808.png



I have created a query like this:

1591133853203.png


and I am getting query result like this:

1591133915754.png



And I don't want the duplication. I mean to say I want to get the result like this (Done in Excel):

1591134047178.png


I want to omit the values which I shaded in Red. Could you guys please help me how to change my query joints?

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:42
Joined
Oct 29, 2018
Messages
21,358
Hi. Can you post the SQL statement for your query? Wasn't there any result for 100, Smith, Ewan, 40 or 50 or 60?
 

plog

Banishment Pending
Local time
Today, 00:42
Joined
May 11, 2011
Messages
11,613
And I don't want the duplication.

There are no duplicates. Each record is unique.

Can you logically explain why you want the records you highlighted to show up and the others to not show? And I literally mean the word "logically". Please provide a set of rules to discern why those records should show and the others should not.
 

Micron

AWF VIP
Local time
Today, 01:42
Joined
Oct 20, 2018
Messages
3,476
I don't think you can ever get those results with one query with 3 tables - mainly because for 100 SMITH there are 3 FamilyPhys records (Ewan, Lewis and James) and 3 Amount values for 100 Smith. That ought to give you 9 records even with DISTINCT or DISTINCT ROW predicates. Why should Access eliminate Lewis and James and pick Ewan instead?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:42
Joined
Feb 28, 2001
Messages
27,001
The question is in the SQL statement. What you showed looks like a Cartesian JOIN may have occurred, which means that you didn't use correct syntax for a proper JOIN. But without seeing the SQL, we cannot be sure.
 

Micron

AWF VIP
Local time
Today, 01:42
Joined
Oct 20, 2018
Messages
3,476
If it was a cartesian join between those 3 table examples there would be 90 records, not 9?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:42
Joined
May 7, 2009
Messages
19,169
table3 is for what?
for 100, smith?
or for the 3 physician of 100, smith?
the ID of the table must be the ID of the physicians.
 

JithuAccess

Member
Local time
Yesterday, 23:42
Joined
Mar 3, 2020
Messages
297
Hi. Can you post the SQL statement for your query? Wasn't there any result for 100, Smith, Ewan, 40 or 50 or 60?
Hi,

This is SQL Statement:

SELECT [Patient Details].ID, [Patient Details].FName, [Physician Details].[Family Physician], [Physician Fees].Amount
FROM ([Patient Details] INNER JOIN [Physician Details] ON [Patient Details].ID = [Physician Details].ID) INNER JOIN [Physician Fees] ON [Patient Details].ID = [Physician Fees].ID;


Thanks
 

JithuAccess

Member
Local time
Yesterday, 23:42
Joined
Mar 3, 2020
Messages
297
The question is in the SQL statement. What you showed looks like a Cartesian JOIN may have occurred, which means that you didn't use correct syntax for a proper JOIN. But without seeing the SQL, we cannot be sure.
Hello Sir,

This is my SQL Statement:



SELECT [Patient Details].ID, [Patient Details].FName, [Physician Details].[Family Physician], [Physician Fees].Amount
FROM ([Patient Details] INNER JOIN [Physician Details] ON [Patient Details].ID = [Physician Details].ID) INNER JOIN [Physician Fees] ON [Patient Details].ID = [Physician Fees].ID;


Thanks
 

JithuAccess

Member
Local time
Yesterday, 23:42
Joined
Mar 3, 2020
Messages
297
I don't think you can ever get those results with one query with 3 tables - mainly because for 100 SMITH there are 3 FamilyPhys records (Ewan, Lewis and James) and 3 Amount values for 100 Smith. That ought to give you 9 records even with DISTINCT or DISTINCT ROW predicates. Why should Access eliminate Lewis and James and pick Ewan instead?

Hi,

Let me explain.

1591194050279.png


Now I will explain you the real problem. I want to find the total money spend by Patient Smith. In real he spent $60.00 but if we are getting a query result like this, then in the report, it will show as $180.00 So could you kindly guide me.

Thank You
 

Attachments

  • 1591193397183.png
    1591193397183.png
    2.4 KB · Views: 85

JithuAccess

Member
Local time
Yesterday, 23:42
Joined
Mar 3, 2020
Messages
297
Hi. Can you post the SQL statement for your query? Wasn't there any result for 100, Smith, Ewan, 40 or 50 or 60?

Hello,

Let me explain.

1591194354580.png



Now I will explain you the real problem. I want to find the total money spend by Patient Smith. In real he spent $60.00 but if we are getting a query result like this, then in the report, it will show as $180.00 So could you kindly guide me.

Thank You
 

plog

Banishment Pending
Local time
Today, 00:42
Joined
May 11, 2011
Messages
11,613
I want to find the total money spend by Patient Smith.

Then there is no need to use Physician Fees in your query. Exclude it and you will get your corect results.
 

JithuAccess

Member
Local time
Yesterday, 23:42
Joined
Mar 3, 2020
Messages
297
Then there is no need to use Physician Fees in your query. Exclude it and you will get your corect results.

Thanks,

But I want to find the total money spent by Smith. So we have to include Physician Fees in our query?

Thanks
 

JithuAccess

Member
Local time
Yesterday, 23:42
Joined
Mar 3, 2020
Messages
297
Then there is no need to use Physician Fees in your query. Exclude it and you will get your corect results.
1591194720125.png


1591194738863.png

SELECT [Patient Details].ID, [Patient Details].FName, [Physician Details].[Family Physician]
FROM [Patient Details] INNER JOIN [Physician Details] ON [Patient Details].ID = [Physician Details].ID;


Thanks
 

Minty

AWF VIP
Local time
Today, 05:42
Joined
Jul 26, 2013
Messages
10,355
Your data tables aren't correct, you would need a table of visits, which should be should have the following ;
1591195011710.png


Without this your data can't work as you have stored it, and won't give you wnat you want.
 

plog

Banishment Pending
Local time
Today, 00:42
Joined
May 11, 2011
Messages
11,613
Sorry about using the wrong table name. Glad you saw through it and figured it out.
 

JithuAccess

Member
Local time
Yesterday, 23:42
Joined
Mar 3, 2020
Messages
297
Your data tables aren't correct, you would need a table of visits, which should be should have the following ;
View attachment 82587

Without this your data can't work as you have stored it, and won't give you wnat you want.

It works. I changed my Physician Details table like this:

1591196277608.png


and updates the values:

1591196302804.png


Designed the Query:

1591196327898.png


And this is my Query Result, exactly what I want.

1591196358203.png


Thanks a lot once again.
 

Users who are viewing this thread

Top Bottom