Im new to SQL and I am trying to create a report that will show each item and it's current balance at every location. How do I create sql code that will show me the balance for that item at that location. Now these items can be set up at more than one location with different balance. I want to see what the balance is for that item at that location. Example.
Example:
Site ABC- item 1234 balance of 3 Site BBC- item 1234 balance of 2..
Need to so show that site with that item and the balance at that site location.
I am using Microsoft SQL.. and I have 3 tables joined.. I included the curbal in that report but the curbal is showing the same for that item in the different site locations. I need to show what that curbal is for that particular site it is located in. Just not sure how to right said function to do that.
SELECT INVENTORY.SITEID, INVENTORY.lOCATION, INVENTORY.ITEMNUM, ITEM.DESCRIPTION,INVTRANS.CURBAL,INVENTORY.LM_PMMS_AUP ,INVENTORY.ORDERUNIT
FROM ((INVENTORY
INNER JOIN ITEM ON INVENTORY.ITEMNUM= ITEM.ITEMNUM)
INNER JOIN INVTRANS ON ITEM.ITEMNUM=INVTRANS.ITEMNUM);