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

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.

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.

What are the results you are expecting

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.

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 :)

Download attached file and check queryFinal should it be like this

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 :)

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

Customer 5 also belongs to asset3 and asset4 why asset4 is not in category 1 i mean first record

yeah, sorry, had it wrong, uploaded again.

Download the attached file and check if it fulfills your requirement

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 :)

I think you can do it yourself now

Ok, never mind, I'll figure it out. Cheers

I posted the solution already.
Here it is implemented.

Do you know if there are any articles that describe this kind of problem and the solution?

Is your problem still not solved

it's solved, I just want to educate myself.
By the way, I ran the query with about 3000 assets and 3000 customers and it worked perfectly giving the result in about 5 minutes. Now I am testing it with 30000 assets and about 25000 customers (all have many-to-many relationships). Access gave me "File sharing lock count exceeded" error message, so I increased maxlocksperfile to 1000000 (see http://support.microsoft.com/kb/815281). Now it is running again, I'll let you know when it finishes.

It is still giving me "file sharing lock count exceeded. increase maxlocksperfile registry entry" I cannot change my registry because of policy restrictions. I typed "DAO.DBEngine.SetOption dbmaxlocksperfile,1000000" into immediate window and pressed return. After running the code again, still same error.

At which routine it gives the error I think its Update Sequences

How can I check this? The code runs for about 2 hours until it gives this error.

can you upload database zipped (if possible)

can you upload database zipped (if possible)

I can upload data if you state that you will not charge any money now or in the future for this help. Sorry for that, corporate regulations.

By the way, the file is 1074 KB, where can I upload it for you?

We are here to solve your problems and giving you advice no one can charge you for giving advice on this forum

you can upload it here

it is too large for uploading here, maybe I can use some uploading site? Which one do you prefer?

what is the size of file

1074 KB, the limit on this forum for .zip is 785.9 KB

you can check your self where the code breaks by stepping into the code
and by the way how many assets and customers you have in database

it does not break when I run through the cycle. The code runs for about 2 hours and then gives me this error. I have 16 000 assets and 16 500 customers and there are 30 000 relationships between them. The algorithm is correct but I guess there is some Access memory handling problem involved.

I cant guess I have to see whats happenings

............