Re: help with matching data required

Duane:

"speaking theoretically"? Is that a good thing? : )

I try to employ the fastest Cartesians I can find. I'm not telling you from
where they come.

There are ways to improve performance.

If the sum is to be N, and you're finding the combinations of 6 values that
add up to the target, add up the 5 smallest values. Subtract that from N
and filter out all values larger than that. That reduces the Cartesians
quite a bit in some cases. Killing Cartesians we can call it. Well,
actually, we just deport them. Do this in subqueries in the FROM clause,
rather than filtering them out AFTER they have been created. Faster.

Simply, if your sum is to be 300, you don't need to consider any number
larger than 300 (assuming there can be no negative values). I haven't built
that into this query yet. Had enough trouble with Jet as it is. Worst I've
ever seen!

I had a similar thread I answered a few years ago that had 10,000 values in
the table. The number of combinations were staggering. We were picking
football teams. I believe it came to 10^XXXX, that is, a 4 digit number.
We computed that computers don't last long enough to solve the problem. It
took only minutes to find a team of 6 players, though. We managed to find a
few ways to reduce the combinations, down to a 3 digit exponent. Not very
helpful in that set of circumstances, but it was a large improvement.
Perhaps my grandchildren will live to see this problem solved.

Tom Ellison

"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:udhlmL$NGHA.3944@xxxxxxxxxxxxxxxxxxxxxxx
I was only speaking theoretically (I learned that from Tom ;-). I stated
"You could write code to create the select statement" which would handle
multiple lists/values. I hadn't done the math on the number of combinations
but it ran fairly quickly with 5-6 records! Them Cartesians know how to
work fast.

--
Duane Hookom
MS Access MVP
--

"Marshall Barton" <marshbarton@xxxxxxxxxx> wrote in message
news:q3gpv1t0tker27gf582ha3ejofcsklqm87@xxxxxxxxxx
Duane Hookom wrote:

Bizarre yes but possibly solvable with SQL. Assuming a smaller example
with
single field tables of

Table Field
======== ======
tblWidth Wdth
tblLength Lngth
tblHeight Hght

add values to the tables and attempt to find a "box" where the volume =
275.

SELECT Lngth, Wdth, Hght, [Lngth]*[wdth]*[Hght] AS Volume
FROM tblLength, tblWidth, tblHeight
WHERE [Lngth]*[wdth]*[Hght]= 275;

Selecting any combination of Lngth * Wdth = 275 or Lngth * Hght =275 or
just
Lngth = 275 would suggest you would need to add the value 1 to each
table.
This could be done with a union query if you didn't want to actually
enter 1
into each table.

If you had only one table of value, you would need to add the same table
into your query as many times as there are records in the table. You
could
write code to create the select statement.

It should all be possible but I am having trouble identifying a real
world
situation that would require this. (Maybe this is a homework assignment
and
we just helped a student).


Duane,
This approach would require a different query for each list
size. I.e. the query must know how many records are in the
table so you can use a matching number of joins.

In the case of Norman's example, there would be a few more
than 104,857,600,000,000,000,000,000,000 records, which, at
best, would take a while to process ;-)

--
Marsh
MVP [MS Access]




.

Relevant Pages

  • Re: help with matching data required
    ... I told you Tom was the chief theorist in the query realm! ... Duane Hookom ... MS Access MVP ... I try to employ the fastest Cartesians I can find. ...
    (microsoft.public.access.queries)
  • Re: help with matching data required
    ... tblWidth Wdth ... tblLength Lngth ... tblHeight Hght ... This could be done with a union query if you didn't want to actually enter 1 ...
    (microsoft.public.access.queries)
  • Re: help with matching data required
    ... tblWidth Wdth ... tblLength Lngth ... tblHeight Hght ... This could be done with a union query if you didn't want to actually enter ...
    (microsoft.public.access.queries)