Records combination to a given value

[mbertuol]

Hello,

I need to know which records sum up to a given value. Lets say I have the following entries in a table:
1 = 78
2 = 56
3 = 120
4 = 24
5 = 480
6 = 89
7 = 489
8 = 98
9 = 54
10 = 569
If I provide to the query the value 752, I need to know which records sum this amount. In this example I would need the records 3, 5, 8, and 9 displayed.

The output can be either a query or report. But I need the VBA.

Any help is truly appreciated.

Thank you,

Mauro

 

[KenHigg]

Technically this seems possible, but it would take complicated algorithm. If there were a lot of records, it could chew up a lot of clock ticks... and return multiple records...

Just curious, what is the real world need for this (in non database terms)?

 

[mbertuol]

There are not too many records. Not more than 20.

My client issues invoices with different amounts. When their clients pay him, he wants to know each invoice that refers to a specific amount. Lets say the Client A pays him a total amount that corresponds for the payment of 4 or 5 of the due invoices. Which is invoice has been cleared?

If you have any idea, I would thank you.

 

[Rich]

isn't it normal practice to apply payments to the oldest invoices anyway? other than that it's probably easiest to use a multiselect listbox and compare the selected invoices with the total amount. But what happens if the customer has over/underpaid?

 

[KenHigg]

I think I would just do form and let the user do a check box by various inv's and have the form calc the total checked and the diff. Or find a math prof.

 

[DLB]

I think I would just ....... Or find a math prof.

I don't think a math prof can help much. For 20 records, there are 1,048,575 combinations.

Just think of the time it would take to go through the combinations, even if you got lucky and could get a match halfway through.

 

[mbertuol]

The fact is that due to the large number of combinations I cannot let the user make them. I need them made automatically.

What the client tells me is that he cannot guess which invoices have been paid as he issues invoices in a continued basis.

I also thought of a math prof. But there should be a way to combine records through VBA...

Please, if anybody has any suggestion...!

 

[Mike375]

The fact is that due to the large number of combinations I cannot let the user make them. I need them made automatically.

What the client tells me is that he cannot guess which invoices have been paid as he issues invoices in a continued basis.

I also thought of a math prof. But there should be a way to combine records through VBA...

Please, if anybody has any suggestion...!

Rich has hit the nail on the head with under and over payments.

By the way, the scenario you describe is very similar to life insurance companies and some policy holders. The policy holder may have several policies and pays all the renewals against one policy numbers or he pays incorrect amounts. Insurance companies try and minimise the problem by getting people to have the premiums debited from their bank.

I am an insurance agent and I can tell you that the insurance companies have a call up system when there are non matching payments and renewal notices. Now bear in mind that life insurance companies probably employ most of the world's actuaries They do have "amounts received" and "total owed" and so by processing all payments they know not to lapse policy 123abc which is showing as not being paid.

But the problem which Rich talks of does happen and then they ring the agent who sold the policies. In other words they have renewal (or invoices if you like) that are for $2367.53, $987.31 and $1234.21 and the person has paid $1500 and they want to know how the client wants the money allocated.

The specific reason I mentioned the life companies is because they are full of

1) Computer people
2) Actuaries

and they can't do what you are after.

If youur client has to do it the way the insurance company does things, that is, each invoice must be allocated its own payment, then I think you will need a call up system.

Mike

 

[Franknstuff]

Maybe

I don't think a math prof can help much. For 20 records, there are 1,048,575 combinations.

Just think of the time it would take to go through the combinations, even if you got lucky and could get a match halfway through.

You can mathmatically remove all numbers larger than the result as you go.
This reducing the possiable combinations.

A statistic analisys, or a math prof. should be able to give you an answer.

 

[mbertuol]

Thank you mike.

 

[DLB]

Maybe not

You can mathmatically remove all numbers larger than the result as you go.
This reducing the possiable combinations.

If you take a look at the sample data in the first post, you will notice that you can't remove any number at all as they are all smaller than the result.

 

[Franknstuff]

Problem

The problem is that there maybe several possible combinations which would throw off later calculations.

I really did'nt mean remove numbers, but ignore them.

subtract 500 from 700

any number above 200 can be ignored.

subtract 100 form 200

any number above 100 can be ignored.

 

[WayneRyan]

Hi m,

I faced this a while ago to find a "zero-sum" solution.
Basically, I just put the #s in a vertical array and
put a large binary number to the right of them.

Not elegant, but it works.

Dim Nums(100, 2) As Integer
Dim TotalNums As Integer

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim ThePower As Integer
Dim LeftOver As Integer
Dim i As Integer
Dim Temp As Integer
'
' Read in all numbers from a table
'
Set dbs = CurrentDb
sql = "Select * from TheNumbers"
Set rst = dbs.OpenRecordset(sql)
' Read them in and count
TotalNums = 1
While Not rst.EOF And Not rst.BOF
  Nums(TotalNums, 1) = rst!TheNumber
  TotalNums = TotalNums + 1
  rst.MoveNext
  Wend
TotalNums = TotalNums - 1
'
' Now try every combination 0 -> 2^n
'
For i = 1 To 2 ^ TotalNums
  ' Break i down into binary
  Temp = i
  For j = TotalNums To 0 Step -1
     If Temp >= 2 ^ j Then
        Nums(j, 2) = 1
        Temp = Temp - 2 ^ j
     Else
        Nums(j, 2) = 0
     End If
     Next j
   ' Now check the sum of (columns 1 * 2)
  Temp = 0
  For j = 1 To TotalNums
     Temp = Temp + Nums(j, 1) * Nums(j, 2)
     Next j
  If Temp = 752 Then   
     MsgBox ("Found 1")
  End If
  Next i

At least its a solution,
Wayne

 

[Mike375]

Provided that the service or product your client supplies does not depend on a particular invoice being paid, such as insurance policies or different phone lines etc. then the following system might do the job for you.

Have the invoice records setup so that those paid in full are separated.

Then have a system that opens the invoices for the client and in ascending order of size. We assume $2000 has been paid and the following invoice amounts are outstanding.

300
600
800
900
1100

If the $2000 is bigger than the first then the first gets and entry "paid' and a calculated field (or the result of a SetValue action) gets 2000 -300 =1700

That value is applied to the invoice of 600 which will leave 1100.
That value is applied to the next invoice which will leave 300

So we have invoices 300, 600 and 800 paid and $300 left. That $300 could be added to the persons next payment and assuming no more invoices had gone out the next time things were run the invoices called up would be the 900 and 1100.

You could make a macro or code that runs through your client's entire customer list opening the invoices and doing the above procedure and then moving to the next record. From an Access point of view I am certain there will be nicer ways of doing it but I think something like that might get you over the line.

As a side note, I use to have a system similar to what I outlined for inserting client ID number into policy benefits we would get from the insurance company. The problem is the insurance company treats a "policy number" as a client and as such they might have the one person entered in their system 3 times. However, we only want the person in the records once and a subform showing all 9 policy benefits with the different policy numbers. Quite some time ago I changed the system to do it through a query and a copy and paste. I have a few other insurance people who use the system and all of them wanted to retain the system of a macro running through all the records on the screen with Echo at Yes

Mike

 

[The_Doc_Man]

This is definitely a case where you don't know (or haven't told us) enough to give you the "right" answer. But you have been given some good advice. I'll try to bring out the issues you need to address.

1. Your database should be a model of your company's rules on payments. What is your company's written rule (in its contract) about how payments are applied when a customer makes a payment?

2. Can you charge interest for overdue invoices? If so, you must also consult your company's lawyers regarding your fiduciary responsibility under your local and national laws. It might actually be ILLEGAL for you to apply payments in any order other than "oldest debts first." Because if you can choose the order, you suddenly have a way to charge your customers more money through the miracles of compound interest, just by doing a "newest debts first" algorithm rather than an "oldest debts first" method. If the oldest debt never quite gets paid off, you can collect pot-loads of interest from it. Which might, according to some jurisdictions, look suspiciously like loan-sharking or usury. Which would, in effect, cause your company to violate a law!

3. You can do a thousand different things to guess the invoices that would be paid when you get X amount of money. But if you are guessing, you are not doing your customer, your shareholders, or your management any good. This is NOT repeat NOT a decision you should make. You are handling a customer's money. It is usually considered your DUTY to ASK THE CUSTOMER how they want that money applied (unless my line-items 1 & 2 provide all the guidance you need on this question.) You should NEVER be placed in the situation of having to guess what to do with it.

HINT on #3 - if you ARE in such a situation, it is because your company officers didn't do such a good job on payment rules in the first place.