Hello all
I need to create an ACCESS-VBA algorithm which is creating a few difficulties.
I have 2 tables:
1)Pool1= is dynamic and contains the current list of "ID" at a certain Test-date
2)HUB= Static table with all the ID available in the database and the relative information

-> Every ID is related to a value which express the SERVICER-BANK.
The test is willing to evaluate the impact of the most redundant SERVICER-BANK within the portfolio at a certain date...
...Translated: I need to
1- Check all the SERVICERS related to each ID of the table POOL1
2- Sum the relative ID-amounts which have common SERVICER.
3-Write the percentage of the the servicer which has the greater impact in the portfolio POOL1(in terms of AMOUNT of MONEY: so that 1 Servicer with an amount of 1million has a greater impact than 5 IDs with the same servicer but an amount of 100.000 each)

I would like to avoid to save the name of each servicer for the ID in Pool1 and update the amount of each of thus due to the size of the tables.

My idea is:
-I start reading each ID of POOL1 with a Do-Loop and I look for the correspondent information for that ID in HUB (with another DO-LOOP)
-I keep scrolling Pool1 saving all the relative amounts for the same servicer...once at the end of the table: I save the total amount for the [articular SERVICER in a variable, and I restart reading Pool1 starting from the following ID with a different servicer than the one I analysed previously.

The problem arises from the fact that I don't know how to save the position of the ID read in POOL1, and anyway: Once I am reading any ID of POOL1 everytime I should read again all the other previous ID to re-sum all the servicers repeated until the end of the table.

I thank in advance everyone who will try to give a solution to this problem.
Have fun
E.M.