Joint Property

JithuAccess

Member
Local time
Today, 03:27
Joined
Mar 3, 2020
Messages
325
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
 
Hi. Can you post the SQL statement for your query? Wasn't there any result for 100, Smith, Ewan, 40 or 50 or 60?
 
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.
 
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 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.
 
If it was a cartesian join between those 3 table examples there would be 90 records, not 9?
 
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.
 
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
 
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
 
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: 126
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
 
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.
 
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
 
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
 
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.
 
Sorry about using the wrong table name. Glad you saw through it and figured it out.
 
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

Back
Top Bottom