Joint Property (1 Viewer)

JithuAccess

Member
Local time
Today, 06:06
Joined
Mar 3, 2020
Messages
35
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
Local time
Today, 05:06
Joined
Oct 29, 2018
Messages
10,812
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, 07:06
Joined
May 11, 2011
Messages
9,903
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, 08:06
Joined
Oct 20, 2018
Messages
3,192
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

Happy Retired Curmudgeon
Local time
Today, 07:06
Joined
Feb 28, 2001
Messages
17,069
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, 08:06
Joined
Oct 20, 2018
Messages
3,192
If it was a cartesian join between those 3 table examples there would be 90 records, not 9?
 

arnelgp

error reading drive A:
Local time
Today, 20:06
Joined
May 7, 2009
Messages
9,623
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
Today, 06:06
Joined
Mar 3, 2020
Messages
35
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
Today, 06:06
Joined
Mar 3, 2020
Messages
35
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
Today, 06:06
Joined
Mar 3, 2020
Messages
35
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

JithuAccess

Member
Local time
Today, 06:06
Joined
Mar 3, 2020
Messages
35
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, 07:06
Joined
May 11, 2011
Messages
9,903
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
Today, 06:06
Joined
Mar 3, 2020
Messages
35
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
Today, 06:06
Joined
Mar 3, 2020
Messages
35
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, 13:06
Joined
Jul 26, 2013
Messages
7,092
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, 07:06
Joined
May 11, 2011
Messages
9,903
Sorry about using the wrong table name. Glad you saw through it and figured it out.
 

JithuAccess

Member
Local time
Today, 06:06
Joined
Mar 3, 2020
Messages
35
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 (Users: 0, Guests: 1)

Top Bottom