2

I am working in Microsoft Access. My requirement is: user will give any percentage value and I have to find the number of IDs which form the percentage of the 'Value' column. For eg in the below dataset (it is sorted by descending of value column which is also required), the sum of all values is '8409131'.

ID        NAME    VALUE
1000000090  A   2295175
1000000974  B   1942753
1000015555  C   1887965
1000004864  D   1310400
1000015557  E   972838

If I enter 75%, the value is 65170765.25, so i need to return all the IDs which forms the '65170765', less than or equals to. So in this case below are the sum of values which are less than 65170765.

ID      NAME    VALUE
1000000090  A   2295175
1000000974  B   1942753
1000015555  C   1887965

Is this possible to achieve my requirement in Access SQL?

My plan is to make a running total column to find sum of first two rows and then sum of that value with next row. But in Access, I am not able to figure out how to create incremental rows in select query also to achieve this.

Query I tried:

SELECT T1.ID, T1.NAME, T1.VALUE,(T1.VALUE + T2.VALUE)  FROM 
(SELECT  OBLIGOR_ID , RUN_MANAGER.BANK_NAME AS BANK_NAME, RUN_MANAGER.REPORTING_PERIOD, SUM(VALUE) As VALUE 
FROM DATA 
INNER JOIN RUN_MANAGER 
ON DATA.RUN_NUMBER=RUN_MANAGER.RUN_NUMBER 
WHERE  RUN_MANAGER.NAME='A' 
GROUP BY ID,RUN_MANAGER.NAME 
ORDER BY SUM(VALUE) DESC) AS T1 
INNER JOIN
(SELECT  OBLIGOR_ID , RUN_MANAGER.BANK_NAME AS BANK_NAME, RUN_MANAGER.REPORTING_PERIOD, SUM(VALUE) As VALUE 
FROM DATA 
INNER JOIN RUN_MANAGER 
ON DATA.RUN_NUMBER=RUN_MANAGER.RUN_NUMBER 
WHERE  RUN_MANAGER.NAME='A' 
GROUP BY ID,RUN_MANAGER.NAME 
ORDER BY SUM(VALUE) DESC) AS T2
ON T1.ID=T2.ID+1

This is not a duplicate question. The problem is, this question is based on Access SQL and also I do not have any incremental ascending rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.