Basic Inventory (1 Viewer)

Morgana

New member
Local time
Yesterday, 22:38
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
Yesterday, 22:38
Joined
Oct 29, 2018
Messages
16,572
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
Yesterday, 22:38
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
Yesterday, 22:38
Joined
Oct 29, 2018
Messages
16,572
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
Yesterday, 22:38
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, 00:38
Joined
Feb 28, 2001
Messages
20,859
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
Yesterday, 22:38
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, 00:38
Joined
Feb 28, 2001
Messages
20,859
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
Yesterday, 22:38
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

Member
Local time
Today, 06:38
Joined
Dec 31, 2020
Messages
47
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.
 

Users who are viewing this thread

Top Bottom