Insert a cumulative balance column within a Microsoft Access query 1

[sportdlockport]

I would like assistance in how to create a cumulative balance column within a Microsoft Access query.

Example:

Columns: (calculated column request)
Code Amount Percentage Cumulative Balance %
1 50.00 .20 .20
2 50.00 .20 .40
3 50.00 .20 .60
4 100.00 .40 1.0

Thanks,
Craig

 

[PHV]

SELECT A.Code, A.Amount, A.Percentage, Sum(B.Percentage) AS [Cumulative Balance %]
FROM yourTable A INNER JOIN yourTable B ON A.Code >= B.Code
GROUP BY A.Code, A.Amount, A.Percentage;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sportdlockport]

Sorry. This solution doesn't seem to work in creating a cumulative balance column. With each record, the cumulative percentage column continues to add based on the next record's percentage amount.

Thanks,
Craig

 

[PHV]

Can you please post your SQL code, input sample and given result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sportdlockport]

Sure, here's the statement:

CLM_PROC_CD = Code
% = Percentage

I'd like to add a cumulative column to this statement.
Thanks,
Craig

SELECT [CPT Raw Source Table_User Table_Paid Amount].CLM_PROC_CD, [CPT Raw Source Table_User Table_Paid Amount].PROC_CD_DESC_1, [CPT Raw Source Table_User Table_Paid Amount].CLM_PAID_AMT, [CPT Paid Amt Summary_User Table].CLM_PAID_AMT AS [TOTAL PAID AMT], [CPT Raw Source Table_User Table_Paid Amount].[CLM_PAID_AMT]/[CPT Paid Amt Summary_User Table].[CLM_PAID_AMT] AS [%] INTO [CPT Audit Source_User Table_Paid Amt]
FROM [CPT Paid Amt Summary_User Table], [CPT Raw Source Table_User Table_Paid Amount]
ORDER BY [CPT Raw Source Table_User Table_Paid Amount].CLM_PAID_AMT;

 

[PHV]

Cumulative by CLM_PROC_CD or by CLM_PAID_AMT ?
Due the ORDER BY clause, I guess by CLM_PAID_AMT.
After this maketable query is run you may try this:
SELECT A.CLM_PROC_CD, A.PROC_CD_DESC_1, A.CLM_PAID_AMT, A.[TOTAL PAID AMT], A.[%], Sum(B.[%]) AS [Cumulative Balance %]
FROM [CPT Audit Source_User Table_Paid Amt] A INNER JOIN [CPT Audit Source_User Table_Paid Amt] B
ON A.CLM_PAID_AMT >= B.CLM_PAID_AMT
GROUP BY A.CLM_PROC_CD, A.PROC_CD_DESC_1, A.CLM_PAID_AMT, A.[TOTAL PAID AMT], A.[%];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sportdlockport]

Below is the statement for the new query. Access doesn't allow me to join as >=. Results show the "cumulative percentage" column the same as the % column.


SELECT [CPT Audit Source_User Table_Paid Amt].CLM_PROC_CD, [CPT Audit Source_User Table_Paid Amt].PROC_CD_DESC_1, [CPT Audit Source_User Table_Paid Amt].CLM_PAID_AMT, [CPT Audit Source_User Table_Paid Amt].[TOTAL PAID AMT], [CPT Audit Source_User Table_Paid Amt].[%], Sum([CPT Audit Source_User Table_Paid Amt_1].[%]) AS [SumOf%]
FROM [CPT Audit Source_User Table_Paid Amt] INNER JOIN [CPT Audit Source_User Table_Paid Amt] AS [CPT Audit Source_User Table_Paid Amt_1] ON [CPT Audit Source_User Table_Paid Amt].CLM_PAID_AMT = [CPT Audit Source_User Table_Paid Amt_1].CLM_PAID_AMT
GROUP BY [CPT Audit Source_User Table_Paid Amt].CLM_PROC_CD, [CPT Audit Source_User Table_Paid Amt].PROC_CD_DESC_1, [CPT Audit Source_User Table_Paid Amt].CLM_PAID_AMT, [CPT Audit Source_User Table_Paid Amt].[TOTAL PAID AMT], [CPT Audit Source_User Table_Paid Amt].[%];

 

[PHV]

Access doesn't allow me to join as >=
Which version ?
Another way:
SELECT A.CLM_PROC_CD, A.PROC_CD_DESC_1, A.CLM_PAID_AMT, A.[TOTAL PAID AMT], A.[%], (SELECT Sum(B.[%]) FROM [CPT Audit Source_User Table_Paid Amt] B WHERE B.CLM_PAID_AMT <= A.CLM_PAID_AMT) AS [SumOf%]
FROM [CPT Audit Source_User Table_Paid Amt] A;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sportdlockport]

It worked! Thanks!