Query not displaying value

[cwwaicw311]

Hi,

I have 3 table:
Staff (Staff ID(PK), Shift)
Inventory (InventoryType(PK),ManchineName)
Transaction (Transaction ID(PK), Date, InvIN, InvOUT, Remarks, Staff ID(FK), InventoryType(FK))

Since i have 10 difference type of inventory, I wanted to create a query whereby for example :Inventory XYZ. So when i open the query it will display:
InventoryType = XYX, InvIN, InvOUT so that i can calculate the balance.

I not sure is it something wrong with my relationship or problem on normalization but the query does not return any value. Pls help, Thank...

[jgelpi16]

If I understand what you want....I would create a simple one-to-one relationship between InventoryType(PK) and InventoryType(FK) within the query. Then add Inventory.InventoryType(PK), Transaction.InvIN, and Transaction.InvOUT in the query fields. That should give you what you are looking for. Let me know if that works.

[objNoob]

Hi,

I have 3 table:
Staff (Staff ID(PK), Shift)
Inventory (InventoryType(PK),ManchineName)
Transaction (Transaction ID(PK), Date, InvIN, InvOUT, Remarks, Staff ID(FK), InventoryType(FK))

Since i have 10 difference type of inventory, I wanted to create a query whereby for example :Inventory XYZ. So when i open the query it will display:
InventoryType = XYX, InvIN, InvOUT so that i can calculate the balance.

I not sure is it something wrong with my relationship or problem on normalization but the query does not return any value. Pls help, Thank...

SELECT inv.inventorytype, trans.invin, trans.inout
FROM inventory as [inv], transaction as [trans]
WHERE inv.inventorytype = trans.inventorytype;

This would display your whole inventory. If you need to only find one type of inventory then add another condition to you WHERE clause like

WHERE inv.inventorytype = trans.inventorytype AND inv.inventorytype=WHAT_YOU_ONLY_WANT_TO_SEE

[cwwaicw311]

Hi,
thank for the code i have done the query part. I have another question, would u guys know how to do running sum in a query. The table would be the same as i stated above.

The running sum would SUM the value of InvIN MINUS InvOUT

How do i do the running for this...??

[jgelpi16]

I would just creater another Select Query based on your first query. Then add InvIn and InvOUT, then add a third field as an expression and type [InvIN]-[InvOUT], change it from a "Group By" to a "Sum". That should do the trick. Let me know if you need a better explanation.

[maximus]

Well as promised here is your mdb with minimum stock prompt.
Changes made:
1)Field Re-order level in inventory Table
2)Tag Field in Inventory_Tracking Table.

When you stock becomes = to < then re-order level on a particular transaction that transaction is taged and when you select the inventory in the main menu the transaction information is displayed. This will continue till your stock is less then or equal to re-order level. But as soon as the stock goes up and you select the inventory the taged trasaction is released.


I was following your post you wanted to know how to do a running sum. well i have used a query which does the same. now think about adding a danger stock level which should be lower then re-order level and once it reaches that level an user cannot make an more issue of that inventory.

let me know about your progress.