result question using queries

[Guest]

I was wondering if there is a way to stop a query as soon as it finds the
first answer.

for example I have a table with numberous items and locations and qty's

such as
item 1 location 11 qty 22
item 2 location 33 qty 44
item 3 location 44 qty 34

I want the query to find the combination of items that will result qty=100
(qty+qty+qty)
however the items must not be the same and the locations must not be the
same as well
As I have it now it returns
item 1 location 11 qty 22 item 2 location 33 qty 44 item 3 location 44 qty34
item 1 location 11 qty 22 item 3 location 44 qty 33 item 2 location 33 qty 44
item 2 location 33 qty 44 item 1 location 11 qty 22 item 3 location 44 qty 34

and so on
I want it to show only the first result not every single combination.
In this example there will not be too many combination but in my real table
there are over 1500 items and 2600 locations. I have restricted the data as
much as possible but still am getting over 60,000 results (which as you would
imagine takes awhile to run). Is it possible to stop running as soon as it
finds the first answer

Any help would be helpful

 

[Guest]

Hi, Bill.

I want the query to find the combination of items that will result qty=100
(qty+qty+qty)
however the items must not be the same and the locations must not be the
same as well

If I understand your question correctly (and I'm not sure I do where the
"items and locations can't be the same" -- I interpreted that to mean a
comparison of the current record with any two other records within the
table), then the following table structure and query would probably generate
the results you want:

Table name: tblCombinations
ID, AutoNum, primary key
ItemNum, Number
Location, Text
Qty, Number

To speed up the query, ensure that the table also has an index on the Qty
field.

SELECT C1.ItemNum AS C1_ItemNum,
C1.Location AS C1_Location, C1.Qty AS C1_Qty,
C2.ItemNum AS C2_ItemNum, C2.Location AS C2_Location,
C2.Qty AS C2_Qty, C3.ItemNum, C3.Location, C3.Qty
FROM (tblCombinations AS C1
INNER JOIN tblCombinations AS C2 ON C1.ID < C2.ID)
INNER JOIN tblCombinations AS C3 ON C2.ID < C3.ID
WHERE ((C1.Qty + C2.Qty + C3.Qty) = 100);

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

I want to be more clear as to the data in the table and if this will affect
my results because I don't think I set my table up correctly.

Example in my current table I have the following and for arguement sakes
lets =150

Item location qty
B200 123456 50
B200 246810 25
P325 135791 25
P325 444444 10
PL4 555555 75
PL5 987654 35

With location being PK.
i want to see
B200 123456 50
P325 135791 25
PL4 555555 75

Not
B200 123456 50
B200 123456 50
B200 123456 50

Does this change you suggestion?

 

[Guest]

Hi, Bill.

Does this change you suggestion?

It only changes the name of the field used for the comparison in the JOIN
clauses and the total that the added Qty fields must add up to. These are
only very minor adjustments. Try:

SELECT C1.Item AS C1_Item, C1.Location AS C1_Location,
C1.Qty AS C1_Qty, C2.Item AS C2_Item,
C2.Location AS C2_Location, C2.Qty AS C2_Qty, C3.Item,
C3.Location, C3.Qty
FROM (tblCombinations AS C1
INNER JOIN tblCombinations AS C2 ON C1.Location < C2.Location)
INNER JOIN tblCombinations AS C3 ON C2.Location < C3.Location
WHERE ((C1.Qty + C2.Qty + C3.Qty) = 150);

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.