Problem identifying records that 'Total' to a certain value?

[Krispy.]

Hi folks.

I have a table that consists of 428 invoice records which have a total value of £477631.78. Now, I have been asked to try and identify a list of those invoices that amount to a total of £120796.27.

Is there any code/program out there which can be used to help identify all possible combinations of Invoice records that would total £120796.27?

Thanks in advance,
Kev

[bilm_ks]

All possible combinations for 428 records? A huge number... Are there any special characteristics in this database like records with same values or integral values?

[Rassis]

I think it will be quite a hassle to achieve what you want. All possible combinations will certainly total a huge number! Nevertheless, here is what I would do if I were in your place:

1. Transfer all data (invoice values) into a spreadsheet;
2. Sort in ascending (or descending) order;
3. Select all the invoices whose values are less than £120796.27;
4. Draw a table similar to the one I join herewith with an example (for only 6 invoices). The number of all possible combinations in your case is given by [2^n - 1], where n stands for number of selected invoices – if you have, 100 say, you get (2^100 - 1) - an impossible task!;
5. Assume an upper and a lower acceptable limit for the total of the summation of all the selected invoices;
6. Filter the totals that are between those limits.

The example I add comprises only 6 invoices and, despite the low quantity, the number of all possible combinations is (2^6 -1 ) = 63.

If you really need to go forward with this task, I suggest that you show the example to somebody computer coding literate and perhaps, after understanding the logic behind it, he can conceive a shortcut. On the other hand, if you are involved in an auditing process – which I suspect you are –, please consult a good book on statistics and in particular on this subject. The following title, among others, can be of help “Statistics for Managers” by Levine, David M. et al., Prentice Hall, 1999, 2nd edition (auditing is covered between pages 458 and 463). There are auditing methods well established since long.

Hope this helps.

Rui

[Rassis]

It is a petty that I don´t get any answers...
I am really curious to know whether you Krispy managed to solve your problem and how you did it - particularly in the case you found it better not to follow my suggestion. Thanks in advance.

Rui