Access 2007 inventory management

EE,
I have a database in Access 2007 that we use to process orders from our website.

We process orders into a form that joins the products, customers, order details, and order tables.

I would like to know if it is possible to input the quantity of products into the products table and then have it automatically adjusted based on sales that are input. Basically maintain a running inventory.

Is this possible? If so would it be difficult to implement. The database was setup by someone else a few years back and my knowledge is limited.

Any help would be greatly appreciated.
dresdena1
dresdena1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PatHartmanConnect With a Mentor Commented:
Keeping a running inventory requires very careful programming and at least a rudimentary knowledge of VBA.  It is easy to slip up and then you are SOL and you have no net.  A more reliable and auditable method is to keep a transaction table of all receive, issue, and adjust transactions that you then sum to show the current balance.  Transactions are stored with their natural sign so they can be summed without logic.  Receive = +, Issue = -, and Adjust may be either.  If there is a discrepancy, you have detail you can rely on to identify missing/duplicate transactions.  With a running balance, you have no way of identifying how it went wrong or when.
0
 
john15nltCommented:
So I inderstand, you have a form to process orders that is not automatically adjusting the inventory or you have other sales that are not processed in the form and need to adjust inventory based on those?
0
 
EirmanChief Operations ManagerCommented:
A running inventory doesn't sound that difficult to implement.
If the database is not proprietary and you posted it with some sample data it would be a great help to us.

Failing that, a screenshot of the relationships in the backend database would be very useful.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
dresdena1Author Commented:
Thanks for the quick responses!

john15nlt,
It is not automatically adjusting inventory.

Eirman,
I have opened up a basic query and added the primary tables so that you can see the joins. I am attaching a screenshot.

Thank you.
dresdena1
0
 
EirmanChief Operations ManagerCommented:
I am attaching a screenshot.
Try again!
0
 
dresdena1Author Commented:
Ooops. I attached it but didn't hit upload.
ee-db.jpg
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do you have other tables? In many cases, you'll have Inventory tables, Transaction tables, etc.
0
 
EirmanConnect With a Mentor Chief Operations ManagerCommented:
Hi dresdena1, Inventories are somewhat more complicated than I first thought.

I came across this old sample inventory database (inventory control.mdb).
It may give you some ideas.


There a another demo db using the FIFO method .... how to allocate individual batches through queries
InventoryControl.mdb
Batch-Allocation.mdb
Batch-Allocation.txt
0
 
dresdena1Author Commented:
Thank you. It appears that it may be more complicated than I had expected.
Thanks again!
0
All Courses

From novice to tech pro — start learning today.