calculated field - difficult
in [Queries]
|
From: clalc on 4 Mar 2010 22:09 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.
From: KARL DEWEY on 5 Mar 2010 11:13 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.
From: clalc on 5 Mar 2010 12:20 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.
From: KARL DEWEY on 5 Mar 2010 15:59 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.
From: clalc on 5 Mar 2010 16:35 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.
|
Next
|
Last
Pages: 1 2 3 Prev: Query prompt one column for multiple random values Next: query to trim name field? |