Stock DB

Hello Everybody!
As I have posted here before I am currently designing stock database for our company to accomplish the following:


1) To maintain full control over the current location, condition (new/used, etc), quantity of every goods/materials/consumables that our company uses.
2) To generate various statistical reports (including Excel charts) for analysis by the senior management
3) To have a single point data collection database by means of Access Forms

I prefer to use out-of-box solutions and avoid any major programming or scripting work, except copy/paste type ready-to-go codes that require minimum editing.

Currently I am facing problems and cannot solve certain tasks with Access. I cannot distinguish if these problems are due to Access own limitations or lack of my knowledge. So I very much need your help.

I have started a new Database with “Incoming” Table to record all goods/materials/consumables received by the stock:
The fields and sample content in the Incoming Table is as below:



Now let’s assume that the following items were issued from the stock and recorded in the “Outgoing” Table. The table content will be something like this:


In order to avoid issuing items that are not physically available at the stock I want to link the “Outgoing” table to the “Incoming” table so the “Outgoing” Table can only be filled-in with items actually available in the stock.

I understand that some kind of "Subtraction Query" must be designed to group, summarize and deduct “Outgoing” items with the same Part No., Status, Shelf Number and Serial Number (if any) from items in the “Incoming” table.

I need help creating such Subtraction Query that would link the “Incoming” and “Outgoing” tables in itself.

Let’s see the sample above to better understand what I am trying to achieve.

For instance, "Kettle 100ml" has a unique Part Number – "K100". Once selected from the dropdown list in the Item field of the “Outgoing” table the Status field in the “Outgoing” table logically should only have one option: “New” in its dropdown list.
Ideally I would like to have a multi-column dropdown list in the “Status” field of the “Outgoing” table with the following entry:



The “Status” field of the “Outgoing” table for the Part Number "T150" with no Serial Number should group the items based on Status, Shelf Number and Quantity and list as below:


How can this be achieved?

Thank you in advance!

the outgoing and incoming tables are good. keep.
but I would think youd need a tStock table, to combine all the 1 item. (to maintain stocks)
this would be what gets subtracted when you have 'outgoing'

'incoming'
K100, 100ml, 50
K100, 100ml, 45

tStock would show
K100, 100ml, 95

then your outgoing would check the stock qty,
out: K100, 100ml, 5
check: is 5 < 95 , then ok
update query to reduce stock from 95 to 90

now, what to do if ,
out: K100, 100ml, 105
do you reduce to negative?
or
reduce to zero stock, but create backorder?
or
suspend the order until filled.

cross posted http://www.access-programmers.co.uk/...d.php?t=280977

tStock would show
K100, 100ml, 95

My problem is I can't create tStock table.

In an inventory database, generically speaking, if the In table and Out table are separate tables (and they don't need to be) then in order to establish a Current Inventory or On Hand one must write the data into a single temp table - and then perform aggregate queries against the temp table.

The single temp table should be in the front end, not the back end - so that multiple users do not conflict with each other for this type design.

Warning: Cross posted! Originally stated here: http://www.access-programmers.co.uk/...=280977&page=2