create inventory in Access (1 Viewer)

mabino79

Member
Local time
Today, 11:14
Joined
Nov 17, 2021
Messages
72
Dear All,

Thanks for all the support given.

i need to create an Access database where i can do as follow :-

Products - can be purchased than make reserved for some project & if required to sale will be deducted from the stock.

Current Stock Qty = Purchase - Reserved (too deducted from current stock) - Sale
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:44
Joined
May 7, 2009
Messages
19,169
start by identifying the "process" you have in your organization?
how do they/you do it in paper/worksheet?
how do they purchased? what form do they use?
what do form they use to withdraw from stock, return to stock.
by knowing these things you can easily translate them
to a working db.
 

mabino79

Member
Local time
Today, 11:14
Joined
Nov 17, 2021
Messages
72
start by identifying the "process" you have in your organization?
how do they/you do it in paper/worksheet?
how do they purchased? what form do they use?
what do form they use to withdraw from stock, return to stock.
by knowing these things you can easily translate them
to a working db.
its same as simple

Purchase Less Reserve less Sales nothing complicated
 

plog

Banishment Pending
Local time
Today, 00:44
Joined
May 11, 2011
Messages
11,613
It's simple because you are not giving it much thought. Arnel asked you to really think about the data you need to capture and you responded with 3 pieces of data.

So, based on that I recommend you don't use a database, or even a computer. The situation you described simply requires a sheet of paper with a number at the top that represents the current inventory. Then when sales are made, subtract from the number above. When you get to the bottom of the sheet--get a new sheet and start the process again.

If that won't work, explain why. You are now past the big picture part of this discussion and you need focus on details.
Arnel gave you some good questions to prompt you to think about the details. I suggest you revisit those questions and provide specifics.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:44
Joined
May 7, 2009
Messages
19,169
have you looked at the sample Northwind db?
 

mabino79

Member
Local time
Today, 11:14
Joined
Nov 17, 2021
Messages
72
have you looked at the sample Northwind

yes, i have seen that but that need more modification

Input in Purchase : Date, Product Code, Qty
Input in Reserve : Date, Product Code, Qty
Input in Sale : Date, Product Code, Qty
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 19, 2002
Messages
42,981
Northwind is not a good sample.

Lets start with what type of inventory do you have? Are you selling nails or computers or a mixture of very cheap and very expensive items. Inventory for cheap stuff is kept differently from expensive stuff. For cheap stuff, all you care about are how many you have on hand. For expensive stuff, you keep child tables so you can provide a separate serial number for each unit.

No one can give you an inventory database without knowing anything at all about your inventory or the details of your process.
 

Cronk

Registered User.
Local time
Today, 16:44
Joined
Jul 4, 2013
Messages
2,770
You don't care about what Product is reserved for, or who does the ordering? For physical stocktaking counts, how will you distinguish between what is Reserved and still in the warehouse and what has been Reserved and been taken from the warehouse?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 19, 2002
Messages
42,981
Even "simple" inventory is not actually "simple" because of the many options and accounting methods to choose from.
 

mabino79

Member
Local time
Today, 11:14
Joined
Nov 17, 2021
Messages
72
You don't care about what Product is reserved for, or who does the ordering? For physical stocktaking counts, how will you distinguish between what is Reserved and still in the warehouse and what has been Reserved and been taken from the warehouse?
no matter who is ordering, just want to make for the stock update, how much stock is available in the stock presently.

Reserved will be deducted from the current stock only, later on it will be converted into Sale.
 

mabino79

Member
Local time
Today, 11:14
Joined
Nov 17, 2021
Messages
72
Northwind is not a good sample.

Lets start with what type of inventory do you have? Are you selling nails or computers or a mixture of very cheap and very expensive items. Inventory for cheap stuff is kept differently from expensive stuff. For cheap stuff, all you care about are how many you have on hand. For expensive stuff, you keep child tables so you can provide a separate serial number for each unit.

No one can give you an inventory database without knowing anything at all about your inventory or the details of your process.

no matter who is ordering, just want to make for the stock update, how much stock is available in the stock presently.

Reserved will be deducted from the current stock only, later on it will be converted into Sale.
 

oleronesoftwares

Passionate Learner
Local time
Yesterday, 22:44
Joined
Sep 22, 2014
Messages
1,159
Check this link there is an inventory template from microsoft

But as said by some members on the forum, you have to document the database workflow .
 

rainbows

Registered User.
Local time
Yesterday, 22:44
Joined
Apr 21, 2017
Messages
425
Reserved will be deducted from the current stock only, later on it will be converted into Sale.

this current stock does or does not include parts on order ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:44
Joined
May 7, 2009
Messages
19,169
here is a "fully functional" demo of Inventory system.
i think it covers what you need and you only need
to add report for your inventory balance.
 

Attachments

  • Inventory System MS Access.zip
    1.4 MB · Views: 223

rainbows

Registered User.
Local time
Yesterday, 22:44
Joined
Apr 21, 2017
Messages
425
[U]arnelgp[/U]

Can you tell me if That database takes care of

1. currency, depending where you buy from and exchange rates for all stock
2. if you update the costs will it only apply for orders /sales as from that date you changed things and will not alter costs on previous transactions

steve
 

GPGeorge

Grover Park George
Local time
Yesterday, 22:44
Joined
Nov 25, 2004
Messages
1,776
[U]arnelgp[/U]

Can you tell me if That database takes care of

1. currency, depending where you buy from and exchange rates for all stock
2. if you update the costs will it only apply for orders /sales as from that date you changed things and will not alter costs on previous transactions

steve
PMFJI:


At some point, you have to become involved directly. By that I mean, open Arnel's demo and LOOK at it. Try it out with some sample data you have accumulated now on paper or in a spreadsheet. See what happens, whether it handles all of the tasks you have envisioned, what's not quite appropriate for your business. Just dig in and try.

It's all part of the process. "Try it out and see" is really the only viable approach from here.


BTW: Part of the problem may be the fact that this is all brand new to you and it's so overwhelming to think about it. It's something we all confront from time to time. The solution to that part of the problem is "Try it out and see." It'll get easier.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 19, 2002
Messages
42,981
@rainbows at this point, you can't even hire someone to build this application for you since there are no specifications. It was very nice of arnel to give you a start but no one knows but you whether arnel's app will work for you. So as George suggested - LOOK at it.

Your follow on questions have nothing to do with the original spec so how would you expect arnel to have known about them ahead of time?

You need to do some things yourself. You can't expect any of us to pull a customized (to your unknown requirements) application out of thin air. We're happy to help but we are not here to design or build an app for you, especially with unknown specifications. We are all volunteers. We don't get paid to help you but you get paid for the work we end up doing for you so please try to not waste our time. Write your specifications down - for yourself. Then you can work through the posted sample and see if you understand how to modify it.
 

mabino79

Member
Local time
Today, 11:14
Joined
Nov 17, 2021
Messages
72
1639391557321.png


have created the Purchase but not able to figure out to make Sale and reserve
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 19, 2002
Messages
42,981
Vendor does NOT belong in tblProducts unless that product can ONLY be purchased from a specific vendor. Plus, you should use the proper name for the FK. Use VendorID NOT Vendor. Consistency is your friend. Same for all the other FK's that don't match the PK they relate to and you have one in EVERY table. When a field IS the same data, it should have the same name. When fields have the same type of data but never identical data, you can make them the same or vary them but again, be consistent. For example if you had address fields in both the client and vendor tables, they would contains the same type of data so using the same names is rational but sometimes people give them different names to avoid confusion so VendAddr, VendCity, ... and CustAddr, CustCity, ...
 

Users who are viewing this thread

Top Bottom