Inventory in, used and SOH Tables/Queries

[rreddy]

Hi,

So i'm somewhat of a newbie to databases but I think i'm a few steps further than the average office applications user.

I'm using Access 2013 and trying to create a database to manage meat inventory better.

• I have a table for incoming meat deliveries. Which includes ED Number(unique and primary key) incoming qty and other variables.
• I have a table for meat consumption transactions which includes ED Number and qty used for certain order number
• I have a query that generates the sum used per ED from the meat consumption table

I made a query with an expression field that subtracts sum used per ED from incoming qty and also shows some descriptive fields from the incoming meat deliveries table. This is to be a "Stock on Hand" query

I was happy as Larry because it all appeared to work. But a little later I realised it doesn't include items that have not been "consumed" at all. I.e. They have been logged on the Incoming Meat Deliveries Table (so the stock exists), No records for that ED in the consumption table (not been used yet) and therefore also not on the Sum used per ED query.

I appreciate any advice that anyone can give me.

Thanks & Kind Regards,

Raj

 

[rreddy]

No ideas anyone?

 

[JHB]

Do you have some sample data, (in Excel or MS-Access table), and also which result you want to get/how you want it showed!

 

[rreddy]

Thanks, I think I have it sorted now