R
RyNC
Hi,
I need help with the below query. Essentially I'm trying to query a list of
records in which the sum of the cost of the list of records pulled is only
one record over in cost than the amount required.
For example, the query is run and I enter 50 as my sum of cost requirement.
There are 3 records in the source table:
Record 1 with cost of 25
Record 2 with cost of 10
Record 3 with cost of 30
The query will return records 3 and 1 in descending order because they meet
my requirement of 50 and stop after going over that requirement by 1 record.
What I'm having a hard time figuring out is how to eliminate manual entry of
my requirement. I want to input a formula that makes the requirement 80% of
the total cost of the source table.
Please help!!!
As-Is query 1:
SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM [tbl_Consolidated Data_Spares] AS a INNER JOIN [tbl_Consolidated
Data_Spares] AS b ON a.Cost<=b.Cost
GROUP BY a.[Part Num]
HAVING SUM(b.Cost)>=[Requirement];
As-Is query 2:
SELECT * INTO [tbl_Planner Review Master_Spares]
FROM [tbl_Consolidated Data_Spares]
WHERE ((([tbl_Consolidated Data_Spares].Cost)>=(SELECT MAX(lastCost)
FROM [query 1])))
ORDER BY [tbl_Consolidated Data_Spares].Cost DESC;
To-Be query 1:
SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM [tbl_Consolidated Data_Spares] AS a INNER JOIN [tbl_Consolidated
Data_Spares] AS b ON a.Cost<=b.Cost
GROUP BY a.[Part Num]
HAVING SUM(b.Cost)>="80% OF SOURCE TABLE SUMMED COST";
I need help with the below query. Essentially I'm trying to query a list of
records in which the sum of the cost of the list of records pulled is only
one record over in cost than the amount required.
For example, the query is run and I enter 50 as my sum of cost requirement.
There are 3 records in the source table:
Record 1 with cost of 25
Record 2 with cost of 10
Record 3 with cost of 30
The query will return records 3 and 1 in descending order because they meet
my requirement of 50 and stop after going over that requirement by 1 record.
What I'm having a hard time figuring out is how to eliminate manual entry of
my requirement. I want to input a formula that makes the requirement 80% of
the total cost of the source table.
Please help!!!
As-Is query 1:
SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM [tbl_Consolidated Data_Spares] AS a INNER JOIN [tbl_Consolidated
Data_Spares] AS b ON a.Cost<=b.Cost
GROUP BY a.[Part Num]
HAVING SUM(b.Cost)>=[Requirement];
As-Is query 2:
SELECT * INTO [tbl_Planner Review Master_Spares]
FROM [tbl_Consolidated Data_Spares]
WHERE ((([tbl_Consolidated Data_Spares].Cost)>=(SELECT MAX(lastCost)
FROM [query 1])))
ORDER BY [tbl_Consolidated Data_Spares].Cost DESC;
To-Be query 1:
SELECT a.[Part Num], LAST(a.cost) AS lastCost
FROM [tbl_Consolidated Data_Spares] AS a INNER JOIN [tbl_Consolidated
Data_Spares] AS b ON a.Cost<=b.Cost
GROUP BY a.[Part Num]
HAVING SUM(b.Cost)>="80% OF SOURCE TABLE SUMMED COST";