Pointers on stock on hand/qty required for inventory

[khayward]

Hi all,
I've been doing a fair bit of reading on stock-on-hand and qty required ('Shopping list') information for access 2007 but I'm having difficulty because my database is not a 'resale' database - I sell assemblies comprised of components and it's the components I need to keep track of - when I enter an order of a quantity of assemblies I ultimately want the database to be able to 'issue' components to that order, subtracting them from inventory and, where the qty on hand drops below a trigger point, add the component to a summarized 'shopping list' report.

Can you kind folks point me in the direction of some reading materials? I've had a look, even trying restaurant databases as this is the closest thing I could think of, but not finding anything super helpful.



Thanks!

[Micron]

sounds like a BOM (Bill Of Materials) set of tables to me. Each assembly is made up of some group or package, which in turn are made up of parts. However, the complexity can be high, as in assembly of multiple sub assemblies then Compoment(s) then Parts per component or just assembly > component > parts

You'd decide on how many levels you need, and each level is a junction table with each record being a child of that particular level. So maybe research BOM databases.
You might find a schema for this here
http://www.databaseanswers.org/data_models/

[orange]

You may get some ideas from this link:
http://www.utteraccess.com/wiki/Expa...Data_Structure
or this draft model of parts which are used to make Assemblies which are sold as FinalProducts.

[Dave14867]

khayward,

I went thru the same thing your trying to do a while ago and found it is quite a task but a doable one. See picture below of just one of the reports created.


I have over 1300 individual parts that I have to track from the PO being placed to parts being shipped. All parts go into some sort of sub-assy or parent part.

I got loads of assistance from others here like Micron and Orange and many others.

I'll be happy to assist in any way I can. I would start from scratch if I was do do it again, I used a template from the web but I had to go through a lot to get what I wanted to make everything work properly and found it harder than starting from scratch in hindsite.

My database has the ability to tell where any part is used, how many are needed, how many are in stock, etc.

Thanks

Dave

[khayward]

Thanks Micron and Orange for the pointers! I've gotten pretty far with my own database - I'm using junction tables a lot to connect components to assemblies and other things, I'll try and share what I have at some point soon, or at least some screenshots. Dave: I built mine from scratch because when I began this I didn't know enough to understand what was going on in a lot of the templates that were available. I took some DB and SQL classes in college but not enough to be anything other than dangerous and I'm still in the corner with a dunce cap on for the most part. At this point I am 5 years in, lots of slow, erratic development but I now have a system that shows BOMs, where used, related equipment, related documents, equipment maintenance scheduling and tracking, employee training records, order history (Very limited, based on excel imports from some time back) and performance charts. It's been a lot of fun, but the inventory piece is going to be the most important piece, and also the most scary. I would be happy to privately share a copy of the system with any of you folks, always looking for feedback

[orange]

You can post a copy of your db here in the forum in zip format. It is likely to get more and diverse comment/feedback here. Make sure you anonymize your data if it is private or confidential.
Also, if you are addressing a specific issue, then inform readers how to get to the issue and tell us about the issue and what you expect --sort of what you are getting and what you want to get with some sample data. It will make any interactions more efficient than having readers guess or stumble upon an issue.
It is also a good starting point to describe the "business" this database is intended to support in simple, plain English with no database/jargon.
Good luck with your project.

[Dave14867]

khayward,

Like Orange said, post a copy but make sure you take out confidential info and lots of folks will assit. It sounds like you have things in yours that I don't in mine. Mine is for the inventory control aspect only, I don't have employee info, dwg's, etc. in mine but I can track parts very well. I can tell what lot # or serial # of any part went into a specific top level build, how many of any parts of each part number was used, etc. I have read many places that the inventory control part is the hardest.

It sounds like you have a good grasp on things so far so keep plugging away and just ask specific questions when they come up.

Thanks

Dave

[khayward]

I don't have too much time to go into details today, but I understand about posting specific questions with solid details. Right now I'm trying to wrap my head around things hence the request for reading materials. I have anonymized a copy of my database so I'm happy to share, just hesitant because it a large file. No idea why that makes me hesitant but I grew up before dial-up, go figure.

I will say that I did have a lot of help on this system last year from user Davegri, just want to give credit where it's due.

Thank you guys for being here!

[orange]

If you zip the file, it should be fine. Yes, we all have a certain hesitancy with internet etc.

[khayward]

Having trouble with the zip file -is 23mb too big for the site file manager?

[Micron]

did you compact/repair your copy first? limit is 2Mb zipped, I think. 23 Mb sounds like a lot (if compacted) for a db in early development.
Is it split? If not, that could be a way around the issue - split, c/r & zip each part.

[khayward]

did you compact/repair your copy first? limit is 2Mb zipped, I think. 23 Mb sounds like a lot (if compacted) for a db in early development.
Is it split? If not, that could be a way around the issue - split, c/r & zip each part.

It is in early development, but also in use and choc full of data, I might have to take everything out, rather than just sensitive info.

[orange]

Perhaps you can make a copy of your database and remove all but a few records and post as zip--just enough data to highlight the problem.

You do have at least 1 current Backup of your database stored in a safe place, right??

[khayward]

Perhaps you can make a copy of your database and remove all but a few records and post as zip--just enough data to highlight the problem.

You do have at least 1 current Backup of your database stored in a safe place, right??

I'll be doing that over the next few days as I have time. And yes, several backups including various stages of development from over the last year or so - I've had it glitch on me enough times to make sure of that!

[khayward]

Hi all,
In the midst of an IT upgrade so I didn't respond to this yet - now I have to get used to Access 365. Lame. Anyhoo, I wasn't able to share a blank copy of my database file due to size. Because, like an idiot, I never really went through and defined field sizes and so forth. I've created a blank copy and will be going through all of those steps over the next couple of weeks, then I'll be able to share it...