I have an Access application wherein date-wise invoices, payments are listed along with cumulative data. I need to find out the date on which each invoice has beeen paid off. My Access query result looks as under:
DateInvAmtDueDatePaymtCum_InvCum_Paymt12-Oct-10264,33226-Nov-100264,332013-Oct-1028,50227-Nov-100292,834014-Oct-1044,01628-Nov-100336,850017-Oct-1055,19101-Dec-100392,041020-Oct-1071,25504-Dec-100463,296021-Oct-1043,57505-Dec-100506,871010-Nov-1029,36025-Dec-100536,231024-Nov-1024,90008-Jan-110561,131029-Nov-10028,074561,13128,07414-Dec-100471,194561,131499,26820-Jan-11117,49506-Mar-110678,626499,26829-Jan-11267,61615-Mar-110946,242499,26807-Feb-11074,084946,242573,35209-Feb-1128,54526-Mar-110974,787573,35210-Feb-119,24327-Mar-110984,030573,35213-Feb-11397,39230-Mar-1101,381,422573,35220-Feb-11053,4461,381,422626,79810-Mar-11041,6491,381,422668,44717-Mar-110263,6021,381,422932,04928-Mar-11037,2201,381,422969,26831-Mar-110391,4311,381,4221,360,69907-Apr-11115,20022-May-1101,496,6221,360,69912-Apr-1112,06727-May-1101,508,6881,360,69926-Apr-1128,80010-Jun-1101,537,4881,360,69910-May-11206,67824-Jun-1101,744,1671,360,69919-May-1173,63303-Jul-1101,817,7991,360,69922-May-1140,11806-Jul-1101,857,9171,360,699
In an Excel I can find out the date on which each invoice has been paid off. I have go row by row; look at the cumulative invoice value, look at the cumulative payment value, identify that date wherein the cumulative payment is greater than the cumulative invoice first time and take the same as the payment date. Finally, the result looks as under:
DateInvAmtDueDatePaymtCum_InvCum_PaymtInvoicePaidOn12-Oct-10264,33226-Nov-100264,332014-Dec-1013-Oct-1028,50227-Nov-100292,834014-Dec-1014-Oct-1044,01628-Nov-100336,850014-Dec-1017-Oct-1055,19101-Dec-100392,041014-Dec-1020-Oct-1071,25504-Dec-100463,296014-Dec-1021-Oct-1043,57505-Dec-100506,871007-Feb-1110-Nov-1029,36025-Dec-100536,231007-Feb-1124-Nov-1024,90008-Jan-110561,131007-Feb-1129-Nov-10028,074561,13128,07414-Dec-100471,194561,131499,26820-Jan-11117,49506-Mar-110678,626499,26817-Mar-1129-Jan-11267,61615-Mar-110946,242499,26828-Mar-1107-Feb-11074,084946,242573,35209-Feb-1128,54526-Mar-110974,787573,35231-Mar-1110-Feb-119,24327-Mar-110984,030573,35231-Mar-1113-Feb-11397,39230-Mar-1101,381,422573,35220-Feb-11053,4461,381,422626,79810-Mar-11041,6491,381,422668,44717-Mar-110263,6021,381,422932,04928-Mar-11037,2201,381,422969,26831-Mar-110391,4311,381,4221,360,69907-Apr-11115,20022-May-1101,496,6221,360,69912-Apr-1112,06727-May-1101,508,6881,360,69926-Apr-1128,80010-Jun-1101,537,4881,360,69910-May-11206,67824-Jun-1101,744,1671,360,69919-May-1173,63303-Jul-1101,817,7991,360,69922-May-1140,11806-Jul-1101,857,9171,360,699
Can the above task be accomplished in Access ? How ?
Rgds
DateInvAmtDueDatePaymtCum_InvCum_Paymt12-Oct-10264,33226-Nov-100264,332013-Oct-1028,50227-Nov-100292,834014-Oct-1044,01628-Nov-100336,850017-Oct-1055,19101-Dec-100392,041020-Oct-1071,25504-Dec-100463,296021-Oct-1043,57505-Dec-100506,871010-Nov-1029,36025-Dec-100536,231024-Nov-1024,90008-Jan-110561,131029-Nov-10028,074561,13128,07414-Dec-100471,194561,131499,26820-Jan-11117,49506-Mar-110678,626499,26829-Jan-11267,61615-Mar-110946,242499,26807-Feb-11074,084946,242573,35209-Feb-1128,54526-Mar-110974,787573,35210-Feb-119,24327-Mar-110984,030573,35213-Feb-11397,39230-Mar-1101,381,422573,35220-Feb-11053,4461,381,422626,79810-Mar-11041,6491,381,422668,44717-Mar-110263,6021,381,422932,04928-Mar-11037,2201,381,422969,26831-Mar-110391,4311,381,4221,360,69907-Apr-11115,20022-May-1101,496,6221,360,69912-Apr-1112,06727-May-1101,508,6881,360,69926-Apr-1128,80010-Jun-1101,537,4881,360,69910-May-11206,67824-Jun-1101,744,1671,360,69919-May-1173,63303-Jul-1101,817,7991,360,69922-May-1140,11806-Jul-1101,857,9171,360,699
In an Excel I can find out the date on which each invoice has been paid off. I have go row by row; look at the cumulative invoice value, look at the cumulative payment value, identify that date wherein the cumulative payment is greater than the cumulative invoice first time and take the same as the payment date. Finally, the result looks as under:
DateInvAmtDueDatePaymtCum_InvCum_PaymtInvoicePaidOn12-Oct-10264,33226-Nov-100264,332014-Dec-1013-Oct-1028,50227-Nov-100292,834014-Dec-1014-Oct-1044,01628-Nov-100336,850014-Dec-1017-Oct-1055,19101-Dec-100392,041014-Dec-1020-Oct-1071,25504-Dec-100463,296014-Dec-1021-Oct-1043,57505-Dec-100506,871007-Feb-1110-Nov-1029,36025-Dec-100536,231007-Feb-1124-Nov-1024,90008-Jan-110561,131007-Feb-1129-Nov-10028,074561,13128,07414-Dec-100471,194561,131499,26820-Jan-11117,49506-Mar-110678,626499,26817-Mar-1129-Jan-11267,61615-Mar-110946,242499,26828-Mar-1107-Feb-11074,084946,242573,35209-Feb-1128,54526-Mar-110974,787573,35231-Mar-1110-Feb-119,24327-Mar-110984,030573,35231-Mar-1113-Feb-11397,39230-Mar-1101,381,422573,35220-Feb-11053,4461,381,422626,79810-Mar-11041,6491,381,422668,44717-Mar-110263,6021,381,422932,04928-Mar-11037,2201,381,422969,26831-Mar-110391,4311,381,4221,360,69907-Apr-11115,20022-May-1101,496,6221,360,69912-Apr-1112,06727-May-1101,508,6881,360,69926-Apr-1128,80010-Jun-1101,537,4881,360,69910-May-11206,67824-Jun-1101,744,1671,360,69919-May-1173,63303-Jul-1101,817,7991,360,69922-May-1140,11806-Jul-1101,857,9171,360,699
Can the above task be accomplished in Access ? How ?
Rgds