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.

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.

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.

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.

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.

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.

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.

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.

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.

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.