Multiple addition query Issue

[RKM solutions]

Hi Guys,
Hope that someone out there has a plausible solution and quick.
I have been attempting to query a table via both sql and vb in order to
process every possible addition permutation
To explain:
table data

Gross Amount Required Search :4911.51
23.73
14.87
345.87
4550.77
678.90
etc

The user needs to input a value of the statement and the system queries the
invoice tbl until it finds a sum of multiple value(s) which matches their
query.

It seemed simple when i started today but whichever way I tackle it when the
table size increases the query time gets silly!!

Any help would be much appreciated... Ive tried For/ next statemnts /Looping
code, multiple copies of the table to one query etc , What I need simply put
is a query that produces all mathematical permutations of the table
efficiently

Thanks to anyone you is able to take the challenge on
thanks in advance
R.K.M

 

[Marshall Barton]

Hope that someone out there has a plausible solution and quick.
I have been attempting to query a table via both sql and vb in order to
process every possible addition permutation
To explain:
table data

Gross Amount Required Search :4911.51
23.73
14.87
345.87
4550.77
678.90
etc

The user needs to input a value of the statement and the system queries the
invoice tbl until it finds a sum of multiple value(s) which matches their
query.

It seemed simple when i started today but whichever way I tackle it when the
table size increases the query time gets silly!!

Any help would be much appreciated... Ive tried For/ next statemnts /Looping
code, multiple copies of the table to one query etc , What I need simply put
is a query that produces all mathematical permutations of the table
efficiently

It doesn't matter how you approach it. If the table has N
records you would have to try O(N^N) sums to find a match
(if one exists. Since that grows VERY rapidly you would be
limited to a table of at most a few hundred records to have
any hope of getting a result in a day. If you want a result
in less than a minute, you can only have a few 10s of
records.

Is there any way you can filter the data to be compared down
to a just a few records?

 

[RKM solutions]

Thanks Marshall,

Thought that I was being a bit dim, but on this occassion I think that I
have exhausted all my efforts in trying to acheive the unacheivable.

As you say I shall have to explain to my client that a limitation of maybe
20 records exists for each search.

Thanks for your prompt reply anyhow, it's much appreciated.
have a good day

Rob

 

[Jim Burke in Novi]

Why exactly do you need this functionality? It seems like a bit of an odd
request to me. Are there times where you're supposed to have a bill total
that relates to individual items or something along those lines? i.e. do you
have, for example, a DB that has bills in it, with each bill having a groups
of related 'items', and you need to find a bill that equals that total,
something like that?

 

[RKM solutions]

Hi Jim,

Thanks for your response. Yes thats right ...its relates to an accounting
procedure. On accassions there is a requirement to find historical items
which have no linked field.

I am writing a system for an accountant, who has to deal with many different
data structures presented to him. The only certainty is that there will
always be two tables, one the child of the other, "Payments and Invoices"
.... but not linked by any reference!! (This is data supplied by some of his
clients)

His job is to troll through the invoice tbl adding up the iitems that relate
to a single payment entry in the master tbl (payments)

I realise that the data should never have been input in the first place
in this fashion ...that is with no linked fields, but hey thats a problem
that hes left me to solve!!

Any suggestions that you have are greatly received
..
Cheers

 

[Jim Burke in Novi]

Hate to say this, but it makes no sense to have no relationship. There has to
be some way of relating data together, otherwise you basically have random
groupings of data. Someone needs to evaluate the way that DB is set up and
establish some realtionships between the sets of data. Normally you'd have
something like, for example, a bills tables, with billID and other data
associated with the bill, then a billItems table that has all items associate
with that bill. the billItems table would have a billID field defined, so
that each bill had a row in the billItems for every item associated with that
bill. Then you would define a relationship between the bills and billItems
table via the billID field. Then if you needed to find any bills that came
out to a certain total you would just define a summary query that summed all
amounts for bill items by billID and compare the bill sums to the total
you're trying to find. But without a relationship I don't see how you could
even make sense of the data. Maybe I'm missing something or misinterpreting?