Lookup payment values to Charge Table

[MLK]

Dear Team,

Kindly look into the attached acdb. It has two tables and two Queries . Charge table has charge amounts for each Visit and payment table has payments which received . Payment table does not have payments for each visit. It has payments which received only.

• My requirement is to lookup payments to charge table. if a payment not received it should display as "0" in result table.
• Query 1 created for Sum payments.
• Query 2 created to combine Query 1 and Charge table.
• But it doesn't give the result what i need.
• if there are no payment received it doesn't show in Query 2 result ( Example Visit # 4)

Could you please help me to get the result as shown below. i want to show visit #4 as "0" payment line.

I need result like this.


Thank you,
MLK.

[June7]

Change query2 to RIGHT JOIN instead of INNER.

[MLK]

wow, thank you so much.