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 said:
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 ?
:
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.
:
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.
:
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.
:
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.
