Hello everybody!

I would like to build an application which will be used for a friends' shop and it should be able to do the followings:



The shopkeeper could add customers in a tree structure. What I mean is that customers number 3 for example suggests customer 5, so this should be indicated somewhere in our DB.


When someone makes a purchase from that shop, then it should be read which customer suggested the one that made the purchase (let's say he/she is the contractor). Then, 5% of the price of the purchase should be added to the contractor, while 2% should be added to the one that suggested contractor (contractor of contractor). This should be done totally to the above 10 levels. The percentage of the third level is 1% while all other levels' (except first and second) percentage is 0,5%.


The solution I attached (which is not working very well) is as follows. I have a table, TblCustomersAffiliate, who indicates the customers relationship. So, customer 1 suggested customer 2 and 3, customer 2 recommended customers 7 and 8 etc...
Table TblCustomers shows what each customer gives to the above levels. For example, I have added 10, 5, 4,3,2 euros to each level.
The query Commition shows how much money each customers earns from the relationship with the other customers. Query total shows the total amount of money for each customer. Unfortunately, for example, customer 1 shoudl earn 51 euro-the query shows 47 euros.


Could anyone help me please? Any possible solution?


Thank you in advance.


Andreas