Need a help for a Query by Charge and payment tables

[MLK]

Dear Team,

Kindly look into the the attached acbd. it contains 2 tables. Charge table has charge amounts for each visit #. Payment table has payments which received for each Visit by payment type. i need to create a Query which given following result.





I have created a query , but it doesn't give correct details. Charge amount has duplicated. I need to take sum of payments of each visit.

I got the following result from query.



Could please help me for this.

Thanks for your time.

MLK.

[mike60smart]

Hi MLK

You are best to view the data as shown in the attached Db

Also look at the example query

[NoellaG]

Hi, you can solve this by making 2 queries:
one totals query qtotPayments to get the total amount payed:

SELECT Payment.[Visit #], Sum(Payment.[Payment Amount]) AS [SumOfPayment Amount]
FROM Payment
GROUP BY Payment.[Visit #];

and then the second query linking the totals query to the charge table:

SELECT Charge.[Visit #], Charge.[Charge Amount], qtotPayments.[SumOfPayment Amount]
FROM qtotPayments INNER JOIN Charge ON qtotPayments.[Visit #] = Charge.[Visit #]
ORDER BY Charge.[Visit #];

[MLK]

Dear Mike,

Thank you very much for the time and support.

Actually I am looking for a Query which give above result.

Thanks
MLK.

[MLK]

Dear NoellaG,

Thank you very much for the support and time.

You have solved this problem.

Really Appreciate you support.

MLK.

[MLK]

Dear NoellaG,

When i use your method with my acdb , i have noticed that, there are some visits without any payments. There are no records for those in Payment table. but charge value is still there in Charge table. Kindly look into attached acbd. ( Visit # 4, charge value is $400, but no payment received for that, there for no recoards in payment table). My requirement is, i need to show them as "0", bcoz charge value is existing. I need final result as below.



Could you please check and let me know a method.

Thank you very much for your time.

MLK.