select records in table by amount

[RyNC]

Hi,

How do I select records in a table that each have a "cost" (which can vary
by record), in order to make up a certain "total cost" that I establish.

For example, I establish a "total cost" in my query of $1000. I want to
select records in a table from highest cost to lowest cost and stop selecting
records once I have selected enough records to make up my "total cost". The
selecting of records must go over the "total cost" but will stop with the
last record that makes it go over the "total cost". So in this example, I
will select the last record in which the total of all records' "cost" is
$1002.

Please help!

Ryan

 

[John W. Vinson]

Hi,

How do I select records in a table that each have a "cost" (which can vary
by record), in order to make up a certain "total cost" that I establish.

For example, I establish a "total cost" in my query of $1000. I want to
select records in a table from highest cost to lowest cost and stop selecting
records once I have selected enough records to make up my "total cost". The
selecting of records must go over the "total cost" but will stop with the
last record that makes it go over the "total cost". So in this example, I
will select the last record in which the total of all records' "cost" is
$1002.

Please help!

Ryan

This is an example of the NP-complete "Knapsack Problem", for which there is
no polynomial time solution. In other words, if the number of items gets
large, any solution to the problem will become more and more inefficient and
expensive!

A brute-force approach using the "greedy algorithm" you suggest is probably
your best bet, but it's hard to implement in a Query; you may have better luck
opening a Recordset in a VBA routine and iterating through it until the limit
is reached.

 

[Michel Walsh]

If all the costs are different (else, you may have more than one solution)


SELECT a.itemID, LAST(a.cost) AS lastCost
FROM tableName AS a INNER JOIN tableName AS b
ON a.cost <= b.cost
GROUP BY a.itemID
HAVING SUM(b.cost) > limitingCostParam


returns the items which will not be selected, except for the first one (the
one having the minimum value of running sum). You may have to change > to >=
in the HAVING clause to fit your criteria in case that the last added item =
the limiting amount. Save the query as qu1.


So, the final query:

SELECT *
FROM tableName
WHERE cost >=
(SELECT MAX(lastCost)
FROM qu1)




Vanderghast, Access MVP

 

[RyNC]

Hi,

I'm a bit green when it comes to technical jargon. I know how to create
queries and the like, but am not great with VB or SQL.

Here is my data:
Part Num Cost
12345 20,408.18
15915 20,042.85
35735 18,739.05
78945 16,299.94
98765 16,190.07
45645 14,027.85
96321 13,802.01

I've established a "total cost" of $100,000.00. So I want my query to pull
the above records in descending cost until I get to or go over $100,000.00.
But the query will stop after reaching the last record which gets me to
$100,000.00.

I would assume then that my query, if working properly, would pull the first
6 parts because those would total $105,707.94. And it would leave out the
last part 96321 because the query already has reached $100,000.00.

Do I copy your info below and paste in SQL view? Please help!

Thanks,
Ryan

 

[Michel Walsh]

The first query, in SQL view, would be:

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM tableName AS a INNER JOIN tableName AS b
ON a.cost <= b.cost
GROUP BY a.[Part Num]
HAVING SUM(b.cost) > 100000


where you replace tableName with the real table name (it occurs at two
places).

It returns:

Part Num lastCost
45645 $14,027.85
96321 $13,802.01


The second query

SELECT *
FROM tableName
WHERE cost >=
(SELECT MAX(lastCost)
FROM qu1)



returns what you seek.



Vanderghast, Access MVP

 

[RyNC]

Hi,

I have pasted the below in SQL view, however it gives me an error of "Syntax
Error in FROM clause" and highlights the "AS" in the FROM statement. I've
replaced tablename with my table name of tbl_Threshold Data_Rotable.

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM tbl_Threshold Data_Rotable AS a INNER JOIN tbl_Threshold Data_Rotable
AS b
ON a.cost <= b.cost
GROUP BY a.[Part Num]
HAVING SUM(b.cost) > 100000

Thanks again for your help!

Ryan

The first query, in SQL view, would be:

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM tableName AS a INNER JOIN tableName AS b
ON a.cost <= b.cost
GROUP BY a.[Part Num]
HAVING SUM(b.cost) > 100000


where you replace tableName with the real table name (it occurs at two
places).

It returns:

Part Num lastCost
45645 $14,027.85
96321 $13,802.01


The second query

SELECT *
FROM tableName
WHERE cost >=
(SELECT MAX(lastCost)
FROM qu1)



returns what you seek.



Vanderghast, Access MVP

Hi,

I'm a bit green when it comes to technical jargon. I know how to create
queries and the like, but am not great with VB or SQL.

Here is my data:
Part Num Cost
12345 20,408.18
15915 20,042.85
35735 18,739.05
78945 16,299.94
98765 16,190.07
45645 14,027.85
96321 13,802.01

I've established a "total cost" of $100,000.00. So I want my query to
pull
the above records in descending cost until I get to or go over
$100,000.00.
But the query will stop after reaching the last record which gets me to
$100,000.00.

I would assume then that my query, if working properly, would pull the
first
6 parts because those would total $105,707.94. And it would leave out the
last part 96321 because the query already has reached $100,000.00.

Do I copy your info below and paste in SQL view? Please help!

Thanks,
Ryan

 

[Bob Barrows]

Your table name seems to have a space in it (bad idea). You need to
surround the table name with brackets:
[tbl_Threshold Data_Rotable]

Hi,

I have pasted the below in SQL view, however it gives me an error of
"Syntax Error in FROM clause" and highlights the "AS" in the FROM
statement. I've replaced tablename with my table name of
tbl_Threshold Data_Rotable.

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM tbl_Threshold Data_Rotable AS a INNER JOIN tbl_Threshold
Data_Rotable AS b
ON a.cost <= b.cost
GROUP BY a.[Part Num]
HAVING SUM(b.cost) > 100000

Thanks again for your help!

Ryan

The first query, in SQL view, would be:

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM tableName AS a INNER JOIN tableName AS b
ON a.cost <= b.cost
GROUP BY a.[Part Num]
HAVING SUM(b.cost) > 100000


where you replace tableName with the real table name (it occurs at
two places).

It returns:

Part Num lastCost
45645 $14,027.85
96321 $13,802.01


The second query

SELECT *
FROM tableName
WHERE cost >=
(SELECT MAX(lastCost)
FROM qu1)



returns what you seek.



Vanderghast, Access MVP

Hi,

I'm a bit green when it comes to technical jargon. I know how to
create queries and the like, but am not great with VB or SQL.

Here is my data:
Part Num Cost
12345 20,408.18
15915 20,042.85
35735 18,739.05
78945 16,299.94
98765 16,190.07
45645 14,027.85
96321 13,802.01

I've established a "total cost" of $100,000.00. So I want my query
to pull
the above records in descending cost until I get to or go over
$100,000.00.
But the query will stop after reaching the last record which gets
me to $100,000.00.

I would assume then that my query, if working properly, would pull
the first
6 parts because those would total $105,707.94. And it would leave
out the last part 96321 because the query already has reached
$100,000.00.

Do I copy your info below and paste in SQL view? Please help!

Thanks,
Ryan

:

If all the costs are different (else, you may have more than one
solution)


SELECT a.itemID, LAST(a.cost) AS lastCost
FROM tableName AS a INNER JOIN tableName AS b
ON a.cost <= b.cost
GROUP BY a.itemID
HAVING SUM(b.cost) > limitingCostParam


returns the items which will not be selected, except for the first
one (the
one having the minimum value of running sum). You may have to
change > to >=
in the HAVING clause to fit your criteria in case that the last
added item =
the limiting amount. Save the query as qu1.


So, the final query:

SELECT *
FROM tableName
WHERE cost >=
(SELECT MAX(lastCost)
FROM qu1)




Vanderghast, Access MVP



Hi,

How do I select records in a table that each have a "cost" (which
can vary
by record), in order to make up a certain "total cost" that I
establish.

For example, I establish a "total cost" in my query of $1000. I
want to
select records in a table from highest cost to lowest cost and
stop selecting
records once I have selected enough records to make up my "total
cost". The
selecting of records must go over the "total cost" but will stop
with the
last record that makes it go over the "total cost". So in this
example, I
will select the last record in which the total of all records'
"cost" is
$1002.

Please help!

Ryan

 

[RyNC]

The brackets worked. Thanks!

Your table name seems to have a space in it (bad idea). You need to
surround the table name with brackets:
[tbl_Threshold Data_Rotable]

Hi,

I have pasted the below in SQL view, however it gives me an error of
"Syntax Error in FROM clause" and highlights the "AS" in the FROM
statement. I've replaced tablename with my table name of
tbl_Threshold Data_Rotable.

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM tbl_Threshold Data_Rotable AS a INNER JOIN tbl_Threshold
Data_Rotable AS b
ON a.cost <= b.cost
GROUP BY a.[Part Num]
HAVING SUM(b.cost) > 100000

Thanks again for your help!

Ryan

The first query, in SQL view, would be:

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM tableName AS a INNER JOIN tableName AS b
ON a.cost <= b.cost
GROUP BY a.[Part Num]
HAVING SUM(b.cost) > 100000


where you replace tableName with the real table name (it occurs at
two places).

It returns:

Part Num lastCost
45645 $14,027.85
96321 $13,802.01


The second query

SELECT *
FROM tableName
WHERE cost >=
(SELECT MAX(lastCost)
FROM qu1)



returns what you seek.



Vanderghast, Access MVP



Hi,

I'm a bit green when it comes to technical jargon. I know how to
create queries and the like, but am not great with VB or SQL.

Here is my data:
Part Num Cost
12345 20,408.18
15915 20,042.85
35735 18,739.05
78945 16,299.94
98765 16,190.07
45645 14,027.85
96321 13,802.01

I've established a "total cost" of $100,000.00. So I want my query
to pull
the above records in descending cost until I get to or go over
$100,000.00.
But the query will stop after reaching the last record which gets
me to $100,000.00.

I would assume then that my query, if working properly, would pull
the first
6 parts because those would total $105,707.94. And it would leave
out the last part 96321 because the query already has reached
$100,000.00.

Do I copy your info below and paste in SQL view? Please help!

Thanks,
Ryan

:

If all the costs are different (else, you may have more than one
solution)


SELECT a.itemID, LAST(a.cost) AS lastCost
FROM tableName AS a INNER JOIN tableName AS b
ON a.cost <= b.cost
GROUP BY a.itemID
HAVING SUM(b.cost) > limitingCostParam


returns the items which will not be selected, except for the first
one (the
one having the minimum value of running sum). You may have to
change > to >=
in the HAVING clause to fit your criteria in case that the last
added item =
the limiting amount. Save the query as qu1.


So, the final query:

SELECT *
FROM tableName
WHERE cost >=
(SELECT MAX(lastCost)
FROM qu1)




Vanderghast, Access MVP



Hi,

How do I select records in a table that each have a "cost" (which
can vary
by record), in order to make up a certain "total cost" that I
establish.

For example, I establish a "total cost" in my query of $1000. I
want to
select records in a table from highest cost to lowest cost and
stop selecting
records once I have selected enough records to make up my "total
cost". The
selecting of records must go over the "total cost" but will stop
with the
last record that makes it go over the "total cost". So in this
example, I
will select the last record in which the total of all records'
"cost" is
$1002.

Please help!

Ryan

 

[RyNC]

Thanks for your help!

Now I want to take this one step further and instead of having to manually
update the threshold of $100,000.00 manually. I'd like to input a query's
calculated threshold named "Threshold3". How do I go about doing that? My
thoughts were to just insert the query name.field where the "100000" is. See
below. Is this correct? It doesn't seem to work.

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM [tbl_Threshold Data_Rotable] AS a INNER JOIN [tbl_Threshold
Data_Rotable] AS b ON a.Cost <= b.Cost
GROUP BY a.[Part Num]
HAVING SUM(b.Cost) > [qry_Threshold_Rotable_Is Not Null > 80%].[Threshold3];

 

[Michel Walsh]

You can use a parameter.


SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM [tbl_Threshold Data_Rotable] AS a INNER JOIN [tbl_Threshold
Data_Rotable] AS b ON a.Cost <= b.Cost
GROUP BY a.[Part Num]
HAVING SUM(b.Cost) > [Enter the limiting sum: ]


and if you run the query from within the user interface, you will be
prompted for that parameter.



Vanderghast, Access MVP

 

[RyNC]

Thanks for your help!

Now I want to take this one step further and instead of having to manually
update the threshold of $100,000.00 manually. I'd like to input a query's
calculated threshold named "Threshold3". How do I go about doing that? My
thoughts were to just insert the query name.field where the "100000" is. See
below. Is this correct? It doesn't seem to work.

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM [tbl_Threshold Data_Rotable] AS a INNER JOIN [tbl_Threshold
Data_Rotable] AS b ON a.Cost <= b.Cost
GROUP BY a.[Part Num]
HAVING SUM(b.Cost) > [qry_Threshold_Rotable_Is Not Null > 80%].[Threshold3];

The first query, in SQL view, would be:

SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM tableName AS a INNER JOIN tableName AS b
ON a.cost <= b.cost
GROUP BY a.[Part Num]
HAVING SUM(b.cost) > 100000


where you replace tableName with the real table name (it occurs at two
places).

It returns:

Part Num lastCost
45645 $14,027.85
96321 $13,802.01


The second query

SELECT *
FROM tableName
WHERE cost >=
(SELECT MAX(lastCost)
FROM qu1)



returns what you seek.



Vanderghast, Access MVP

Hi,

I'm a bit green when it comes to technical jargon. I know how to create
queries and the like, but am not great with VB or SQL.

Here is my data:
Part Num Cost
12345 20,408.18
15915 20,042.85
35735 18,739.05
78945 16,299.94
98765 16,190.07
45645 14,027.85
96321 13,802.01

I've established a "total cost" of $100,000.00. So I want my query to
pull
the above records in descending cost until I get to or go over
$100,000.00.
But the query will stop after reaching the last record which gets me to
$100,000.00.

I would assume then that my query, if working properly, would pull the
first
6 parts because those would total $105,707.94. And it would leave out the
last part 96321 because the query already has reached $100,000.00.

Do I copy your info below and paste in SQL view? Please help!

Thanks,
Ryan

 

[RyNC]

That should work. Thanks again for your help!!!