From: clalc on
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
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
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
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
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.