Inventory on-hand brick wall (1 Viewer)

Hello,

I know that there have been a ton of threads about calculating stock on-hand, but I haven't been able to get any of them to work for me. I've tried Allen Browne's method, but couldn't get the code right or something. Right now, I'm trying to set up queries but I keep running into the 'aggregate function' errors. Anyway, here's my table structure.

tblItems 'This is the central table, most other tables relate to this
ItemsID (key)
SignID (FK to tblSign) 'not relevant here
SizeID (FK to tblSize) 'ditto

tblInventory 'This is my once-a-year physical count of the warehouse
InventoryID (Key)
InventoryDate
ItemsID (FK to tblItems)
NumSigns (Item count)

tblSignAcquiredDetail 'Receiving details
AcquiredDetID (Key)
AcquiredID (FK to tblSignAcquired)
NumSignsIn (invoice count)
ItemsID (FK to tblItems)

tblSignAcquired 'Receiving base table
AcquiredID (Key)
AcquiredDate

Here's the query I've been trying to make work:

SELECT Items.ItemsID, (NumSigns +
(SUM(SignAcquiredDetail.NumSignsIn)))
FROM (Items INNER JOIN Inventory ON Items.ItemsID = Inventory.ItemsID) INNER JOIN (SignAcquired INNER JOIN SignAcquiredDetail ON SignAcquired.AcquiredID = SignAcquiredDetail.AcquiredID) ON Items.ItemsID = SignAcquiredDetail.ItemsID;

This is just the first part of the query. I still have to subtract orders from the inventory total, and work in a date clause to exclude orders and receipts from before the current inventory, but I figured I'd tackle one thing at a time. I really appreciate any help.

Thanks,
Nathan

Try reading this for a method.

or download this

http://office.microsoft.com/en-gb/templates/inventory-management-database-TC001018458.aspx

this is very good, except I would change it to store sales items as negative stock movements, to make it easier to work with.