How do I store Totals in Access using VBA code

[ThomasDriscoll]

I have created an Inventory Table with total fields for qty of all purchases for each stock item and one for the customer orders. I have created tables for the purchase order detail lines and customer order detail lines and have built test data to these tables and all looks good. Have tried queries but no joy there. All I want to do is hold the total qty of all purchases and sales by part number and then calculate stock level. Any ideas on how I can do this are welcome. Thomas

[jforbes]

The best advice I can offer you is to purchase an Accounting package. There is a lot under the hood of an Accounting application that is easily over looked and that isn't fully realized until you get in and start messing around with it. Stock level is one aspect that can be a bit daunting, but there are at least a dozen different ways to cost an Item. You may only see the need for one, but sooner or later you or someone else will find a need to cost an item differently and if this isn't planned for in the beginning, you will end up rewriting a lot of your code and queries. And costing is just one of the many gotchas. Serial tracking and Lot tracking can be a barrel of fun.

But if you want to see what you can accomplish, I would recommend:

• Create an InventoryItem Table to store Inventory Item Definitions. Things like Item Number, Costing type, Tracking Type, Unit of Measure, Active.
• Create a WarehouseLocati on Table with address information (or a lookup to an address record) Stocking type, like Stock or non-stock.
• Create a ItemWarehouse table to link Items to Warehouses and Quantities on Hand.
• Create an InventoryTransa ction Table to store transactions like moving some of an Item from one warehouse to another. This is where things can get really messy because if you are doing any form of accounting of costs, activity in this table will directly relate to your Accounts Receivable and Accounts Payable, and they will need to match up.

If you are costing:

• Create a Customer/Vendors Table to link to an address and set credit limits.
• Create an Customer Orders Table to hold information about a customer order.
• Create a Customer Orders Lines table to hold Items that a customer is ordering
• Create a Purchase Order Table to buy Inventory
• Create Purchase Orders Lines Table to include Items to be purchased

Even if you aren't costing, you may need to address how you are shipping and receiving your inventory. Which would mean more tables.

If you are still at this... Then create a routine that Queries the InventoryTransa ction table and updates the ItemWarehouse table with the current OnHandQuantity for an Item. Doing this could be considered going against http://bytes.com/topic/access/insigh...ble-structures, but this would be one place where your database might not be considered fully normalized (depending who you are talking to) since the query to look through a decade of Inventory Transactions can take quite a while. Then as you add code to Ship or Receive Inventory (or internal moves of inventory from one warehouse to another), have it create an InventoryTransa ction then call the routine that updates the OnHandQuantity so that when you query an ItemWarehouse, the quantity available will be accurate.

There is a lot to this, but that is the basics.

[BikeToWork]

JForbes, are you an accountant by any chance. I was very impressed with your post. Could you do all this in Peachtree or other off the shelf accounting software?

[jforbes]

Glad you liked it. I'm not an accountant, but I spent quite a few years writing ERP Software, which caused me to spend a lot of time in code dealing with accounting and talking to accountants on how best to accomplish what was needed.

There are a lot of Accounting packages out there and it looks like Peachtree has Inventory Management which would allow you to Maintain Inventory. The one thing that I didn't care for is that it's not using a SQL Server Database, which I would consider a Requisite if you ever want to develop against the database or create your own reports outside of their system.

I could recommend a couple packages, but I don't think this is the best forum for that. So I'll just reiterate that having SQL as a Backend should be required.

[ThomasDriscoll]

Thank you jforbes for the reply to my question.
I have created all the tables you have suggested and more and populated the tables with data. The only outstanding point is I would like to store for each stock item totals of purchases, sales from which I can calculate the stock level.

I have been in IT for over 45 years prior to retiring in 2007.

Recently I came out of retirement to help the son of a friend get his failing business back on track.

At this point in time funds are limited otherwise I would have suggested buying good software.

I have only dabbled with Access DB in the past and with a 7 year gap from working I need to get back up to speed fast and do most of stock control myself a payroll and accounts package to handle the rest.

I hope that explain the position I am in.

Regards

Thomas

[jforbes]

I started replying with some recommendations on what to do next and two things happened:

• I got a little bummed out when I realized how much I was going to need to type in.
• Then it changed to excitement when I thought about making a sample to show what I was thinking instead of regurgitating a bunch of recommendations .

So I threw this together today. I had a good time with it: http://bytes.com/attachment.php?atta...1&d=1441409648

It's just a starting point and Costing and Tracking are not addressed in the sample, but it does include a transaction table and a little query to sum the transactions to find the OnHandQuantity.

Hope it helps and let me know if I messed anything up or you need an explanation of something.

[TylerBennett]

This is brilliant ^