For Access10 I need an expression to search for currency numbers that
will add up to a certain figure.

For example, if the books are out by $1055.69, I want to search a
query column for groups of numbers that will add up to $1055.69.

I suppose this will involve taking the top number of a column and
adding to this number any of the lower ones to give the $1055.69.

Any help warmly welcomed.

Peter

That's an algorithm you'd need to develop. You open the table as a dao
recordset with vba code and loop over the recordset in your algorithm.
Maybe there are some routines already developed you could translate
into vba. There is no solution as a query or anything built in to
access itself.

It seems though there would probably be multiple correct answers in
any large recordset.

Peter Jason wrote:
with Christian that you would have to code this yourself. I am usually
opposed to sending a recordset loop to do a query's job, but in this case,
this is not a query's "job".
Here is a stackoverflow thread about a similar question:
http://stackoverflow.com/questions/1...r-given-number

On Mon, 31 Oct 2011 12:03:07 -0500, "Bob Barrows"
<reb01501 (AT) NOyahooSPAM (DOT) com> wrote:

Thanks. This is far beyond my abilities because I have designed my
application using wizards and vba code cut/pasted. I'll have to hire
a programmer.

Regards.

On Tue, 25 Oct 2011 09:13:14 +1100, Peter Jason <pj (AT) jostle (DOT) com> wrote:

If you are looking for pairs which add up you could use a query like

SELECT table1.id, table1.C, table1_1.id, table1_1.C
FROM table1, table1 AS table1_1
WHERE (((table1_1.id)>[table1].[id]) AND
(([table1].[C]+[table1_1].[C])=1055.69));

if you want three figures which add up ...

SELECT table1.id, table1.C, table1_1.id, table1_1.C, table1_2.id,
table1_2.C
FROM table1, table1 AS table1_1, table1 AS table1_2
WHERE (((table1_1.id)>[table1].[id]) AND
(table1_2.id)>[table1_1].[id]) AND
(([table1].[C]+[table1_1].[C]+[table1_2].[C])=1055.69);

etc

queries like this without joins can take a lot of resources if the
tables are very large!

I would suggest exporting your recordset to Excel in a column (or
row). Then use Excel's solver to get your answer. Have the column (or
row, if that is the case) right next to your recordset be binary
values to includ/exclude the value from a summation (YourRSValue*[1/0]
and sum all the products). Set Excel Solver to change the binary
values until your goal is achieved. Here is a website with the Excel
Solver object model explained.

http://msdn.microsoft.com/en-us/library/ff846376.aspx

Please note that the Solver that comes with Excel has a maximum of 255
variables, so your recordset will have to be smaller than this if you
are using the standard solver. More advanced solvers with more
variables are available from Frontline Systems (developer of Excel's
Solver) if you have the cash for it. They're not cheap, though. Other
math applications, such as Mathematica, or SAS, have built in solvers
that can do more than the standard Excel Solver, but using these
applications will probably require more development time one your
part.

Steve

[posted and mailed]

Peter Jason <pj (AT) jostle (DOT) com> wrote in
news:ggoba71r74fr5p4et5mlpcml7fco6umnqq (AT) 4ax (DOT) com:

A long time ago on a computer far away (OK, it was 1984 and written on a
Commodore 64) I tried to do something very similar. I eventually realised
that from a group of N records, I'd need to do N factorial comparisons,
and on a 1 megahertz processor that was going to take days of processing.
I decided to write some code that just picked random numbers from my list
to add to a running total, checking each time if the total was equal
(hooray!) or greater (bother!) than the target total, and set it to run
and quit after one hour.
I was amazed to find that I got a result in less than a minute.

Your problem intrigued me enough to sit down and hack out an updated
version. I had a list of 3,749 numbers for my test-bed, and running it on
my test list gave me 25 results in 5 seconds.

Here's my code, "as is" from the test. It could be better, the variable
names are lazy, it needs a nice form for input and presentation, (Ah! The
nostalgia of InputBoxes) and my method for avoiding duplicates is
downright sloppy, but if it works for you as a starting point, then
great!

I've added a Sub to output the result to a file in your Temp Folder.

I have no idea if it will run under Access 10, or if it even does what
you actually want. I'm also assuming that because it seems to work on my
data it will work on other data without fudging some of the array sizes.

I'll paste a test run output under the code.
Whoops! I've just done that, and it was longer than I thought. Never
mind, all the more to check.

It looked OK to me, but it's six hours past my bed-time.
If you get no results at first, add a few more seconds in this line:

SecondsToSpend = Timer + 5 'Five seconds was plenty for 3000 items.

Cheers,
Alan


Written in Access 97
<---------------- Under This ------------------>
Option Compare Database
Option Explicit

Sub Peter()
Dim db As DAO.Database
Dim r As DAO.Recordset
Set db = CurrentDb

Dim Msg$
Set r = db.OpenRecordset("YourTableAndField") ' A list of amounts

Dim X As Currency
' X = 1055.69 'The total I want.
X = InputBox("What's the total you're after?")
Dim C As Long 'Just a count of how many tries.
Dim D$ 'For oldfashioned formatting

Dim SecondsToSpend As Long 'One second was plenty for me.
Dim NumberOfItems As Long 'Number of dollar amounts.
Dim I As Long 'General purpose count
Dim J As Long 'Count of items to be tested
Dim K As Long 'Count for duplicate elimination
Dim CheckDup(100) '100 was plenty
Dim FoundDupFlag As Boolean
Dim TryThis As Long 'Choose random element from the array of amounts.

Dim TryThese(100) As Currency 'Build up the tested amounts (in
TryTotal)
Dim TryTotal As Currency 'Running total of current items

r.MoveLast
NumberOfItems = r.RecordCount
r.MoveFirst

'Put items into array
ReDim theList(NumberOfItems) As Currency
For I = 1 To NumberOfItems
theList(I) = r(0)
r.MoveNext
Next

'
SecondsToSpend = Timer + 5 'Five seconds was plenty for 3000 items.
While Timer < SecondsToSpend
J = 0
K = 0
TryTotal = 0
FoundDupFlag = False
C = C + 1
While TryTotal < X
TryThis = Int(NumberOfItems * Rnd) + 1

For I = 1 To K
If TryThis = CheckDup(I) Then
FoundDupFlag = True
Exit For
End If
Next
K = K + 1
CheckDup(K) = TryThis
If FoundDupFlag Then
FoundDupFlag = False
Else
J = J + 1
TryThese(J) = theList(TryThis)
TryTotal = TryTotal + theList(TryThis)
End If
Wend
If TryTotal = X Then
Msg = Msg & vbCrLf
For I = 1 To J
D$ = " "
RSet D$ = Format(TryThese(I), "Currency")
Msg = Msg & D$ & vbCrLf
'Debug.Print D$
Next
'Debug.Print
RSet D$ = Format(TryTotal, "Currency")
Msg = Msg & D$ & vbCrLf
'Debug.Print TryTotal
End If
Wend
TempPrint Msg
End Sub

Sub TempPrint(Msg)
Dim PrintPlace$
PrintPlace$ = Environ("temp") & "\OverWriteMe.txt"
Dim FileNum As Integer
FileNum = FreeFile
Open PrintPlace$ For Output As #FileNum
Print #FileNum, Msg
Close FileNum
PrintPlace$ = "notepad.exe " & PrintPlace$
Msg = Shell(PrintPlace$, 1) 'Re-use Msg
End Sub
<---------------- Over This ------------------>

<---------------
Sample Output.


$222.39
$28.86
$125.00
$48.98
$49.99
$29.63
$8.35
$180.00
$150.00
$212.49
$1,055.69

$150.00
$17.45
$29.99
$17.80
$60.00
$15.51
$627.98
$100.00
$36.96
$1,055.69

$150.00
$10.00
$3.50
$600.00
$150.00
$100.00
$42.19
$1,055.69

$100.00
$235.39
$125.00
$41.80
$39.00
$10.94
$3.50
$3.00
$217.17
$176.89
$100.00
$3.00
$1,055.69

$55.00
$29.10
$3.00
$60.00
$16.80
$204.59
$627.20
$60.00
$1,055.69

$188.65
$165.34
$22.09
$150.00
$125.00
$225.25
$129.00
$23.41
$15.40
$9.55
$2.00
$1,055.69

$40.44
$125.00
$180.00
$75.00
$15.50
$150.00
$30.94
$100.00
$50.48
$38.33
$100.00
$150.00
$1,055.69

$19.50
$54.57
$184.37
$125.00
$100.00
$125.00
$24.90
$125.00
$1.75
$140.00
$150.00
$5.60
$1,055.69

$183.40
$627.98
$0.00
$204.36
$39.95
$1,055.69

$125.00
$125.00
$150.00
$100.00
$0.07
$150.00
$3.00
$3.50
$33.00
$26.40
$208.92
$21.70
$109.10
$1,055.69

$239.75
$317.00
$29.27
$36.44
$200.00
$180.00
$53.23
$1,055.69

$58.37
$150.00
$44.00
$3.78
$184.37
$17.19
$20.00
$14.95
$208.92
$100.00
$51.04
$49.99
$125.00
$28.08
$1,055.69

$89.00
$150.00
$29.21
$125.00
$100.00
$15.68
$0.46
$59.90
$150.00
$186.44
$150.00
$1,055.69

$125.00
$125.00
$80.97
$100.00
$150.00
$217.17
$15.51
$242.04
$1,055.69

$222.39
$2.00
$207.05
$42.00
$20.00
$100.30
$125.00
$125.00
$17.52
$125.00
$58.37
$11.06
$1,055.69

$0.50
$125.00
$40.44
$150.00
$10.00
$40.78
$1.75
$202.85
$150.00
$184.37
$150.00
$1,055.69

$212.49
$163.50
$125.00
$200.65
$37.05
$317.00
$1,055.69

$217.17
$15.09
$17.04
$56.25
$229.95
$24.95
$39.72
$33.90
$27.98
$125.00
$100.30
$165.34
$3.00
$1,055.69

$176.89
$176.89
$281.61
$100.30
$20.00
$150.00
$2.00
$3.00
$125.00
$20.00
$1,055.69

$150.00
$150.00
$222.39
$150.00
$180.00
$100.00
$100.30
$3.00
$1,055.69

$100.00
$10.00
$157.75
$176.34
$200.00
$22.95
$200.00
$188.65
$1,055.69

$705.37
$48.98
$176.34
$125.00
$1,055.69

$200.00
$23.46
$125.00
$17.47
$100.00
$35.06
$188.65
$5.00
$1.40
$15.35
$129.00
$10.94
$150.00
$25.50
$28.86
$1,055.69

$188.65
$40.70
$3.00
$42.25
$200.00
$50.00
$13.58
$3.15
$150.00
$29.99
$184.37
$150.00
$1,055.69

$150.00
$184.37
$40.89
$32.76
$125.76
$208.92
$100.00
$0.50
$212.49
$1,055.69

$100.00
$31.07
$400.00
$150.00
$16.00
$6.80
$184.37
$150.00
$17.45
$1,055.69

$21.95
$204.59
$150.00
$3.50
$180.00
$33.08
$0.00
$217.17
$245.40
$1,055.69

On Thu, 03 Nov 2011 12:32:25 -0500, Alan Carpenter <Not (AT) iHome (DOT) nz>
wrote:

Sorry for the slow reply, but I have been busy with other things.
Thank you very much for the above code which I will try and add to my
database. At the end of financial year the search for missing $
amounts of entries is tedious and so I need something automated. I'll
get back with results later.

Again, thanks. Peter