Access 2010 ... keeping track of inventory across different tables
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
Table two and table three are withdrawals from inventory (different venues)
That's fine.
The queries i indicated will give you the totals.
The queries i indicated will give you the totals.
ASKER
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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