Multi-Location Inventory Managment System

[lzook88]

Hey everyone need some help on a project I am currently working on.




I have a data base program that I have created. Its for an EMS company..


I have the following set up on it as of now.

TABLES
Categories - For the products
tblCertifications - Monitors the EMTs/Paramedics Certifications
tblProducts - Lists out the products we use (ID / ItemName / Category / Type (Each/roll/etc) )
tblSecurityLevel - defines the security of the user logged in for the login script
tblUsers - lists out the users allowed to access the system
Types - Defines the type of location where items are stocked (Vehicle (AMBULANCE) / Station / Bag (EMT/Medics Personal Bag) )
Units - Defines the locations of where items are stocked (All the vehicles, emts/medics with bags and the two stations)

QUERIES
Certification Query - Queries the number of days until an certification expires

FORMS
AddItems - Allows a user to add a product we use to the product table
AddUnit - Allows a user to add a unit, emt/medic with a bag, or station to the Units table
AddUser - Adds a user to the database
Admin - The admin screen
frmUserInfo - allows a user to change their password
Login - the form used to login

RERORTS
Expiration Report - Runs a report for certifications expiring in the next 60 days


With that all laid out...

I need to set it up where I can transfer products from the STATIONS to the units and/or bags.

at the same time of doing the transfer minus the stock from the station and add it to the unit.

I would also, then need to set it up to see when levels are low on a vehicle or bag to restock them



I have tried multiple ways to get this to work. however I have been unsucessful at this. Nothing I have tried works.



Thanks
L. Zook
EMT


My knowledge is mostly in EXCEL VBA but access seems to be a better format for this project..


ANY HELP YOU ALL CAN PROVIDE WILL BE GREATLY HELPFUL.

Cross Posted At:
http://www.access-programmers.co.uk/...d.php?t=280696
http://www.utteraccess.com/forum/ind...wtopic=2031382

[orange]

Do you have a a set of requirements?
Do you have a clear description of what you are trying to do in plain English?
You have 1 post and seems you have a working database -- guessing on my part.
What exactly is the status of this project at the moment?
Do you have a data model?

I submitted a sample database in an older post to assist a poster who was moving equipment to and from inventory and various locations. It isn't exactly what you are asking, but you may get some ideas from it.

You should investigate additions to inventory as positive transactions, and removal of items from inventory as negative transactions; and use queries to calculate current stock on hand via

CurrentStockOnHand = LatestStockOnHand + Items Acquired - Items Removed

see this by Allen Browne


Also, since Access and Excel are based on different object models, there is not much "carry over of commonality" from one to the other. Knowing Excel may not be beneficial. You may have to unlearn some spreadsheet concepts as you learn Access/database.

Good luck with your project.

[lzook88]

I have looked at the Allen Browne System however it didnt seem to provide me a way to manage multiple locations.. currently the employee part works.. the inventory side is still BLAH!

Requirements:
Be able to see stock on hand at Stations
Be able to see current stock level on Units / Medic Bags
Be able to see stock needed at Stations and on Units / Medic Bags
Be able to transfer stock from Station to Unit/Medic Bags
Be able to receive stock from Annex (our supplier) to the Stations

I also looked at the the older post you replied on and downloaded the sample but I have many products. and those many products can be between many locations.

EX:

I could have 50 Gauze Rolls at Station A
While having 10 Gauze Rolls on UNIT A
at the same time having 5 Gauze Rolls in Medic A Bag

Does that make more sense?

I tried to attach the database but it exceeds the size allowed on here..

[orange]

Try making a zip of your database.

[lzook88]

Orange,

See attached Zip File...


Just an FYI: Avoid the Login Form.. it is a lockout window and you must provide credentials to continue.. if you dont it will close the database.

Thanks again for all of your help and time..


Zook

[orange]

Just reading through your post I see the following "things" to be considered in your database.

I'm not clear on Types and Units --you have separated these--perhaps you could clarify.. I think these will result is several other "things".

Product
ProductCategory (must be for your admin/accounting/management??)
Location (but this may mask other entities Vehicle, ambulance, Station, BAG etc??)
Certification (seems 2 things involved a Position requires a certain Certification and a Person has been certified to a Certification. Also some certifications have a time limit -- must be re-certified annually/every 3 years etc.)

How do you do your inventory currently?
What issues are you having?
Who exactly are Users?
How will database be kept current?
How will you do a physical stock taking to get an actual count of Items by Location?

Do you have a description of the business along the lines of -- "a typical day in our organization". Such an article would help put the pieces into context and would provide you with some "what if scenarios" to test any proposed database design. It is critical to get your database structured to support your business requirements before jumping too deeply into Access.

Here is a tutorial that can help you with database design - tables and relationships. You have to work through it to experience the process he uses. The approach he suggests can be used when designing any database.

Good luck with your project.

[lzook88]

Products Category - Used to make it easier to find supplies in the product list
Location - Used to tell someone where the product is located
Certification - yes you are correct

Currently there is not system to do our inventory. We are losing lots of stock and going through more stuff than what is documented

The users would be the EMT/Medics in the field and the Supervisors

Database would be kept current by weekly inventories on the units, and monthly inventories on the station and bags

the business is an EMS (Ambulance company)..

I will through the Tutorial and see what I can learn.



EDIT: That tutorial looks more focused towards how I have the users (Employees) and their certifications set up. That part of the database works fine.. I don't need anything fancy their.

[orange]

You should work through one or more of the tutorials on the page in the link.
The tutorials all start with a clear description of the business and the requirement.
Each tutorial follows the same general approach to identify "things"(entities and attributes) and the relationship between them. You really have to do the work to get the benefit.

Perhaps you could show us a description of your business with the same sort of detail given in the tutorial overviews.
Please provide readers a description of the business along the lines of -- "a typical day in our organization".

[orange]

FYI
cross posted http://www.access-programmers.co.uk/...d.php?t=280696