Share
Report
69I am trying to develop a query to tell me the last time an inventory item was acted upon in the system, then also tell me its total cost and total quantity on hand. I want to use the intran and the itemsite tables, and use the field intvid as the key field.
So far, this query works for me and returns good results:
select [Inventory ID], [Total Cost], [Total Quantity]
from
(
select itemsite.invtid as "Inventory ID",
sum (itemsite.TotCost) as "Total Cost",
sum (itemsite.QtyOnHand) as "Total Quantity"
from ItemSite
group by itemsite.invtid
)
itemsite
AND this query works for me
select [Inventory ID], [Last Touched]
from
(
select intran.invtid as "Inventory ID", MAX (intran.Perpost) as "Last Touched"
from Intran where
intran.Rlsed = 1
and intran.S4Future05 = 0
group by intran.invtid
)
intran
But, I need to join them into one query to product a list of 4 fields and I can not get that to work. I am wondering the proper format. Any help would be appreciated
*This post is locked for comments
All responses (
Answers (
