Solved

Access 2007 inventory management

Posted on 2014-11-08
9
694 Views
Last Modified: 2014-11-11
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
0
Comment
Question by:dresdena1
9 Comments
 
LVL 1

Expert Comment

by:john15nlt
ID: 40430200
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
 
LVL 23

Expert Comment

by:Eirman
ID: 40430203
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
 

Author Comment

by:dresdena1
ID: 40430291
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 23

Expert Comment

by:Eirman
ID: 40430294
I am attaching a screenshot.
Try again!
0
 

Author Comment

by:dresdena1
ID: 40430299
Ooops. I attached it but didn't hit upload.
ee-db.jpg
0
 
LVL 84
ID: 40432945
Do you have other tables? In many cases, you'll have Inventory tables, Transaction tables, etc.
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40432978
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
 
LVL 23

Assisted Solution

by:Eirman
Eirman earned 250 total points
ID: 40434634
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
 

Author Closing Comment

by:dresdena1
ID: 40435241
Thank you. It appears that it may be more complicated than I had expected.
Thanks again!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 35
Install both Office 2010 and 2013 4 37
SQL multicriteria from ONE textbox 32 43
Opening forms with an input box 5 22
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question