Created a Query but got duplicate Results (1 Viewer)

[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.

 

[Pat Hartman]

While Sales and Collections each have a relationship with Customer, they don't appear (in your application) to have a relationship with each other. Look at this silly example to see what happens. We have students, classes, and pets. Classes and pets are both related to students but not to each other (unless the pets take certain classes)

Joe, Englisn, Spot
Joe, English, Blinky
Joe, Math, Spot
Joe, Math, Blinky

In your case, it is logical for the three tables to have a hierarchical relationship. And that would NOT cause duplication. So:

Customer -->> Sales -->> Collections

In this schema, collections are related to specific sales. A slightly different interpretation would include a m-m relationship which may be what you want to model your work flow:

Customer -->> Sales -->> SalesCollectioned <<-- Collections

In this schema, collections are related to customers indirectly via the payment collection process. A customer renders $100 and he has 3 open sales records, you apply the payment to the oldest first. Remaining amount goes next to the next oldest sale, and so on until the amount is exhausted.

 

[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

 

[Pat Hartman]

The immediate problem is that there is no collection record for Forbes Academy. The joins in qryARBalance are inner joins and that requires matching records on both sides of the join.

Beyond that, the schema needs major work and that should be fixed before you move on. Most of the relationships shown in the relationship window are invalid and that is why you cannot enforce Referential Integrity (RI). Relationships are ALWAYS between the primary key in one table and a data field or part of the primary key in the other. Most of your relationships are data field to data field and the ones that are PK to data field cannot have RI enforced because there is bad data in the tables. To fix the problem, you will need to remove all the data from the tables so you can delete the "text" fields and add the long integer fields that will point to the PK in the other table. For example, you have four tables that relate to tbl_Branch. ALL are wrong. Rather than being BranchName to BranchName, they should be BranchID to BranchID. So remove the BranchName field from tblSales_Order, tbl_Sales, tbl_Customer, and tbl_Collection and replace with BranchID (long integer). This makes the tables harder to look at when you just open them up because you are seeing a number rather than a name but this is the way relationships work. Whenever you need to see the text, you do it by joining to the Branch table and including the BranchName in the query. Or on forms you would bind BranchID from the sales table to the form and use a combo which has a RowSource that selects data from tbl_Branch. The combo is set to hide the "id" field and show the "text" field so your users never see the number, they only ever see the name.

You also have a lot of bad names that include dashes, slashes, and spaces. While technically Access supports this, I would fix it now because it just becomes a pain later on when you get to coding.

I fixed the branch on the customer table and combo on the form to show how it works. I also set RI where I could which was only a couple of places. Now there is one relationship with BranchID and three that are left to BranchName

 

[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

 

[Pat Hartman]

Eliminate the collection table from the query since you don't seem to care whether it has matching records or not.