Invoice Payments Query (1 Viewer)

[Sarma]

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

 

[CJ_London]

can you present your data a bit more clearly (use advanced reply and surround with code tags).

e.g dates look like they are just day and month
is it Inv Amt (2 fields) or InvAmt (1 field)
etc

It is certaining possible in Access (it is a common requirement) but difficult to advise as currently presented

 

[Sarma]

When I wrote, it appeared normal but the data got mixed up because I copy / pasted two excel tables. I do not know how to attach them.

The query gave me result with the following fields:

Date
Invoice Amount
Due date of invoicee Amount
Cumulative invoice amount
Cumulative Payments received

What I need is - all the above + the date on which each invoice has been paid off. This is the day when the cumulative payments is greater than the cumulative invoices first time.

Hope now clear. Thanks for interest.

 

[CJ_London]

You don't need to attach - just below where you posted there is a button called 'Go Advanced'.

Click on this and a more advance reply box will open. Along the top there are a number of options, including # - this is the code tag. Highlight the pasted data and click on the code tag.

The problem I have at the moment is you have said

I need to find out the date on which each invoice has beeen paid off

But your data does not contain an invoice number - so does it relate to only one invoice?

 

[Sarma]

On a single day, there may be 2 or three invoices. Hence, I have summed them as Invoice amount. This amount has to be paid off after 45 days. That is why the invoice date is there and also the due date. Against this invoice due date, I need to find when it was paid off. This is the day when for the first time, the cumulative payments exceeds the cumulative invoices, going from row to row. If that date doesn't exist, probably a code can appear "Not paid".

 

[CJ_London]

Hi Sarma,

OK, please do as I requested and provide the data in a readable format otherwise I am unable to help. Any answer I provide could only be general and from previous experience what could be answered in one or two posts will take 10 times that. I give my time freely to help but I don't want to waste it.

 

[Sarma]

Appl_DateInvDueDatePaymtCum_InvCum_PaymtPayment Date12-10-10264,33240,5080264,332014-12-1013-10-1028,50240,5090292,834014-12-1014-10-1044,01640,5100336,850014-12-1017-10-1055,19140,5130392,041014-12-1020-10-1071,25540,5160463,296014-12-1021-10-1043,57540,5170506,8710 10-11-1029,36040,5370536,2310 24-11-1024,90040,5510561,1310 29-11-100 28,074561,13128,074 14-12-100 471,194561,131499,268 20-01-11117,49540,6080678,626499,268 29-01-11267,61640,6170946,242499,268 09-02-1128,54540,6280974,787499,268 10-02-119,24340,6290984,030499,268 13-02-11397,39240,63201,381,422499,268 20-02-110 53,4461,381,422552,714 07-03-110 74,0841,381,422626,798 10-03-110 41,6491,381,422668,447 17-03-110 263,6021,381,422932,049 28-03-110 37,2201,381,422969,268 31-03-110 391,4311,381,4221,360,699 07-04-11115,20040,68501,496,6221,360,699 12-04-1112,06740,69001,508,6881,360,699 26-04-1128,80040,70401,537,4881,360,699 10-05-11206,67840,71801,744,1671,360,699 19-05-1173,63340,72701,817,7991,360,699 22-05-1140,11840,73001,857,9171,360,699 25-05-110 113,4721,857,9171,474,171 29-05-110 11,8861,857,9171,486,057 30-05-1137,38940,73801,895,3061,486,057 31-05-1136,84940,73901,932,1551,486,057 06-06-1136,96240,74501,969,1171,486,057 09-06-1163,35940,74802,032,4761,486,057 15-06-1143,36740,75402,075,8431,486,057 16-06-1138,03940,75502,113,8821,486,057 20-06-1143,40540,75902,157,2871,486,057 21-06-1190,94840,76002,248,2351,486,057 22-06-110 28,3682,248,2351,514,425 26-06-11103,84740,765203,5782,352,0821,718,003 28-06-1190,84140,76702,442,9231,718,003 06-07-1137,89040,77502,480,8131,718,003 07-07-110 112,0442,480,8131,830,048 11-07-1141,91740,78002,522,7301,830,048 12-07-1141,12640,78102,563,8561,830,048 17-07-110 73,1242,563,8561,903,172 24-07-110 36,4082,563,8561,939,579 27-07-110 29,3692,563,8561,968,948 31-07-11288,80840,80002,852,6641,968,948 03-08-110 563,1992,852,6642,532,147 07-08-110 288,8082,852,6642,820,955 10-08-11434,40040,81003,287,0642,820,955 16-08-11376,48040,81603,663,5442,820,955 24-08-1194,34840,82403,757,8922,820,955 07-09-1149,48940,83803,807,3812,820,955 08-09-1161,41340,83903,868,7942,820,955 11-09-1114,90240,84203,883,6972,820,955 18-09-1131,90040,849434,4003,915,5973,255,355 20-09-1142,02840,85103,957,6243,255,355 02-10-110 370,8333,957,6243,626,188 03-10-1129,93940,86403,987,5633,626,188 06-10-1112,22640,86703,999,7893,626,188 10-10-1126,46040,87104,026,2493,626,188 13-10-110 92,9334,026,2493,719,120 23-10-11194,88740,88404,221,1363,719,120 26-10-110 109,2394,221,1363,828,359 30-10-110 14,6794,221,1363,843,038 03-11-110 142,5524,221,1363,985,590 09-11-1163,35640,90104,284,4923,985,590 15-11-11292,00040,90704,576,4923,985,590 16-11-11170,03240,90804,746,5243,985,590 27-11-110 292,0004,746,5244,277,590 28-11-1172,39940,92004,818,9234,277,590 04-12-1168,01340,92604,886,9364,277,590 19-12-115,85040,94104,892,7864,277,590 21-12-1126,66440,94304,919,4494,277,590 25-12-110 191,9644,919,4494,469,555 31-12-110 62,4054,919,4494,531,960 04-01-1292,68140,95705,012,1304,531,960 05-01-1246,04440,958167,4825,058,1744,699,441 08-01-1291,23340,96105,149,4074,699,441 10-01-129,75040,96305,159,1574,699,441 15-01-120 71,3135,159,1574,770,754 22-01-120 66,9935,159,1574,837,747 29-01-1227,33440,98205,186,4914,837,747 06-02-1220,92440,99005,207,4154,837,747 08-02-120 26,2645,207,4154,864,010 20-02-1242141,00405,207,8364,864,010 21-02-126,20041,00505,214,0364,864,010 23-02-120 136,6445,214,0365,000,654 26-02-120 99,4685,214,0365,100,122 04-03-1230,48041,01705,244,5165,100,122 05-03-1230,48041,01805,274,9965,100,122 06-03-1245,72041,01905,320,7165,100,122 11-03-1246,39741,02405,367,1125,100,122 15-03-1261,17041,02805,428,2825,100,122 18-03-1215,29241,03126,9245,443,5745,127,046 25-03-1223,71841,03805,467,2925,127,046 26-03-1234,12241,03905,501,4145,127,046 27-03-1223,71841,04005,525,1325,127,046 28-03-1223,71841,04105,548,8505,127,046 29-03-1223,71841,04220,6105,572,5685,147,656 01-04-12118,64841,04505,691,2165,147,656 05-04-1223,71841,04905,714,9345,147,656 12-04-120 6,1075,714,9345,153,763 19-04-1224,29141,06305,739,2265,153,763 29-04-120 105,0805,739,2265,258,843 03-05-120 105,9535,739,2265,364,796 07-05-127,38141,08105,746,6075,364,796 14-05-120 15,0635,746,6075,379,859 17-05-1294,29841,091127,0595,840,9055,506,918 27-05-120 140,2315,840,9055,647,148 29-05-12320,91041,10306,161,8155,647,148 30-05-1214,19641,10406,176,0115,647,148 07-06-120 23,9276,176,0115,671,075 16-06-1298,51541,12106,274,5265,671,075 01-07-1224,42541,13606,298,9515,671,075 04-07-120 92,8846,298,9515,763,959 05-07-12154,78441,14006,453,7355,763,959 16-07-120 320,9106,453,7356,084,869 18-07-12343,88841,15306,797,6236,084,869 30-07-1271,57041,16506,869,1926,084,869 31-07-1225,46041,16606,894,6526,084,869 05-08-120 7,3816,894,6526,092,250 06-08-1224,28741,17206,918,9406,092,250 23-08-120 176,5216,918,9406,268,771 26-08-120 338,7296,918,9406,607,500 27-08-1222,60341,19306,941,5436,607,500 28-08-1235,41841,19406,976,9616,607,500 30-08-12191,99641,19607,168,9576,607,500 02-09-129,52641,19914,1967,178,4836,621,697 17-09-1222,47641,21407,200,9596,621,697 23-09-1233,47041,22007,234,4296,621,697 27-09-120 8,2567,234,4296,629,952 02-10-1237,69641,22907,272,1256,629,952 07-10-1233,31541,234250,0187,305,4406,879,970 14-10-120 30,1377,305,4406,910,107 15-10-12123,16441,24207,428,6046,910,107 16-10-1245,66141,24307,474,2656,910,107 24-10-127,49841,25107,481,7636,910,107 30-10-1226,25141,25707,508,0146,910,107 31-10-120 97,0307,508,0147,007,137 11-11-120 152,1447,508,0147,159,280 18-11-1232,85841,27607,540,8737,159,280 21-11-120 9,3837,540,8737,168,664 05-12-12180,49341,29307,721,3667,168,664 06-12-1290,22741,29407,811,5937,168,664 08-12-1244,24641,29607,855,8397,168,664 09-12-120 195,1687,855,8397,363,831 10-12-1232,74241,29807,888,5807,363,831 16-12-120 40,2017,888,5807,404,032 17-12-1225,66341,30507,914,2437,404,032 24-12-1289,78441,31208,004,0277,404,032 25-12-12134,71341,31308,138,7407,404,032 26-12-1289,82941,31425,8578,228,5707,429,890 13-01-1323,60041,33208,252,1707,429,890 16-01-130 32,3668,252,1707,462,255 20-01-1328,32041,33908,280,4907,462,255 27-01-130 310,2418,280,4907,772,496 03-02-130 25,2788,280,4907,797,775 13-02-130 341,8628,280,4908,139,637 25-02-1388,77841,37508,369,2678,139,637 26-02-13155,21141,37608,524,4798,139,637 11-03-136,83141,38908,531,3098,139,637 17-03-1351,57341,39508,582,8838,139,637 19-03-130 27,8958,582,8838,167,532 21-03-1354,64541,39908,637,5278,167,532 25-03-1314141,40308,637,6688,167,532 27-03-1349,29241,40508,686,9618,167,532 28-03-136,37841,40608,693,3398,167,532 03-04-1314,20441,41208,707,5438,167,532 09-04-13193,77341,41808,901,3168,167,532 11-04-1327,05041,42008,928,3668,167,532 16-04-1385,62841,42509,013,9948,167,532 25-04-130 240,3299,013,9948,407,861 28-04-130 6,7289,013,9948,414,589 30-04-13193,77341,43909,207,7678,414,589 05-05-130 50,8009,207,7678,465,389 15-05-130 108,6619,207,7678,574,049 27-05-13216,61441,46609,424,3818,574,049 28-05-1326,84141,46709,451,2228,574,049 29-05-1386,08141,46809,537,3038,574,049 30-05-13289,78641,469190,8669,827,0888,764,916 02-06-130 124,9799,827,0888,889,894 03-06-1324,03741,47309,851,1258,889,894 [CODE][CODE]

[/CODE]
[/CODE]

 

[Sarma]

I have done what you wanted me to. Hope you will now get my file.

 

[Brianwarnock]

No sorry but that is indecipherable ok so for the first record we can work out the appl_date and the date or payment date but in between is just impossible both for the field names and the data.

You initially said it was in a spreadsheet, why can you not attach it?

CJ needs to see the names and data in an easy to view tabular layout.

Brian

 

[Sarma]

Attached. Hope it reaches.

 

[Sarma]

Please use this example. Reject the earlier one. Rgds.

 

[CJ_London]

I can now see the data but making a couple of assumptions

1. All these transactions relate to one customer or supplier
2. The lines without entries (e.g. 1/11/12,,6/11/12 etc) can be ignored

The query you need will be along the following lines

SELECT InvDate, Invoice, DueDate, Paymt, Cum_Paymt, [Payment made on], Nz((SELECT Min(InvDate) FROM MyTable as Tmp WHERE Cum_Paymt>=MyTable.Cum_Inv),"Not Paid") AS LastPayt
FROM MyTable

I've attached the result of this query so you can compare with your excel method. You will need to change MyTable to the name of your table and correct for differences in field names

 

[Sarma]

Your both assumptions are right. Can you please try out the example given later ?

 

[Sarma]

CJ: I have tried out. It works. Just a small suggestion. Can it avoid 'Not Paid' in the row where invoice value =0 ? Grateful to your help.

 

[CJ_London]

SELECT Table7.InvDate, Table7.Invoice, Table7.DueDate, Table7.Paymt, Table7.Cum_Paymt, Table7.[Payment made on], [COLOR=red]IIf([invoice]=0,"",[/COLOR]Nz((SELECT Min(InvDate) FROM Table7 as Tmp WHERE Cum_Paymt>=table7.Cum_Inv),"Not Paid")[COLOR=red])[/COLOR] AS LastPayt
FROM Table7;

 

[Sarma]

CJ: Better still is if we can have the word 'Not Applicable' when invoice value = 0

 

[CJ_London]

Sarma,

You should be able to work that out yourself! but here it is

SELECT Table7.InvDate, Table7.Invoice, Table7.DueDate, Table7.Paymt, Table7.Cum_Paymt, Table7.[Payment made on],[COLOR=red] IIf([invoice]=0,"Not Applicable"[/COLOR],Nz((SELECT Min(InvDate) FROM Table7 as Tmp WHERE Cum_Paymt>=table7.Cum_Inv),"Not Paid")) AS LastPayt
FROM Table7;

 

[Sarma]

Wow. Brilliant. Grateful. Worked like a charm.