Created a Query but got duplicate Results

[jrsaturnino]

Good morning smart people!

I hope one of you can help my current issues that i am facing with MS Access.
The situation is i made separate tables for Customers, Sales and Collection. Then when i created a query from those tables i got duplicated results. I was expecting by obtaining the information from those tables will give me the following:

1. Sales table will match payments from collection table using the reference number.

2. No duplicates

3. Customers, Sales and Collection Tables will merge into one report on a query.


Hope you can help me with this. I am not a programmer just a newbie in MS Access. Still Learning!.

Thanks,

Bert

 

[June7]

Customers has 1-to-many relationship with both Sales and Collections? Yes, you will get duplication if you include all 3 tables in query.

 

[arnelgp]

there will always be duplicate.
there can be multiple partial payment for each sale.

 

[jrsaturnino]

Thank you Pat! Your suggestion has bee effected. but i found difficulty in merging payments into one payment when customer pays in partial.

In my query, in relation to roberto saturnino transactions
in his second payment TotalRevenue should not be duplicated/ or should be excluded from TotalRevenue.

Please help me resolve this everyone. Thanks!

 

[June7]

If you don't need to see individual partial payments, do a query that aggregates (SUMS) payments then join that query to Sales.

Otherwise, put payments in a subreport.

 

[jrsaturnino]

June,

Thank you for the comment and suggestion. I like the option 2 that you just gave me which shows the sub-report, can you help me how to do it?

 

[June7]

What specifically do you need to know? There are many tutorials on-line about building report/subreport arrangement.

 

[arnelgp]

kabayan, subuk lang.
look @ the report I made.

 

[jrsaturnino]

Dear smart people,

Salamat kabayan! for tips and helpful changes in my database. I made some queries (qryARBalance/qryDueForCollection and Etc). I tried to add a sales from Forbes Academy but the balances of those queries mentioned remained the same. It should increase by 900K but did not work.

Please help me out

 

[jrsaturnino]

Thank you Pat. But how can we add the sales from Forbes Academy to qryARBalance considering this is new transaction and will pay after 15 days.

Please help me.

thanks