Writing a query combing 3 tables

[varadaradj]

Hi

Though I'm using Access for last 10 years, I don't know to write complex queries. I can't understand inner join, outer join, left join or right join. Is there any help available with examples?

Please help me how to write a query required for me in Access 2007.



Tables

1. Master (ID, Name, OpeningBalance fields are required in this table)
2. Collection ((I) Sum of Amount Collected (field only) for a period ex. 01/04/2017 to 31/03/2018, grouped by ID and (II) sum of amount collected (field only) for the period before {01/04/2017} grouped by Member ID
3. Refunds ((I) Sum of Amount refunded (field only) for a period ex. 01/04/2017 to 31/03/2018, grouped by ID and (II) sum of amount refunded (field only) for the period before {01/04/2017} grouped by Member ID

I want to query (generation a recordset itself is sufficient to create a report) by combing the above data from 3 tables (viz) ID, Name, OpenBalance field from Table 1, Sum(AmountCollected)-1 and sum(amountCollected)-2 from Table 2 and sum(AmountRefunded) from table 3 by matching respective ID (A unique data).

Thanks in Advance

[June7]

Use Access query designer. Then switch to SQL View to see the structure. That's how I learned a lot of SQL coding.

What you want will probably involve multiple queries.

This is really the same concept of calculating inventory balance. You have transaction records of somethings in and somethings out. Review http://allenbrowne.com/AppInventory.html

[sugr74]

OK i got all the jobs now, but i need to set the field which comes up empty to zero, how do i do that please?

Tia

[June7]

@sugr74, you should start your own thread and better explain what you are trying to accomplish.

Post sample data and desired output. Post the attempted query.