If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
calculated field - difficult
I have to make a calculated field in the query where the value from one table
is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
#2
|
|||
|
|||
calculated field - difficult
It will not work the way you have laid out the process. There isn't a rule
that will work with your numbers. You are operating with negative numbers. Most businesses establish a stock level to maintain. Your inventory would look like this FOR ITEM AAA -- 1 FEB = 22 3 FEB = 2 10 FEB = -3 15 FEB = -13 When does the stock get replenished? You have to show on-hand, order, issue, received. -- Build a little, test a little. "clalc" wrote: I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
#3
|
|||
|
|||
calculated field - difficult
I'm not sure if I explained properly but the whole idea is to track future
expenses for items to be ordered in the future. For this purpose I will not have to manipulate table on hand. I just want to see how many items I need to order and how much money I will need to have on each date when demand arises. For not complicating the matter I omitted the money part, but this is the idea behind the table Needs. Does it explain better the logic behind it ? Thank you for advise. "KARL DEWEY" wrote: It will not work the way you have laid out the process. There isn't a rule that will work with your numbers. You are operating with negative numbers. Most businesses establish a stock level to maintain. Your inventory would look like this FOR ITEM AAA -- 1 FEB = 22 3 FEB = 2 10 FEB = -3 15 FEB = -13 When does the stock get replenished? You have to show on-hand, order, issue, received. -- Build a little, test a little. "clalc" wrote: I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
#4
|
|||
|
|||
calculated field - difficult
This query will give you the results below --
SELECT Order_Fill.Item, Order_Fill.FillDate, (SELECT IIF((Stock.[On-Hand] - Sum([XX].[QTY])) 0,0, (Stock.[On-Hand] - Sum([XX].[QTY]))*-1) FROM Order_Fill AS [XX] WHERE [XX].FillDate = Order_Fill.FillDate AND [XX].Item = Order_Fill.Item) AS ToOrder FROM Order_Fill LEFT JOIN Stock ON Order_Fill.Item = Stock.Item ORDER BY Order_Fill.Item, Order_Fill.FillDate; Item FillDate ToOrder aaa 2/3/2010 0 aaa 2/10/2010 3 aaa 2/15/2010 13 ccc 2/5/2010 0 ccc 2/8/2010 7 ccc 2/12/2010 14 Can you show me matmatically how you would get the results you show? -- Build a little, test a little. "clalc" wrote: I'm not sure if I explained properly but the whole idea is to track future expenses for items to be ordered in the future. For this purpose I will not have to manipulate table on hand. I just want to see how many items I need to order and how much money I will need to have on each date when demand arises. For not complicating the matter I omitted the money part, but this is the idea behind the table Needs. Does it explain better the logic behind it ? Thank you for advise. "KARL DEWEY" wrote: It will not work the way you have laid out the process. There isn't a rule that will work with your numbers. You are operating with negative numbers. Most businesses establish a stock level to maintain. Your inventory would look like this FOR ITEM AAA -- 1 FEB = 22 3 FEB = 2 10 FEB = -3 15 FEB = -13 When does the stock get replenished? You have to show on-hand, order, issue, received. -- Build a little, test a little. "clalc" wrote: I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
#5
|
|||
|
|||
calculated field - difficult
Thank you very much for help. I'll let you know if it worked.
"KARL DEWEY" wrote: This query will give you the results below -- SELECT Order_Fill.Item, Order_Fill.FillDate, (SELECT IIF((Stock.[On-Hand] - Sum([XX].[QTY])) 0,0, (Stock.[On-Hand] - Sum([XX].[QTY]))*-1) FROM Order_Fill AS [XX] WHERE [XX].FillDate = Order_Fill.FillDate AND [XX].Item = Order_Fill.Item) AS ToOrder FROM Order_Fill LEFT JOIN Stock ON Order_Fill.Item = Stock.Item ORDER BY Order_Fill.Item, Order_Fill.FillDate; Item FillDate ToOrder aaa 2/3/2010 0 aaa 2/10/2010 3 aaa 2/15/2010 13 ccc 2/5/2010 0 ccc 2/8/2010 7 ccc 2/12/2010 14 Can you show me matmatically how you would get the results you show? -- Build a little, test a little. "clalc" wrote: I'm not sure if I explained properly but the whole idea is to track future expenses for items to be ordered in the future. For this purpose I will not have to manipulate table on hand. I just want to see how many items I need to order and how much money I will need to have on each date when demand arises. For not complicating the matter I omitted the money part, but this is the idea behind the table Needs. Does it explain better the logic behind it ? Thank you for advise. "KARL DEWEY" wrote: It will not work the way you have laid out the process. There isn't a rule that will work with your numbers. You are operating with negative numbers. Most businesses establish a stock level to maintain. Your inventory would look like this FOR ITEM AAA -- 1 FEB = 22 3 FEB = 2 10 FEB = -3 15 FEB = -13 When does the stock get replenished? You have to show on-hand, order, issue, received. -- Build a little, test a little. "clalc" wrote: I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
#6
|
|||
|
|||
calculated field - difficult
I have recreated the tables to suit query and tried to run query and I'm
asked for XX.QTY. Is that something not in order in the query ? When it comes to numbers how I arrived at them, here it is. ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2j left 0 ) aaa 10 15-feb-10 (10-0 no more on hand) ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3, left 0) ccc 7 12-feb-10 (7-0 no more on hand) Is that helping ? "KARL DEWEY" wrote: This query will give you the results below -- SELECT Order_Fill.Item, Order_Fill.FillDate, (SELECT IIF((Stock.[On-Hand] - Sum([XX].[QTY])) 0,0, (Stock.[On-Hand] - Sum([XX].[QTY]))*-1) FROM Order_Fill AS [XX] WHERE [XX].FillDate = Order_Fill.FillDate AND [XX].Item = Order_Fill.Item) AS ToOrder FROM Order_Fill LEFT JOIN Stock ON Order_Fill.Item = Stock.Item ORDER BY Order_Fill.Item, Order_Fill.FillDate; Item FillDate ToOrder aaa 2/3/2010 0 aaa 2/10/2010 3 aaa 2/15/2010 13 ccc 2/5/2010 0 ccc 2/8/2010 7 ccc 2/12/2010 14 Can you show me matmatically how you would get the results you show? -- Build a little, test a little. "clalc" wrote: I'm not sure if I explained properly but the whole idea is to track future expenses for items to be ordered in the future. For this purpose I will not have to manipulate table on hand. I just want to see how many items I need to order and how much money I will need to have on each date when demand arises. For not complicating the matter I omitted the money part, but this is the idea behind the table Needs. Does it explain better the logic behind it ? Thank you for advise. "KARL DEWEY" wrote: It will not work the way you have laid out the process. There isn't a rule that will work with your numbers. You are operating with negative numbers. Most businesses establish a stock level to maintain. Your inventory would look like this FOR ITEM AAA -- 1 FEB = 22 3 FEB = 2 10 FEB = -3 15 FEB = -13 When does the stock get replenished? You have to show on-hand, order, issue, received. -- Build a little, test a little. "clalc" wrote: I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
#7
|
|||
|
|||
calculated field - difficult
I'm asked for XX.QTY.
Post the SQL you have in your query. One way to do this is first to run a Rank in a Group query so each transaction is numbered sequencial. Then in a subquery you could call out the last trasaction results to subtract from running sum. This is an example of Rank in a Group query -- SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Products] Q1 WHERE Q1.[Group] = Q.[Group] AND Q1.[Points] Q.[Points])+1 AS Rank FROM Products AS Q ORDER BY Q.Group, Q.Points; -- Build a little, test a little. "clalc" wrote: I have recreated the tables to suit query and tried to run query and I'm asked for XX.QTY. Is that something not in order in the query ? When it comes to numbers how I arrived at them, here it is. ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2j left 0 ) aaa 10 15-feb-10 (10-0 no more on hand) ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3, left 0) ccc 7 12-feb-10 (7-0 no more on hand) Is that helping ? "KARL DEWEY" wrote: This query will give you the results below -- SELECT Order_Fill.Item, Order_Fill.FillDate, (SELECT IIF((Stock.[On-Hand] - Sum([XX].[QTY])) 0,0, (Stock.[On-Hand] - Sum([XX].[QTY]))*-1) FROM Order_Fill AS [XX] WHERE [XX].FillDate = Order_Fill.FillDate AND [XX].Item = Order_Fill.Item) AS ToOrder FROM Order_Fill LEFT JOIN Stock ON Order_Fill.Item = Stock.Item ORDER BY Order_Fill.Item, Order_Fill.FillDate; Item FillDate ToOrder aaa 2/3/2010 0 aaa 2/10/2010 3 aaa 2/15/2010 13 ccc 2/5/2010 0 ccc 2/8/2010 7 ccc 2/12/2010 14 Can you show me matmatically how you would get the results you show? -- Build a little, test a little. "clalc" wrote: I'm not sure if I explained properly but the whole idea is to track future expenses for items to be ordered in the future. For this purpose I will not have to manipulate table on hand. I just want to see how many items I need to order and how much money I will need to have on each date when demand arises. For not complicating the matter I omitted the money part, but this is the idea behind the table Needs. Does it explain better the logic behind it ? Thank you for advise. "KARL DEWEY" wrote: It will not work the way you have laid out the process. There isn't a rule that will work with your numbers. You are operating with negative numbers. Most businesses establish a stock level to maintain. Your inventory would look like this FOR ITEM AAA -- 1 FEB = 22 3 FEB = 2 10 FEB = -3 15 FEB = -13 When does the stock get replenished? You have to show on-hand, order, issue, received. -- Build a little, test a little. "clalc" wrote: I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
#8
|
|||
|
|||
calculated field - difficult
I just copied your query, but maybe I missed some part... Here it is:
SELECT Order_Fill.Item, Order_Fill.FillDate, (SELECT IIF((Stock.[On_Hand] - Sum([XX].[QTY])) 0,0, (Stock.[On_Hand] - Sum([XX].[QTY]))*-1) FROM Order_Fill AS [XX] WHERE [XX].FillDate = Order_Fill.FillDate AND [XX].Item = Order_Fill.Item) AS ToOrder FROM Order_Fill LEFT JOIN Stock ON Order_Fill.Item = Stock.Item ORDER BY Order_Fill.Item, Order_Fill.FillDate; "KARL DEWEY" wrote: I'm asked for XX.QTY. Post the SQL you have in your query. One way to do this is first to run a Rank in a Group query so each transaction is numbered sequencial. Then in a subquery you could call out the last trasaction results to subtract from running sum. This is an example of Rank in a Group query -- SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Products] Q1 WHERE Q1.[Group] = Q.[Group] AND Q1.[Points] Q.[Points])+1 AS Rank FROM Products AS Q ORDER BY Q.Group, Q.Points; -- Build a little, test a little. "clalc" wrote: I have recreated the tables to suit query and tried to run query and I'm asked for XX.QTY. Is that something not in order in the query ? When it comes to numbers how I arrived at them, here it is. ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2j left 0 ) aaa 10 15-feb-10 (10-0 no more on hand) ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3, left 0) ccc 7 12-feb-10 (7-0 no more on hand) Is that helping ? "KARL DEWEY" wrote: This query will give you the results below -- SELECT Order_Fill.Item, Order_Fill.FillDate, (SELECT IIF((Stock.[On-Hand] - Sum([XX].[QTY])) 0,0, (Stock.[On-Hand] - Sum([XX].[QTY]))*-1) FROM Order_Fill AS [XX] WHERE [XX].FillDate = Order_Fill.FillDate AND [XX].Item = Order_Fill.Item) AS ToOrder FROM Order_Fill LEFT JOIN Stock ON Order_Fill.Item = Stock.Item ORDER BY Order_Fill.Item, Order_Fill.FillDate; Item FillDate ToOrder aaa 2/3/2010 0 aaa 2/10/2010 3 aaa 2/15/2010 13 ccc 2/5/2010 0 ccc 2/8/2010 7 ccc 2/12/2010 14 Can you show me matmatically how you would get the results you show? -- Build a little, test a little. "clalc" wrote: I'm not sure if I explained properly but the whole idea is to track future expenses for items to be ordered in the future. For this purpose I will not have to manipulate table on hand. I just want to see how many items I need to order and how much money I will need to have on each date when demand arises. For not complicating the matter I omitted the money part, but this is the idea behind the table Needs. Does it explain better the logic behind it ? Thank you for advise. "KARL DEWEY" wrote: It will not work the way you have laid out the process. There isn't a rule that will work with your numbers. You are operating with negative numbers. Most businesses establish a stock level to maintain. Your inventory would look like this FOR ITEM AAA -- 1 FEB = 22 3 FEB = 2 10 FEB = -3 15 FEB = -13 When does the stock get replenished? You have to show on-hand, order, issue, received. -- Build a little, test a little. "clalc" wrote: I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
#9
|
|||
|
|||
calculated field - difficult
I have created ranking in my table with query :
SELECT Q.Item, Q.quantityNeeded, Q.FillDate, (SELECT COUNT(*) FROM Order_Fill O_F WHERE O_F.Item = Q.Item AND O_F.FillDate Q.FillDate)+1 AS RANK FROM Order_Fill AS Q ORDER BY Q.Item, Q.FillDate; and the result is: Item quantityNeeded FillDate RANK aaa 20 03/02/2010 1 aaa 5 10/02/2010 2 aaa 10 15/02/2010 3 ccc 3 05/02/2010 1 ccc 10 08/02/2010 2 ccc 7 12/02/2010 3 but I'm not sure how would I call last transaction ... "KARL DEWEY" wrote: I'm asked for XX.QTY. Post the SQL you have in your query. One way to do this is first to run a Rank in a Group query so each transaction is numbered sequencial. Then in a subquery you could call out the last trasaction results to subtract from running sum. This is an example of Rank in a Group query -- SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Products] Q1 WHERE Q1.[Group] = Q.[Group] AND Q1.[Points] Q.[Points])+1 AS Rank FROM Products AS Q ORDER BY Q.Group, Q.Points; -- Build a little, test a little. "clalc" wrote: I have recreated the tables to suit query and tried to run query and I'm asked for XX.QTY. Is that something not in order in the query ? When it comes to numbers how I arrived at them, here it is. ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2j left 0 ) aaa 10 15-feb-10 (10-0 no more on hand) ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3, left 0) ccc 7 12-feb-10 (7-0 no more on hand) Is that helping ? "KARL DEWEY" wrote: This query will give you the results below -- SELECT Order_Fill.Item, Order_Fill.FillDate, (SELECT IIF((Stock.[On-Hand] - Sum([XX].[QTY])) 0,0, (Stock.[On-Hand] - Sum([XX].[QTY]))*-1) FROM Order_Fill AS [XX] WHERE [XX].FillDate = Order_Fill.FillDate AND [XX].Item = Order_Fill.Item) AS ToOrder FROM Order_Fill LEFT JOIN Stock ON Order_Fill.Item = Stock.Item ORDER BY Order_Fill.Item, Order_Fill.FillDate; Item FillDate ToOrder aaa 2/3/2010 0 aaa 2/10/2010 3 aaa 2/15/2010 13 ccc 2/5/2010 0 ccc 2/8/2010 7 ccc 2/12/2010 14 Can you show me matmatically how you would get the results you show? -- Build a little, test a little. "clalc" wrote: I'm not sure if I explained properly but the whole idea is to track future expenses for items to be ordered in the future. For this purpose I will not have to manipulate table on hand. I just want to see how many items I need to order and how much money I will need to have on each date when demand arises. For not complicating the matter I omitted the money part, but this is the idea behind the table Needs. Does it explain better the logic behind it ? Thank you for advise. "KARL DEWEY" wrote: It will not work the way you have laid out the process. There isn't a rule that will work with your numbers. You are operating with negative numbers. Most businesses establish a stock level to maintain. Your inventory would look like this FOR ITEM AAA -- 1 FEB = 22 3 FEB = 2 10 FEB = -3 15 FEB = -13 When does the stock get replenished? You have to show on-hand, order, issue, received. -- Build a little, test a little. "clalc" wrote: I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
#10
|
|||
|
|||
calculated field - difficult
Sorry I used another name for QTY that's why I was getting this message. I
fixed it now, but I'm still not getting the right numbers. "clalc" wrote: I just copied your query, but maybe I missed some part... Here it is: SELECT Order_Fill.Item, Order_Fill.FillDate, (SELECT IIF((Stock.[On_Hand] - Sum([XX].[QTY])) 0,0, (Stock.[On_Hand] - Sum([XX].[QTY]))*-1) FROM Order_Fill AS [XX] WHERE [XX].FillDate = Order_Fill.FillDate AND [XX].Item = Order_Fill.Item) AS ToOrder FROM Order_Fill LEFT JOIN Stock ON Order_Fill.Item = Stock.Item ORDER BY Order_Fill.Item, Order_Fill.FillDate; "KARL DEWEY" wrote: I'm asked for XX.QTY. Post the SQL you have in your query. One way to do this is first to run a Rank in a Group query so each transaction is numbered sequencial. Then in a subquery you could call out the last trasaction results to subtract from running sum. This is an example of Rank in a Group query -- SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Products] Q1 WHERE Q1.[Group] = Q.[Group] AND Q1.[Points] Q.[Points])+1 AS Rank FROM Products AS Q ORDER BY Q.Group, Q.Points; -- Build a little, test a little. "clalc" wrote: I have recreated the tables to suit query and tried to run query and I'm asked for XX.QTY. Is that something not in order in the query ? When it comes to numbers how I arrived at them, here it is. ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2j left 0 ) aaa 10 15-feb-10 (10-0 no more on hand) ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3, left 0) ccc 7 12-feb-10 (7-0 no more on hand) Is that helping ? "KARL DEWEY" wrote: This query will give you the results below -- SELECT Order_Fill.Item, Order_Fill.FillDate, (SELECT IIF((Stock.[On-Hand] - Sum([XX].[QTY])) 0,0, (Stock.[On-Hand] - Sum([XX].[QTY]))*-1) FROM Order_Fill AS [XX] WHERE [XX].FillDate = Order_Fill.FillDate AND [XX].Item = Order_Fill.Item) AS ToOrder FROM Order_Fill LEFT JOIN Stock ON Order_Fill.Item = Stock.Item ORDER BY Order_Fill.Item, Order_Fill.FillDate; Item FillDate ToOrder aaa 2/3/2010 0 aaa 2/10/2010 3 aaa 2/15/2010 13 ccc 2/5/2010 0 ccc 2/8/2010 7 ccc 2/12/2010 14 Can you show me matmatically how you would get the results you show? -- Build a little, test a little. "clalc" wrote: I'm not sure if I explained properly but the whole idea is to track future expenses for items to be ordered in the future. For this purpose I will not have to manipulate table on hand. I just want to see how many items I need to order and how much money I will need to have on each date when demand arises. For not complicating the matter I omitted the money part, but this is the idea behind the table Needs. Does it explain better the logic behind it ? Thank you for advise. "KARL DEWEY" wrote: It will not work the way you have laid out the process. There isn't a rule that will work with your numbers. You are operating with negative numbers. Most businesses establish a stock level to maintain. Your inventory would look like this FOR ITEM AAA -- 1 FEB = 22 3 FEB = 2 10 FEB = -3 15 FEB = -13 When does the stock get replenished? You have to show on-hand, order, issue, received. -- Build a little, test a little. "clalc" wrote: I have to make a calculated field in the query where the value from one table is deducted from the value in another table. Is that possible ? Here is the example: 1st table (created from linking 2 tables): Needs ItemName quantityNeeded date aaa 20 03-feb-10 aaa 5 10-feb-10 aaa 10 15-feb-10 ccc 3 5-feb-10 ccc 10 8-feb-10 ccc 7 12-feb-10 2nd table has data on hand: OnHand aaa 22 bbb 5 ccc 6 Now I have to do running total per date of items to be ordered. The result would look something like this: ItemName ToOrder Date aaa 0 03-feb-10 (it's 22-20, left 2) aaa 3 10-feb-10 (it's 5-2 ) aaa 10 15-feb-10 ccc 0 5-feb-10 (6-3, left 3) ccc 7 8-feb-10 (10-3) ccc 7 12-feb-10 Is that possible to do in a query ? I'll appreciate any advise. Thank you. |
|
Thread Tools | |
Display Modes | |
|
|