Joint Property

[JithuAccess]

Hello Guys,

I have three tables:

Table 1

Table 2

Table 3

I have created a query like this:

and I am getting query result like this:

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

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]

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]

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.

 

[plog]

Also, is ID a text value?

 

[Deleted member 147267]

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]

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.

 

[Deleted member 147267]

If it was a cartesian join between those 3 table examples there would be 90 records, not 9?

 

[arnelgp]

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]

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]

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]

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.

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

 

[JithuAccess]

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.

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]

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]

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]

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

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]

Your data tables aren't correct, you would need a table of visits, which should be should have the following ;

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

 

[plog]

Sorry about using the wrong table name. Glad you saw through it and figured it out.

 

[JithuAccess]

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.

Oh So sorry. Million thanks for your suggestions.

Thanks

 

[JithuAccess]

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:

and updates the values:

Designed the Query:

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

Thanks a lot once again.