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....
I have only experience using the design view to build queries
Thanks in advance!
elektr
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....

I have only experience using the design view to build queries
Thanks in advance!
elektr
Last edited: