Basic Inventory (1 Viewer)

Morgana

New member
Local time
Today, 06:49
Joined
May 4, 2021
Messages
28
I know there are already a lot of threads on Inventory, but I'm getting overwhelmed. I am trying to do just a simple inventory (yes, no such thing), but all I need is an On-Hand Stock with In/Out/Removed. I don't need re-order levels or suppliers as what I receive is decided above my pay-grade. The last Access database I made was 20+ years ago, so a very different Access. I am trying to learn VBA, but don't have the time to dedicate to it that I would like.

I've been watching YouTube Design and Deploy where he goes over creating a database, including a short inventory sample, but his solution changes the actual field holding the quantity, it doesn't capture In/Out/Removed

I currently am using an Excel spreadsheet, and this is all I really need to capture for the actual inventory part. Once I get this figured out, I'm confident I can add in the Who requested bits and any other reports/forms I need. Does anyone have recommendations for a simple on-hand database I can study?

1624380914544.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:49
Joined
Oct 29, 2018
Messages
21,357
Hi. Typically, the on hand quantity is a calculated value. Have you tried using a query or a function to calculate it from your transactions table.

If you can show us your table structure, we might be able to tell you how to calculate the on hand qty column.
 

Morgana

New member
Local time
Today, 06:49
Joined
May 4, 2021
Messages
28
I know I do want it to be a calculated field. I think that is where I am getting overwhelmed or over-thinking it. I'm still at the writing it out on paper stage. How do I go from three different entries Stock In (add), Stock Out (subtract), Stock Counted (override) and make sure that if I have a Stock Count that it overrides everything else? Should these be all in one table, or querying separate tables?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:49
Joined
Oct 29, 2018
Messages
21,357
Normally, I would imagine an inventory database would have a table structure similar to this.

Products
ProductID
Description
etc...

Transactions
TransactionID
ProductID
TransactionType
TransactionDate
Qty
etc...

The transaction table might contain records like:

Prod1, StockIn, 10, 6/1/2021
Prod1, StockOut, -5, 6/7/2021
etc...

With the above table, we can easily calculate the on hand quantity for Prod1 using Sum(Qty).
 
Last edited:

Morgana

New member
Local time
Today, 06:49
Joined
May 4, 2021
Messages
28
Thanks. That makes sense.
Could you put a TransactionType of StockTaken or something similar for correcting the count? Our stock is not currently secure and I've had counts both lower and higher than expected. lol. How would override the Sum(qty)?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:49
Joined
Feb 28, 2001
Messages
26,999
What usually happens for stock overrides or stock counts is that you have two flavors of them. You get the current total by summing the transactions you have. Then you enter the override amount and have the form create an AddStock or RemoveStock transaction with the amount required to correct the total. That way, you just sum up all transactions no matter what.

There is, however, another approach. Just add a flag to the transactions. Then when you do an override for a given stock item, you enter the override amount and at the same time, set this flag on all transactions with dates earlier than the override. Then when you write your sum, just make it ignore transactions that are flagged with having been overridden. (Doing it this way makes it easy for you to archive your DB, since all of the transactions that are no longer relevant are marked and ready for copy followed by deletion.

There are LOTS of ways to skin this particular cat. {{MEOW!}}
 

Morgana

New member
Local time
Today, 06:49
Joined
May 4, 2021
Messages
28
this page helped me much while I was trying to understand how inventory databases work:
I had seen this posted before and was trying to follow this, but I was letting myself get overwhelmed by the jargon that I wasn't understanding. Maybe I need more basic information on VBA before I can complete this. :) Thanks for the recommendation.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:49
Joined
Feb 28, 2001
Messages
26,999
With the great Google brain at your disposal, jargon shouldn't be a problem. But heck, ask definition questions in the GENERAL section or the DATABASE DESIGN section. We're easy.
 

Morgana

New member
Local time
Today, 06:49
Joined
May 4, 2021
Messages
28
With the great Google brain at your disposal, jargon shouldn't be a problem. But heck, ask definition questions in the GENERAL section or the DATABASE DESIGN section. We're easy.
Great Google is who has been helping me so far. Unfortunately, I can only consult with it once I get free time. lol
 

Cotswold

Active member
Local time
Today, 13:49
Joined
Dec 31, 2020
Messages
521
There is an Inventory example in the Access Templates that are included with Access.
Not sure if it suitable but it may give you a jump start. Could be helpful and you can update it
to do what you want as you go.

Design and detail may not be the best but it will work (or should do) without falling over.
 

jepoysaipan

Registered User.
Local time
Today, 23:49
Joined
Nov 4, 2007
Messages
133
Hi Morgana,

I am in the middle of developing a sales & stocks monitoring system for my brother, a simple concept at first and then became complicated as it progress, btw kudos to this thread for members who are always ready to shell out their knowledge.

anyways, based on my experience the way to tackle this is to create a DB and normalize/drill it as far as I can, but this approach is kinda obsolete and always culprit to the 2GB limitation of native ms-access restriction, so this is how I approach mine:

Table Design/Structure - I created the barebone tables

Employee Table - handles the users & level for permission & security
EmpID - PK
EmpName - String
EmpUserLevel - 1 for Admin; 2 for Managers; 3 for Supervisors; 4 for Staff

ProductIn Table - handles product received
ProdID - PK
ProdDateRec
ProdDesc
ProdQty
ProdPrice

ProductOut Table - handles product sold
ProdSoldID - PK
ProdSoldDateSold
ProdSoldDesc
ProdSoldQty
ProdSoldPrice

I created an Inner Join query with both ProductIn & ProductOut Table with the following:

Field: ProductSoldQty * -1
- reason for being such is that when you sum the Qty field it will deduct the (-) ProductSoldQty and give you the available qty!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:49
Joined
Feb 28, 2001
Messages
26,999
but this approach is kinda obsolete and always culprit to the 2GB limitation of native ms-access restriction

If I may comment on this, and it is ONLY a comment in passing.... If you are often running into the 2 GB limitation, it is time to look at something like MySQL or SQLLite or any other "true" SQL engine, none of which have the 2 GB limits. I think the cheapest true SQL options still allow 10 GB of data, and if you go into true SQL Server you have effectively as much as you can fit on the disk that holds the tables. Which could easily be above 10 GB with no sweat. It isn't without problems, but it IS workable.
 

jepoysaipan

Registered User.
Local time
Today, 23:49
Joined
Nov 4, 2007
Messages
133
Hi Doc,

Agreed!, but for this kind of small app I will not bother to setup a split DB with an open source SQL (if budget is an issue) but truly the 2GB cap is history when using a split DB and SQL. :)

We all have experienced clients who are always concern with their budgets rather than the result/efficiency of what we are building, so I am always showing them a prototype with a lot of dummy data in stand alone native access for them to see that if they don't invest in hardware (servers, SQL, split DB) they will experience the same performance laggy or slow performing down the line as they fill-up the DB.

Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:49
Joined
Feb 28, 2001
Messages
26,999
@jepoysaipan

If you have unrelated tables, it is possible to have more than one BE file, giving you some extra breathing room. Each such file has another 2 GB of room for table space. The only problem is that you cannot enforce relational integrity across split back ends.

However, the way you answered your question makes me wonder... is the DB that is running into space issues already split into BE/FE files? Having a monolithic DB file consumes table space with infrastructure tables that are FE-related.
 

jepoysaipan

Registered User.
Local time
Today, 23:49
Joined
Nov 4, 2007
Messages
133
Hi Doc,

Yup one of my local client that distributes nutritional assistance, it was a split DB and after 8 yrs it is now struggling :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:49
Joined
Feb 28, 2001
Messages
26,999
Hi Doc,

Yup one of my local client that distributes nutritional assistance, it was a split DB and after 8 yrs it is now struggling :)

I have one word for you.... "Archiving."

There is no reason that you couldn't set an arbitrary but meaningful time limit and say... anything older than X will now go to the archived back-end rather than the current back-end. You can still do the search but might have to do a UNION query between near-term and far-term data. Then have two back-end files.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 02:49
Joined
Apr 1, 2019
Messages
712
Morgana, Allen Browne of allenbrowne.com has a good treatise on stock control & a module to calculate stock at any time the right way. Also, you might find a copy of "Dharamasala Tea' somewhere, It's an example of a simple stock control database & named after the first Product Item.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:49
Joined
Sep 12, 2006
Messages
15,613
Hi Morgana,

I am in the middle of developing a sales & stocks monitoring system for my brother, a simple concept at first and then became complicated as it progress, btw kudos to this thread for members who are always ready to shell out their knowledge.

anyways, based on my experience the way to tackle this is to create a DB and normalize/drill it as far as I can, but this approach is kinda obsolete and always culprit to the 2GB limitation of native ms-access restriction, so this is how I approach mine:

Table Design/Structure - I created the barebone tables

Employee Table - handles the users & level for permission & security
EmpID - PK
EmpName - String
EmpUserLevel - 1 for Admin; 2 for Managers; 3 for Supervisors; 4 for Staff

ProductIn Table - handles product received
ProdID - PK
ProdDateRec
ProdDesc
ProdQty
ProdPrice

ProductOut Table - handles product sold
ProdSoldID - PK
ProdSoldDateSold
ProdSoldDesc
ProdSoldQty
ProdSoldPrice

I created an Inner Join query with both ProductIn & ProductOut Table with the following:

Field: ProductSoldQty * -1
- reason for being such is that when you sum the Qty field it will deduct the (-) ProductSoldQty and give you the available qty!

I have to say, if you run into the 2Gb problem there's something amiss. Typically you need at least hundreds of thousands of records to get to 2Gb
 

Users who are viewing this thread

Top Bottom