Search For All Combinations - Possible Factorial Solution?

[Guest]

I need to match different combinations from on table to a single line item on
another table.

The relevant fields are account number, date, identifier, and quantity.

Table 1 might look like this.

Account Date Identifier Quantity
123 10/01/2007 XYZ 150
123 10/02/2007 XYZ 200
123 10/02/2007 XYZ 150
123 10/03/2007 XYZ 100

Table 2 might look like this

Account Identifier Quantity
123 XYZ 500

I need the query to sum the first three, and match to the one item line on
Table 2. As you can see, I can't just sum by Account and Identifier. The
items always show up on different dates.

 

[Roger Carlson]

There has to be some rationale for why the first three records of one table
should match the single record on the other, while the fourth does not.
Unless you can provide that rationale to the database, it can't be done.

I'd be interested in knowing, however, why you need Table 2. Looks
unnecessary to me.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

What is the significance of “3†in your example, and how do you determine it?

 

[Guest]

What is the significance of the 3 records in your example, and how do you
determine to use them and only them?

 

[Guest]

The rationale is that 150 + 150 + 200 = 500. That is what I need to find so
that the records in table 1 match the record in table 2.

In this example, the first three records are what is needed. But, it could
be the first and third... second and fourth...

Combinations of the first table will match to a value in the second table.

 

[Guest]

The significance is that 150 + 150 + 200 = 500. That is what I need to find
so that the records in table 1 match the record in table 2.

In this example, the first three records are what is needed. But, it could
be the first and third... second and fourth...

Combinations of the first table will match to a value in the second table
(Quantity).

 

[Roger Carlson]

But what if the fourth record was also 150? How would you know which two
"150" records to add to make the correct total. By this I mean, what if
different combinations of numbers have the SAME total?

You haven't said what this is all about, but I suspect you've got a major
database design flaw.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

It is a possibility, but an extremely unlikely occurrence. The values I used
are for representative purposes only - Simply to demonstrate the flow of the
problem. The values are typically not rounded so evenly.

Approach the solution thinking that the fourth record also being 150 as
being an event that will never happen. Literally, a trillion to one shot.

 

[Guest]

I see where you're going here, but do you know how many possibilities you're
talking about with even a few records? Especially if your table has more
than a few records per account.

 

[Guest]

Yes, it can be a lot, theoretically. But, the data lines involved in Table 1
and Table 2 will always be less than 2,000.

I've made MS Access crunch through more than that. I think it'll swallow
this without chewing...

 

[Guest]

Any new thoughts on this problem?