Order entry and tracking system (1 Viewer)

[mike60smart]

I am looking at what is best to create the dummy GUI - I was thinking Powerpoint which I have used before but ChatGP suggests Figma which I have never heard of or used.

Hi Alex
Have you started putting together any tables in Access yet?

If you have then upload a zipped copy and we can then advise much easier.

 

[LarryE]

I do have a template for building products that incorporates vendor purchase orders, customer orders, product build inventory details and employee labor costs, but I don't know if that will meet your needs until you answer some important questions:

1. How does your current vendor purchase order process work?
2. How does your current customer order process work?
3. Do you want to include employee labor time in your product cost calculations?
4. How do customers pay you? Do you present an invoice at the end of the build process?
5. Do you always build custom to suit lighting designs or do you build several makes and models of products and keep them in inventory for later sale?

This will be a long process so get ready for that. All of these kinds of questions need to be answered before any ACCESS design is built from scratch or re-built from an existing template. All ACCESS designs follow the workflow process first, then tables are designed and relationships established. You already gave us some sense of what kinds of information you wish to track so that is good, but we need to know how those fields are going to be used and what they are so we can determine which tables they belong in. All of these things will be at least a start.

 

[Pat Hartman]

The Northwind dev edition is the only sample I have ever come across that even attempts to handle inventory. So, if you need your application to also manage your inventory, we need to help you to get the dev version up and running. You may still not be able to use it as is but at least it handles inventory in a rational way. If you don't need to manage inventory, the problem becomes simpler.

 

[AlexT]

I am looking at what is best to create the dummy GUI - I was thinking Powerpoint which I have used before but ChatGP suggests Figma which I have never heard of or used.

Hi Alex
Have you started putting together any tables in Access yet?

If you have then upload a zipped copy and we can then advise much easier.

Hi Mike,
I am currently creating a menu GUI so I can determine what I really require. I will post it soon.

 

[AlexT]

The Northwind dev edition is the only sample I have ever come across that even attempts to handle inventory. So, if you need your application to also manage your inventory, we need to help you to get the dev version up and running. You may still not be able to use it as is but at least it handles inventory in a rational way. If you don't need to manage inventory, the problem becomes simpler.

Hi Pat,
I am not looking to handle Inventory other than load a Product from the database. I may look at expanding it later but in the first instance I simply want to create a system for entering Customer orders and then tracking them through the production process andpossibly automatcially creating the shipping labels and a standalone invoice.

 

[AlexT]

I do have a template for building products that incorporates vendor purchase orders, customer orders, product build inventory details and employee labor costs, but I don't know if that will meet your needs until you answer some important questions:

1. How does your current vendor purchase order process work?
2. How does your current customer order process work?
3. Do you want to include employee labor time in your product cost calculations?
4. How do customers pay you? Do you present an invoice at the end of the build process?
5. Do you always build custom to suit lighting designs or do you build several makes and models of products and keep them in inventory for later sale?

This will be a long process so get ready for that. All of these kinds of questions need to be answered before any ACCESS design is built from scratch or re-built from an existing template. All ACCESS designs follow the workflow process first, then tables are designed and relationships established. You already gave us some sense of what kinds of information you wish to track so that is good, but we need to know how those fields are going to be used and what they are so we can determine which tables they belong in. All of these things will be at least a start.

Hi Larry,
I will have a graphical mockup of the process soon. In the first instance am only looking for a system to handle Customer Orders. I don't need 1. Vendor orders, 3. Labor time calcs.
Payments from customers vary. Website sales are prepaid and sales to wholesalers are invoiced for payment on the 20th of the following month. We use Xero for that. It is not necessary to have a totally integrated system for our business as opposed to having modules that do their task well.

 

[AlexT]

I've been importing bits and pieces of Northwind Starter which seems to be more stable. Is Northwind in a trusted location?

Not sure what trusted location means I will read on it.

 

[Cotswold]

I'm not sure that this is going to fly. After 6 days and 27 posts nobody is any the wiser. A graphic will not be a specification.
The specification, should show what you want out of the system and include table structures. Table design is the beginning. Plus, you cannot proceed with a program segment. You must have it all. Otherwise you will be continually updating existing tables as you add new ones. Which will affect any existing code. You will be amending all the time, which is not the way to go.

If you cannot specify what you need, even experienced developers cannot help you. They will have a good idea what you want but not actually what your imagined detail. Then after you have the system working, you will need to install security to prevent tinkering. The list goes on and just from what you have written to date, my guess it will probably take an experienced developer with a stack of libraries maybe every day several months. For you to do it, I'd say at least 12 to 18 months to include learning and understanding for something the business can rely on.

You are using Xero which from a (very) brief look does not allow import/export and is quite basic. Your proposed system whilst maybe not needing to be integrated, will need to export to and possibly import from accounts. Otherwise you will need to manually post invoices. Which was pointless in 1985, never mind 2025. You will need to use a business accounting system. SAGE will be fine and some versions may do everything that you want. SAGE also have approved add-on systems. Xero is unlikely to suit an expanding business. Don't be tempted into using spreadsheets. This is a database application.

One thing that you can be assured of, is that what you want to do has been done before. Many times. There will be many systems out there but it
will take effort to find them and assess. From where you are I'd suggest you use your time running the business. Get reps in, give them your spec and see what they have. If that fails, then maybe start burning the midnight oil.

 

[AlexT]

I'm not sure that this is going to fly. After 6 days and 27 posts nobody is any the wiser. A graphic will not be a specification.
The specification, should show what you want out of the system and include table structures. Table design is the beginning. Plus, you cannot proceed with a program segment. You must have it all. Otherwise you will be continually updating existing tables as you add new ones. Which will affect any existing code. You will be amending all the time, which is not the way to go.

If you cannot specify what you need, even experienced developers cannot help you. They will have a good idea what you want but not actually what your imagined detail. Then after you have the system working, you will need to install security to prevent tinkering. The list goes on and just from what you have written to date, my guess it will probably take an experienced developer with a stack of libraries maybe every day several months. For you to do it, I'd say at least 12 to 18 months to include learning and understanding for something the business can rely on.

You are using Xero which from a (very) brief look does not allow import/export and is quite basic. Your proposed system whilst maybe not needing to be integrated, will need to export to and possibly import from accounts. Otherwise you will need to manually post invoices. Which was pointless in 1985, never mind 2025. You will need to use a business accounting system. SAGE will be fine and some versions may do everything that you want. SAGE also have approved add-on systems. Xero is unlikely to suit an expanding business. Don't be tempted into using spreadsheets. This is a database application.

One thing that you can be assured of, is that what you want to do has been done before. Many times. There will be many systems out there but it
will take effort to find them and assess. From where you are I'd suggest you use your time running the business. Get reps in, give them your spec and see what they have. If that fails, then maybe start burning the midnight oil.

Hi Cotswold, Thanks for taking the time to give me your thoughts. Not being a programmer (at all), I first need to create a graphical flow of what I am trying to achieve so that an expert has at least got a starting point. In the first instance, I had asked if anyone knew of something that might meet the "general" outline of what I am trying to achieve as I figured there must be 1,000's of templates out there. But clearly I need to be more specific about what I want to do. I am not about to embark on any development myself as my 5 minutes of investigating Access shows me that would be stupid - it is not plug and play for bunnies like me. The developers have given me some great advice and I am now working on trying to be clearer about my requirements. While basic business accounting systems do provide integration of various tasks, they do not have the ability (at least not that I am aware of), to do the basic task of order management in the manner that suits our small operation - meaning very little or no customisation. I also do not want to use cloud based software. Your comment about having to manually post invoices, is only a small pain in the backside for us as we don't do that many. I would rather have an easy to use order tracking system that allows customisation. I found an excel template created by Someka that is something like what I am looking to do. However it is not formatted to meet our requirements and will require work to alter. Everyone tells me not to use spreadsheets, so I am looking for a database version of something similar. The GUI is very important to ensure ease of use.

 

[tvanstiphout]

I think you are in need of a Business Analyst. This person works with you to understand what you want, and creates a Functional Spec that developers can use to understand what you want.
Some people in Access forums are pure developers, and want to design databases and code business logic. Others are more focused on understanding needs and translating that into requirements, database design, and specifications. That latter person is who you should hire for a few days. Best money ever spent.

 

[arnelgp]

on my opinion you don't need to hire an analyst, you yourself can tell your programmer what to do since you know very well exactly what you need. you can show him the GUI you want and your programmer will translate that to you. you can show him the computations and he will also translate that to you.

 

[Cotswold]

In the past I have installed software at hundreds of companies, my own and third party software. Very few of the business owners understand any of the software that their business relies on every day of the year. They also have little understanding of the possibilities. Many of them are unable to use most, or any of their software. On that they rely on their staff. However, they all have the knowledge of what they want the software to do. Although some have had difficulty in describing their requirements. An experienced software consultant/developer should be able to understand your requirements. Find someone you can work with who is reliable.

Creating software is time consuming and at the end of the day should be 100% correct. A plumber, builder, electrician, mechanic can get away with their work being good enough. Or take the attitude, that'll do. Not so with systems your business relies upon. Just remember, if you change something it will cost you. Better to spend more time at the start. Preparation and specification.

 

[LarryE]

Hi Cotswold, Thanks for taking the time to give me your thoughts. Not being a programmer (at all), I first need to create a graphical flow of what I am trying to achieve so that an expert has at least got a starting point. In the first instance, I had asked if anyone knew of something that might meet the "general" outline of what I am trying to achieve as I figured there must be 1,000's of templates out there. But clearly I need to be more specific about what I want to do. I am not about to embark on any development myself as my 5 minutes of investigating Access shows me that would be stupid - it is not plug and play for bunnies like me. The developers have given me some great advice and I am now working on trying to be clearer about my requirements. While basic business accounting systems do provide integration of various tasks, they do not have the ability (at least not that I am aware of), to do the basic task of order management in the manner that suits our small operation - meaning very little or no customisation. I also do not want to use cloud based software. Your comment about having to manually post invoices, is only a small pain in the backside for us as we don't do that many. I would rather have an easy to use order tracking system that allows customisation. I found an excel template created by Someka that is something like what I am looking to do. However it is not formatted to meet our requirements and will require work to alter. Everyone tells me not to use spreadsheets, so I am looking for a database version of something similar. The GUI is very important to ensure ease of use.

At the risk of repeating myself, I'll repeat myself:
Can you describe your current order entry system for us and if you wish to keep track manufacturing and labor costs as well for each customer build. All ACCESS development is based upon the business process you have. The tables that you create and the relationships between them are the most critical part of any ACCESS build process. No one can make ACCESS behave like a spreadsheet, and the process is very different.

I have some questions based upon the spreadsheet pdf file you attached:

1. What is an "Enolt Order" and a "1500mm order"?
2. What are Singles, twins and triples?
3. Is "Part Number" your inventory part number or your vendor part number?
4. What does "Sell Each (GST EXCL) mean
5. What does Nett Sell Each (GST EXCL) mean?
6. What does "Sell Each (GST INCL) mean?
7. What does "Shipping (GST INCL) mean?
8. What does "Value of order ex GST On Acct." mean?
9. What does "Value of order INCL GST On Acct." mean?
10. Is "Tracking #" your number or the shippers number?

Presenting a picture of some form from EXCEL does not help us help you. So, please answer my questions and MAYBE someone can help. I mentioned before that I do have a template I might be able to modify, but I can't do that without your help.

 

[Pat Hartman]

@AlexT I've created at least a half dozen different order entry systems. At their heart they are the same and so start with the same few tables. But, the reason many companies opt for custom order entry systems is so that they don't have to change their workflow and so they can support specific types of customization of the ordered product. So, everyone who has tried to help you has the core Order Entry System in their minds and what we are trying to figure out is how yours is different. But the core system is basically, select an item from inventory. Enter a quantity. Determine the tax status of the customer so that proper taxes can be applied. Send the order to Picking. Pick the order. Print the Invoice. Send the Order.

 

[AlexT]

Yep Pat - an excellent summary. I want the software to meet my requirements not the other way round. Do you have a template I can look at?

 

[AlexT]

At the risk of repeating myself, I'll repeat myself:
Can you describe your current order entry system for us and if you wish to keep track manufacturing and labor costs as well for each customer build. All ACCESS development is based upon the business process you have. The tables that you create and the relationships between them are the most critical part of any ACCESS build process. No one can make ACCESS behave like a spreadsheet, and the process is very different.

I have some questions based upon the spreadsheet pdf file you attached:

1. What is an "Enolt Order" and a "1500mm order"?
2. What are Singles, twins and triples?
3. Is "Part Number" your inventory part number or your vendor part number?
4. What does "Sell Each (GST EXCL) mean
5. What does Nett Sell Each (GST EXCL) mean?
6. What does "Sell Each (GST INCL) mean?
7. What does "Shipping (GST INCL) mean?
8. What does "Value of order ex GST On Acct." mean?
9. What does "Value of order INCL GST On Acct." mean?
10. Is "Tracking #" your number or the shippers number?

Presenting a picture of some form from EXCEL does not help us help you. So, please answer my questions and MAYBE someone can help. I mentioned before that I do have a template I might be able to modify, but I can't do that without your help.

Thanks Larry, I am working on the spec and hopefully will have it later today. In answer to your questions -
1. Ignore that as we simply need to create a Sales Order
2. Simply different models - we have approx. 10 models and each model may have several options for example, LL1500S-O-WK is a 1500mm single with an Opaque lens and a wires suspension kit. A LL1500S-C-CK is a 1500mm single with a clear lens and a chain suspension kit.
3. Part Number is our internal part # - this piece of software does not deal with Vendors in any way.
4. Is our standard selling price excluding GST (Goods and Services Tax) -prior to any discount.
5. Nett Sell (GST Excl) is after any discounts have been applied to the order.
6. Sell Each (GST Incl) is the Nett Sell with 15% GST added.
7. The cost we pay to ship the product to the customer - sometimes we charge the customer sometimes the shipping/freight is included.
8. Some of our sales are prepaid and some are charged to a customers account. We sell to end users and to Wholesalers. It exlcudes the GST.
9. Same as above but including 15% GST.
10. This is the shipping tracking number that we send to the customer so they know where their package is.

GST (Goods and Services Tax) is a govt tax of 15% on all sales. When we sell something we have to add 15% tax which we collect for the govt. Each month we then have to supply the govt with a Tax return that lists all the tax we have collected on their behalf and give the money back to them. It is essentially a Zero transaction. If you are a business and are GST registered then you deduct any GST you have paid from the GST you have received and you either get a refund or have to pay the govt.

Please keep in mind that my "template" (if you can call a Google sheet mockup a template) is not exactly as I want it to be. The Excel example from Someka is as close as I have seen but modifying it will no doubt mess up the code. If you have an existing template it would be helpful to look at. It is much easier to critique something than do it from scratch. Thanks for your input - I really appreciate it.

 

[AlexT]

I think you are in need of a Business Analyst. This person works with you to understand what you want, and creates a Functional Spec that developers can use to understand what you want.
Some people in Access forums are pure developers, and want to design databases and code business logic. Others are more focused on understanding needs and translating that into requirements, database design, and specifications. That latter person is who you should hire for a few days. Best money ever spent.

Hi, Thanks for taking an interest in my project. However a Business Analyst is not at all what is required. My experience (limited) with Business Analysts over the last 30-40 years has shown the old saying of "if you can't do it teach it" to have some real basis. I know what is required for my business I just don't know how to find it yet. I had hoped I would be flooded with example templates that I could pick and choose the best features from to meet my specific requirements. If you know your business (which I am confident I do), then it is highly unlikely that a fresh pair of eyes (be it from a Business Analyst) will add anything of significant value. I am not suggesting that Business Analysts don't have a role, just not in what I am doing with what should be a seemingly simple task.

 

[AlexT]

on my opinion you don't need to hire an analyst, you yourself can tell your programmer what to do since you know very well exactly what you need. you can show him the GUI you want and your programmer will translate that to you. you can show him the computations and he will also translate that to you.

I agree with that 100% - as many of the developers have said "Alex tell us what you want................................what you really really want. As I noted in one of the other replies it is easier to critique something than start from scratch. In my previous life overseeing some very successful (multi million dollar) international electronic products, that often included much more complicated software than this, I would get the team together and tell them what I wanted to achieve and then tell them to go get started. Come back to me tommorrow with your ideas and we will build on those. I am sure you are familiar with the design principle of "Prototype often". It allows the boss and the team to not be afraid of making mistakes and more importantly no-one gets a sense of ownership in a sketch they have taken a day to produce. What it does allow is the ability to design with hindsight - much easier to critique than create. Try telling an engineer that his 6 month project is rubbish. I clearly have not provided sufficient info to get started (as per my reply to Larry), but having said that where are all the existing templates for Order management - maybe they are all very proprietary and no-one wants to release them? I have made a very rudimentary spreadsheet but I know it can be made a lot better and automated.................................now if only I knew how to at least get started so I could use my business brain (which is not a programmers/developers brain) to get something I could kick around. Do you know of any templates I can look at for ideas? The Excel file from Someka shows the sort of interface I am looking to create and is the nearest thing I have found to what I need...............................but it won't do the job because customising it will be too difficult a task for me and everyone tells me that Excel is not the software to use. I am trying not to reinvent the wheel as opposed to modifying one. Sorry if that is a bit of a ramble.

 

[LarryE]

Thanks Larry, I am working on the spec and hopefully will have it later today. In answer to your questions -
1. Ignore that as we simply need to create a Sales Order
2. Simply different models - we have approx. 10 models and each model may have several options for example, LL1500S-O-WK is a 1500mm single with an Opaque lens and a wires suspension kit. A LL1500S-C-CK is a 1500mm single with a clear lens and a chain suspension kit.
3. Part Number is our internal part # - this piece of software does not deal with Vendors in any way.
4. Is our standard selling price excluding GST (Goods and Services Tax) -prior to any discount.
5. Nett Sell (GST Excl) is after any discounts have been applied to the order.
6. Sell Each (GST Incl) is the Nett Sell with 15% GST added.
7. The cost we pay to ship the product to the customer - sometimes we charge the customer sometimes the shipping/freight is included.
8. Some of our sales are prepaid and some are charged to a customers account. We sell to end users and to Wholesalers. It exlcudes the GST.
9. Same as above but including 15% GST.
10. This is the shipping tracking number that we send to the customer so they know where their package is.

GST (Goods and Services Tax) is a govt tax of 15% on all sales. When we sell something we have to add 15% tax which we collect for the govt. Each month we then have to supply the govt with a Tax return that lists all the tax we have collected on their behalf and give the money back to them. It is essentially a Zero transaction. If you are a business and are GST registered then you deduct any GST you have paid from the GST you have received and you either get a refund or have to pay the govt.

Please keep in mind that my "template" (if you can call a Google sheet mockup a template) is not exactly as I want it to be. The Excel example from Someka is as close as I have seen but modifying it will no doubt mess up the code. If you have an existing template it would be helpful to look at. It is much easier to critique something than do it from scratch. Thanks for your input - I really appreciate it.

Inventory Form at the present time:

Customer input, order detail and build detail form at the present time:

 

[mike60smart]

I agree with that 100% - as many of the developers have said "Alex tell us what you want................................what you really really want. As I noted in one of the other replies it is easier to critique something than start from scratch. In my previous life overseeing some very successful (multi million dollar) international electronic products, that often included much more complicated software than this, I would get the team together and tell them what I wanted to achieve and then tell them to go get started. Come back to me tommorrow with your ideas and we will build on those. I am sure you are familiar with the design principle of "Prototype often". It allows the boss and the team to not be afraid of making mistakes and more importantly no-one gets a sense of ownership in a sketch they have taken a day to produce. What it does allow is the ability to design with hindsight - much easier to critique than create. Try telling an engineer that his 6 month project is rubbish. I clearly have not provided sufficient info to get started (as per my reply to Larry), but having said that where are all the existing templates for Order management - maybe they are all very proprietary and no-one wants to release them? I have made a very rudimentary spreadsheet but I know it can be made a lot better and automated.................................now if only I knew how to at least get started so I could use my business brain (which is not a programmers/developers brain) to get something I could kick around. Do you know of any templates I can look at for ideas? The Excel file from Someka shows the sort of interface I am looking to create and is the nearest thing I have found to what I need...............................but it won't do the job because customising it will be too difficult a task for me and everyone tells me that Excel is not the software to use. I am trying not to reinvent the wheel as opposed to modifying one. Sorry if that is a bit of a ramble.

Hi Alex
Can you upload a copy of your current Excel file so we can see what your current requirements are?