Sales & Subassembly Database

[camerontaylor]

The company that I work for wants to potentially use an Access database to hold Sales IDs (lists of subassemblies to be compiled into a sale product) and to hold a list of subassemblies, which are grouped together in each Sales ID accordingly. Eventually, they are hoping to expand the database to incorporate inventory and raw materials, and essentially have something which can take a Sales ID as an input, create a work order for production, and tell them what subassemblies are required and what parts are required for each subassembly, and whether they're in stock or not.

Now, I've been told to look for a potential already existent database, but I have also been told to look into other software options. So, I know that this post isn't strictly related to Access, but if anybody has any feedback or suggestions for pre-existing DBs or has any reviews of certain manufacturing and inventory management software, then I'm all ears.

 

[The_Doc_Man]

You could try searching this forum for "Sub-Assembly" and "Component Inventory" as this topic has come up before. We have tons of "Inventory" entries but some of them do discuss cases of both primary products and components in the same inventory.

Hint for thinking about the design of your database. There is no difference FOR INVENTORY PURPOSES between a complete assembly and a component. Both are things that are in storage bins or on shelves or in boxes or whatever. However, there may be a secondary relationship among the inventoried items regarding whether you have what you need to actually MAKE another completed assembly. The "what's on hand" question is therefore different from the "what can I make" question.

 

[Uncle Gizmo]

It's a good idea to start out with an off-the-shelf package, you may be lucky and find one that does everything you want. Often you can get a free trial period which allows you to see if the software is suitable.

If you can't find an off-the-shelf package which is suitable, your experience with the off-the-shelf package will help you specify the requirements for your database construction.

The other thing to bear in mind is that MS Access is most suited for a networked environment, a LAN, a local area network, with wired connections and does not play well with wireless connection.

If you have multiple sites. or off site requirements, then MS Access may not be suitable.

 

[camerontaylor]

You could try searching this forum for "Sub-Assembly" and "Component Inventory" as this topic has come up before. We have tons of "Inventory" entries but some of them do discuss cases of both primary products and components in the same inventory.

Hint for thinking about the design of your database. There is no difference FOR INVENTORY PURPOSES between a complete assembly and a component. Both are things that are in storage bins or on shelves or in boxes or whatever. However, there may be a secondary relationship among the inventoried items regarding whether you have what you need to actually MAKE another completed assembly. The "what's on hand" question is therefore different from the "what can I make" question.

Thanks for getting back to me. I've done some digging and have found some useful articles, for which I'm very grateful for your help

 

[camerontaylor]

It's a good idea to start out with an off-the-shelf package, you may be lucky and find one that does everything you want. Often you can get a free trial period which allows you to see if the software is suitable.

If you can't find an off-the-shelf package which is suitable, your experience with the off-the-shelf package will help you specify the requirements for your database construction.

The other thing to bear in mind is that MS Access is most suited for a networked environment, a LAN, a local area network, with wired connections and does not play well with wireless connection.

If you have multiple sites. or off site requirements, then MS Access may not be suitable.

Just out of curiosity, do you have any experience with any off-the-shelf packages, or any recommendations? I have been given a week to do research into different options, but from my research, it seems that the best option is an MRP. However, most of the MRPs that I have found are quite expensive.

 

[mike60smart]

Hi Cameron
Do you have an example Excel file with your Sub Assembles and related Parts?

 

[Pat Hartman]

I've created several applications that deal with inventory and bill of material structures. They were not trivial so unless you are an expert developer, I would try to find a product that comes close to matching your requirements. It's almost impossible to find a perfect match but you can probably get pretty close unless you have some non-standard requirements. Keep in mind that you need an actual spec ahead of time. You won't know if a packaged product is suitable unless you know EXACTLY what you need. And if you decide to build, you need even more details in the spec.

WARNING --- BE CAREFUL wen looking at products to buy. Subscription based web software is all the rage. That model is excellent for the company that sells it because once they suck you in, they own you. That means for your employer's safety, that the first thing you need from the provider (and you need it in writing before you sign a contract) is a plan for exporting your data into a usable format should you decide you want to move to a different provider or build your own. Remember, with a web based subscription, you are very unlikely to have direct access to the hosted BE. If you will have direct access, that is a much safer situation for your company.

Never forget what AWS did to Parler in Jan 2021 when AWS decided they didn't like Parler's politics. Make sure you can't be cut off at the knees if your company takes a position that the provider disagrees with and they decide to cancel you. In the past when you bought a perpetual license and installed it on your own servers, you were in a much stronger position. They couldn't turn off the app and give you 24 hours to take your data and be gone which they can do today with a web based subscription. Make sure your contract protects you from being "cancelled".

 

[Mike Krailo]

Make sure your contract protects you from being "cancelled".

What's this world coming to? It's like whoever is hosting your data can essentially use it against you like ransomware. Better have a really good backup plan.

 

[Uncle Gizmo]

Just out of curiosity, do you have any experience with any off-the-shelf packages, or any recommendations?

No not really, only that they can give you an insight into different ways of doing things, things you might not have thought of yourself.

 

[jdraw]

Cameron,

Do you have a written set of requirements? You'll need same or similar to compare OTS software products.
As others have said -inventory is not trivial and hierarchical structure is an additional complexity. Could such a thing be done with Access? Certainly - but not a project for inexperienced developer in my mind. Pat and others have provided some forewarning - very worth considering.

 

[camerontaylor]

I've created several applications that deal with inventory and bill of material structures. They were not trivial so unless you are an expert developer, I would try to find a product that comes close to matching your requirements. It's almost impossible to find a perfect match but you can probably get pretty close unless you have some non-standard requirements. Keep in mind that you need an actual spec ahead of time. You won't know if a packaged product is suitable unless you know EXACTLY what you need. And if you decide to build, you need even more details in the spec.

WARNING --- BE CAREFUL wen looking at products to buy. Subscription based web software is all the rage. That model is excellent for the company that sells it because once they suck you in, they own you. That means for your employer's safety, that the first thing you need from the provider (and you need it in writing before you sign a contract) is a plan for exporting your data into a usable format should you decide you want to move to a different provider or build your own. Remember, with a web based subscription, you are very unlikely to have direct access to the hosted BE. If you will have direct access, that is a much safer situation for your company.

Never forget what AWS did to Parler in Jan 2021 when AWS decided they didn't like Parler's politics. Make sure you can't be cut off at the knees if your company takes a position that the provider disagrees with and they decide to cancel you. In the past when you bought a perpetual license and installed it on your own servers, you were in a much stronger position. They couldn't turn off the app and give you 24 hours to take your data and be gone which they can do today with a web based subscription. Make sure your contract protects you from being "cancelled".

Thank you Pat. That is extremely useful advice that I hadn't thought of. While we are looking into perpetual license solutions, they are becoming more and more difficult to find, and so some of the options I'm looking into are web-based. I really appreciate the advice!

 

[Pat Hartman]

Hope it helps. Two of my clients have been burned by web hosted apps.

 

[camerontaylor]

Hope it helps. Two of my clients have been burned by web hosted apps.

It definitely does. Out of curiosity, and if you're comfortable sharing, what were the names of the apps your clients were burned by? Just so that if we decide to go that route we don't make a bad decision

 

[Pat Hartman]

One was Salesforce. I don't remember the other. The Salesforce client ended up printing out reports and rekeying his contacts. The other wasn't so lucky.