Running Totals in Make Table/Update Query

M

meangene

I am trying to create a delimited file for use by my ERP import tool that
will show item adjustment detail in the correct format for the ERP. Example:
Have a FIFO tier table with an item with two tiers:
Item Date Qty
ABC 1/1/08 13
ABC 2/12/08 100 (Total QtyOnHand: 113)

I have another table (Item_Adj) with a single record adjusting -40
Adj Item Adj Qty
ABC -40

Need to create two records for my ERP from the above where I end up with
following in another table (Adjustment Qty):
Item QtyAdj
ABC -13
ABC -27

Can this be done with a make table or combo of make table/update?
 
C

Clifford Bass

Hi,

Sure can. Try:

select Item, Qty - [Adj Qty] as QtyAdj into [Adjustment Qty]
from FIFO inner join Item_Adj on [Adj Item] = Item

Clifford Bass
 
C

Clifford Bass

Hi,

Oops, just realized I did not read you question correctly. It can be
done in code. Let me think a bit more if there is a way using queries.

Clifford Bass

Clifford Bass said:
Hi,

Sure can. Try:

select Item, Qty - [Adj Qty] as QtyAdj into [Adjustment Qty]
from FIFO inner join Item_Adj on [Adj Item] = Item

Clifford Bass

meangene said:
I am trying to create a delimited file for use by my ERP import tool that
will show item adjustment detail in the correct format for the ERP. Example:
Have a FIFO tier table with an item with two tiers:
Item Date Qty
ABC 1/1/08 13
ABC 2/12/08 100 (Total QtyOnHand: 113)

I have another table (Item_Adj) with a single record adjusting -40
Adj Item Adj Qty
ABC -40

Need to create two records for my ERP from the above where I end up with
following in another table (Adjustment Qty):
Item QtyAdj
ABC -13
ABC -27

Can this be done with a make table or combo of make table/update?
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi,

Okay, try this:

SELECT A.Item, IIf(-[Adj Qty]>[Qty],-[Qty],(select Sum(Qty) from FIFO as B
where B.Item = A.Item and B.Date < A.Date)+[Adj Qty]) AS QtyAdj INTO
[Adjustment Qty]
FROM FIFO AS A INNER JOIN Item_Adj AS B ON A.Item = B.[Adj Item]
WHERE (((IIf(-[Adj Qty]>[Qty],-[Qty],(select Sum(Qty) from FIFO as B where
B.Item = A.Item and B.Date < A.Date)+[Adj Qty]))<0));

Clifford Bass
 
M

meangene

Clifford - Thanks for getting back. I will try this as soon as I can (been
gone a few days) and let you know how it goes!

Clifford Bass said:
Hi,

Okay, try this:

SELECT A.Item, IIf(-[Adj Qty]>[Qty],-[Qty],(select Sum(Qty) from FIFO as B
where B.Item = A.Item and B.Date < A.Date)+[Adj Qty]) AS QtyAdj INTO
[Adjustment Qty]
FROM FIFO AS A INNER JOIN Item_Adj AS B ON A.Item = B.[Adj Item]
WHERE (((IIf(-[Adj Qty]>[Qty],-[Qty],(select Sum(Qty) from FIFO as B where
B.Item = A.Item and B.Date < A.Date)+[Adj Qty]))<0));

Clifford Bass

meangene said:
I am trying to create a delimited file for use by my ERP import tool that
will show item adjustment detail in the correct format for the ERP. Example:
Have a FIFO tier table with an item with two tiers:
Item Date Qty
ABC 1/1/08 13
ABC 2/12/08 100 (Total QtyOnHand: 113)

I have another table (Item_Adj) with a single record adjusting -40
Adj Item Adj Qty
ABC -40

Need to create two records for my ERP from the above where I end up with
following in another table (Adjustment Qty):
Item QtyAdj
ABC -13
ABC -27

Can this be done with a make table or combo of make table/update?
Click to expand...
Click to expand...
 
M

meangene

Clifford - This did the trick - thanks!

Clifford Bass said:
Hi,

Okay, try this:

SELECT A.Item, IIf(-[Adj Qty]>[Qty],-[Qty],(select Sum(Qty) from FIFO as B
where B.Item = A.Item and B.Date < A.Date)+[Adj Qty]) AS QtyAdj INTO
[Adjustment Qty]
FROM FIFO AS A INNER JOIN Item_Adj AS B ON A.Item = B.[Adj Item]
WHERE (((IIf(-[Adj Qty]>[Qty],-[Qty],(select Sum(Qty) from FIFO as B where
B.Item = A.Item and B.Date < A.Date)+[Adj Qty]))<0));

Clifford Bass

meangene said:
I am trying to create a delimited file for use by my ERP import tool that
will show item adjustment detail in the correct format for the ERP. Example:
Have a FIFO tier table with an item with two tiers:
Item Date Qty
ABC 1/1/08 13
ABC 2/12/08 100 (Total QtyOnHand: 113)

I have another table (Item_Adj) with a single record adjusting -40
Adj Item Adj Qty
ABC -40

Need to create two records for my ERP from the above where I end up with
following in another table (Adjustment Qty):
Item QtyAdj
ABC -13
ABC -27

Can this be done with a make table or combo of make table/update?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Distributing Adjustment Quantities Across FIFO Tiers 2
Another replacing value from query question 5
Creating Records in New Table Using a Loop 3
Query to update qty on insert in another table 1
Need help with an Update query statement ... 4
Totals Query 2
Make table query where INTO {tablename} is actually a parameter; doesn't work 2
Date lookup in Table 2

Top