Calculations with many-to-many relationship

elektr

Registered User.
Local time
Today, 15:20
Joined
Apr 20, 2009
Messages
22
Hi!
I tried to search for this but I am not sure how to search for this kind of problem as English is not my first language, sorry :)

I have this kind of situation:
I have a many-to-many relationship in my database and I have these three tables:
CUSTOMER_TABLE: Fields CUSTOMER_ID, CUSTOMER_BALANCE (currency)
ASSET_TABLE: Fields ASSET_ID, ASSET_VALUE (currency)
JOIN_TABLE: CUSTOMER_ID, ASSET_ID

Now, I need to calculate the following value for each customer: balances to assets ratio which = SumOfCUSTOMER_BALANCE/SumOfASSET_VALUE. I can do this easily if there is a one-to-many relationship (eg. customer is connected to 3 assets and each of these 3 assets is connected to the same customer). Then I just use the totals query in query builder and check if the relationship is one-to-many...

Now how could I approach this problem if for example I have a customer, who is connected to 2 assets and one of these 2 assets is in turn connected to some other customers? There can be situations where 4 assets are connected to 10 customers etc...

So far I have done this manually in Excel by making this a table with the following fields (just search-copy-paste):
CUSTOMER_ID_1, CUSTOMER_ID_2, ..., CUSTOMER_ID_N, ASSET_ID_1, ASSET_ID_2, ..., ASSET_ID_N, CUSTOMER_BALANCE_1, CUSTOMER_BALANCE_2, ..., CUSTOMER_BALANCE_N, ASSET_VALUE_1, ASSET_VALUE_2, ..., ASSET_VALUE_N

Then by summing all the customer balances and asset values, I calculate the correct balances to assets ratio. Now I know that it is stupid way to calculate it manually like this.... :D

I have only experience using the design view to build queries

Thanks in advance!

elektr
 
Last edited:
How does this work in the real world? Say five customers are linked to an asset worth $50. How do you determine that value of customer 2's asset?
 
Ok, an example:

Customer_1: Balance 10 $
Customer_2: Balance 10 $
Customer_3: Balance 10 $
Customer_4: Balance 10 $
Customer_5: Balance 10 $

Asset_1: Value 50 $

All customers are connected to Asset_1

Now balance to asset ratio = (10+10+10+10+10)/50= 50/50 = 1 (or let's say 100%)

Every customer has balance to asset ratio of 100%


2nd example with many-to-many:

Customer_1: Balance 10 $
Customer_2: Balance 10 $
Customer_3: Balance 10 $
Customer_4: Balance 10 $
Customer_5: Balance 10 $

Asset_1: Value 50 $
Asset_2: Value 50 $

Connections:
Customer_1 - Asset_1
Customer_1 - Asset_2
Customer_2 - Asset_2
Customer_3 - Asset_2
Customer_4 - Asset_2
Customer_5 - Asset_2
(Because of customer_1 there is a many-to-many relationship)

We have to use some sort of algorithm (so far I do this manually) to find all related customers and assets. Now we calculate the balance to assets ratio:
(10+10+10+10+10)/(50+50)=50/100=0.5 (50%) - this is the ratio for all customers
 
The trick is to make the assets 'unique' instead of grouped...
I.e. Make a query first to devide your assets by the number of customers to make the 'real' asset value (50 / 5 = 10)

Then make your totals query (like you would normaly make it) based upon above query.

Good luck !
 
Assuming tables...

tCustomer
CustomerID (PK, Long)
Customer (String)
Balance (Currency)

tAsset
AssetID (PK, Long)
AssetValue (Currency)

tCustomerAsset
CustomerAssetID (PK, Long)
CustomerID (Long)
AssetID (Long)

...and given that any asset that is linked to multiple customers is split evenly between them, you can return the Balance-To-Asset ratio using the following SQL.

Code:
SELECT Balance / 
  (SELECT Sum([tAsset].[AssetValue]/
    (SELECT Count(*) AS CustomerCount 
    FROM tCustomerAsset AS t2 
    WHERE t2.AssetID = t1.AssetID) 
    ) AS Asset
  FROM tCustomerAsset AS t1 INNER JOIN tAsset ON t1.AssetID = tAsset.AssetID
  WHERE t1.CustomerID = [prmCustomerID] 
  ) AS Ratio
FROM tCustomer
WHERE CustomerID = [prmCustomerID]

I think. Note that the SQL will require you to supply prmCustomerID, which is the customer for whom you'd like to know the ratio.
 
I will try to explain more clearly:
All customers that are linked with an asset must have the same ratio, they can have different balances but the ratio will be calculated as sum of total balances divided by sum of total asset values. I cannot divide the asset value with the number of customers.

Now the tricky part is that when I have a relationship where one customer is connected to n assets and the n assets are in turn connected to other customers, then the calculation is not correct (according to the rule that I have to add up all customer balances and asset values)

I have done this so that I create a pivottable in access:
Column: count of customer (1, 2, 3, ... etc) - this value I get by counting how many customers are connected to each asset (sort by asset_ID and then number the customers, I do this in excel with if(asset_ID(previous record)=asset_ID(current record);previous record+1;1)
Row: Asset_ID
Value: Customer_ID

I attached an example in Excel with the pivot table, the expected result and the calculation.
 

Attachments

Means you just want to have total balances / total ratio that doesnt seem complicated
 
On the basis on data you provided in post # 3 in this thread how the resultant query should look like
 
On the basis on data you provided in post # 3 in this thread how the resultant query should look like

That is exactly my challenge. I know how to do this by hand but not with code. In the pivot list, the assets have no duplicates. I first find if there are duplicate contracts in field 1, then when I find one, then I move the asset next to the first asset and delete the duplicate contract. Sometimes with the new asset, there come also new contracts. When I finish checking the first field, then I take the next until the last one. After that I will start checking if there are duplicate contracts in different fields etc etc....
 
Still not able to understand what are the results you want
 
I need to have all assets and clients which are connected on one field, without duplicate asset_ID and without duplicate customer_ID

Thanks for the help so far :)
 
Last edited:
Download attached file and check queryFinal should it be like this
 

Attachments

this is very good already but....

I need to have each group of assets-customers as a different record in the database. So there will be n number of fields for assets (n=maximum number of assets which are connected with each other by customers) and n number for customers (same rule).


I added some data to the example Excel file and the expected result

And thanks again for trying to help :)
 

Attachments

i CANT UNDERSTAND THE CALCULATION BECAUSE I HAVE NOT SEEN THE ORIGINAL DATA

Tell me the customer balances and asset values
 
I attached the db (I updated the tables with the information from the Excel file) and Excel file, this is how it should work
 

Attachments

Customer 5 also belongs to asset3 and asset4 why asset4 is not in category 1 i mean first record
 
khawar, you are a genius, this is exactly what I need!
by the way, can you please modify the query so that there is no "AssetValue" field in table Assets and no "Balance" field in table Customers? The result would be just the combinations of customers and assets without the numeric calculation.

wow, thanks again for this elegant solution :)
 

Users who are viewing this thread

Back
Top Bottom