Access 2010  ... keeping track of inventory across different tables

peispud
peispud used Ask the Experts™
on
Hi

I am using access 2010

I have three tables for an inventory list.  Each table has two important fields.  

Stock #  and quantity...............  Stock # is the key in each table and they are relationally linked.

Table 1 = input to inventory
Table 2  =  Withdrawal from inventory
Table 3 = Withdrawal  from inventory

I want a query or a table so that I have available to me at any time the current inventory.

What is the correct way to approach this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't really follow your table structure .  There can be no sensible way of relating these tables to each other.  They might well all have a relationship with a 'Stock Item' table if one existed but they do not relate to each other.

To get the total per stock number you can create a saved  union query to build a single list and then create a  Totals query using the first query as the source.
Use your own table & field names in the queries below.

qry1:

Select [StockNumber] , [quantity] as Movement from table1
Union All
Select [StockNumber] , -1*[quantity] as Movement from table2
Union All
Select [StockNumber] , -1*[quantity] as Movement from table3

then
qry2:

Select [Stocknumber] , Sum(Movement) as Inventory from qry1 group by [stocknumber]
EirmanChief Operations Manager

Commented:
Surely your three tables are .....

Actual up to date inventory
Input to inventory
Withdrawal  from inventory

Have a look at this free demo db
http://www.sourcecodester.com/microsoft-access/inventory-system-ms-access-version.html
peispudTech

Author

Commented:
Table #1  would is a starting  starting inventory.  Additional inventory would be there from time to time


Table two and table three are withdrawals from inventory (different venues)
That's fine.

The queries i indicated will give you the totals.
peispudTech

Author

Commented:
Thanks so far.  This will definitely be some work for me to learn how to do this.  I don't mind that at all but I would like to be sure where I should be putting my code.

I am trying to implement your code just now (will have to go to my day job soon).   Should I be doing this in the SQL view in a query or should I be doing this in the Visual Basic editor?


Right now I am trying this in the Union SQL editor accessed from a design view of the query.  


SELECT [Stock #],[quantity] as Movement from [Table -- Consignment Sales]
Union All

SELECT [Stock #],[quantity] as Movement from [Table -- Inventory  Inputs to Kessem Treasures]
Union All
When queries are discussed in any on-line forum then the only approach is use SQL.  It is far too difficult and time consuming to talk about the query grid.

So my queries are what you should place in the sql view of a new query - after amending them to fit your own table and field names.

In many cases you can then view the query in the query design grid once you have pasted in the sql..
However, a UnionAll query cannot be represented in the grid and can only be shown in SQL view.
MIS Liason
Most Valuable Expert 2012
Commented:
I am quite sure Pete's has a firm handle on how to get what you need with your current structure.

Just some info...
Most systems like this use *One* table for inventory transactions:
Negative values indicate Items removed
Positive values indicate Items added.

TableName: tblInventoryTransations
Fields
itID (PK)
itProductID
itQuant
itDate
...etc
See the attached database...

Just FYI...

Also see the Access sample database for more insight:
http://office.microsoft.com/en-us/templates/inventory-management-database-TC001018458.aspx?CTT=5&origin=HA001234159


JeffCoachman
Database113.mdb

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial