Creating a cheese production record from scratch

[strive4peace]

not being able to live up to expectations.

@Panayiotis, nothing to aplogize for! You're busy doing the actual tasks and Access will be a wonderful help once it is set up. The sooner that happens, the sooner time to track things can be alleviated ... but meanwhile, business must go on ... and you also have quite a learning curve ~

Take the time you need and come back here as you are able to assimlate. Wishing you the best success.

 

[JMongi]

I'll leave the database comments to those much more experienced then I. However, I will comment on this line:

I became overwhelmed with this project and decided to take a step back; I felt like I would need too much feedback and time from other people's valuable time, for a project that I ultimately volunteered to create for the company I work for, while also making cheese every day.

My suggestion to you would be to figure out the answers to the following questions:

1. When you say "volunteered to create", is there now an expectation of delivery by your company? A clearly defined scope (either by you or your company) will help you to not feel as overwhelmed. Breaking it down into manageable "modules" is a good way to manage your time going forward.

2. How much are you willing to learn? There is a learning curve transitioning from spreadsheet think to database think. But, it is not impossible. Many on this forum have done just as you have for a similar reason (throwing myself in this group). But, there are unique terminologies (schema, entity, attribute, normalization, SQL, recordset, etc) to relational databases. There are further concepts involved in using VBA (Virtual Basic for Applications) which is often leveraged to make our database applications work the way we would like them to do. All of this requires time to educate ourselves. How much time to do you have? That will naturally control the speed with which you can learn and implement these concepts. This forum is an excellent resource.

3. How good does this need to be? This one is near and dear to my heart because I have a tendency to do things "best" instead of "good enough". Striving for excellence is admirable. But, it can bog you down when you don't have the requisite knowledge to BE excellent. With apologies in advance to @Pat Hartman, a wrongly created database may still often function adequately for the situation at hand. Pat's experience tells her that you WILL be redoing things at some point when your recognize the mistakes or problems. She's trying to save you the hassle and headache. Sometimes, implementing a poorly designed database will more quickly teach you good database design than anything else (the proverbial school of hard knocks). It is something to consider.

You might liken it to a math proof. When learning, a student might create a math proof that is 30 steps long. That might be 20 more steps than actually needed for the proof. By the time you complete the 30 step proof, though, you now understand the 10 step proof you should have used from the beginning. But, the 30 step proof IS STILL A PROOF! It still solved the problem even if it was inefficient and needs fixing and you won't do it again. Just something to consider.

4. How much work are you willing to give up? I didn't dive into your database myself, but, based on others' posts your table structure (schema) is not great. Your forms and the work you've done is based upon this flawed foundation. In order to properly fix it, you may have to start from square one or close to it. Are you willing to potentially discard that work in order to get this right?

I feel like your answers to these questions will help you to chart a path forward. As a help, here is a bookmark I had that might help you with your database knowledge:

Bookmark

 

[JMongi]

One more tip...the longer a thread goes, the better the "Similar threads" recommendations become. They are usually listed at the bottom of your thread. I've found some really good information looking through some of the threads. Many are quite old and would not normally be found perusing the forum casually.

 

[JMongi]

Actually, Pat's experience tells her that if you build it wrong to begin with, it will stay wrong forever because if you can't take the time to fix 10 things now, you certainly are not going to take the time to fix 50 or a hundred things later. And managing the poor design will grow to be your singular job or simply be abandoned when you realize you can't fix it. Then you'll blame Access rather than your poor schema.

I would say this depends upon the individual but I'm sure you have seen a lot of this happen over the years. I would put my only caveat on this that the time it takes to "fix 10 things now" might still be more than the time needed to "fix 100 things later" if one's understanding and experience have grown exponentially between those two times. However, I still acknowledge the likelihood of your point being correct in a majority of cases. Maybe again, I'm too skewed by my own wheel spinning that is unique to my own mental hangups.

The rest of your points, particularly "not great" vs "not workable" are a welcome boundary to the points I was making. I admittedly am approaching my "advice" from my own perspective of needing to scale back my initial package to something more workable. I particularly like "identify your single worst problem and work on solving that". Great advice!

 

[GPGeorge]

Spoiler: etc

Actually, Pat's experience tells her that if you build it wrong to begin with, it will stay wrong forever because if you can't take the time to fix 10 things now, you certainly are not going to take the time to fix 50 or a hundred things later. And managing the poor design will grow to be your singular job or simply be abandoned when you realize you can't fix it. Then you'll blame Access rather than your poor schema.

There is a difference between "not great" and "not workable". Poor naming practices are "not great". They will cause typos and errors but if you have properly defined Option Explicit, at least you'll find most of the errors if you bother to compile. But novices rarely bother to compile. Poor structure is "not workable".

While I agree with @JMongi that the app doesn't have to spring forth as perfect and complete but if your company is making business decisions based on it, you'd better not be giving management wrong information or you could single handedly bankrupt a company.

It is far better to identify your single worst problem and work on solving that. Make the scope as small as possible. You are doing everything manually now. Automate just a little bit at a time while you get your sea legs. If inventory is your worst problem, work on solving that. But, keep in mind that inventory is one of the more complex "simple" processes. I call it "simple" only because when someone says "inventory" most people can at least visualize it generically. It goes downhill from there. This is not an inventory of widgets. It is an inventory that requires using "part" of something and managing units of measure and expiration dates. If you purchase by the gallon and use by the quart or pound, you better know how to do the conversion accurately. You also need to understand that updating quantities is not the way to manage inventory. You need to do it with transactions. Record what you receive from the vendor. Record what you add to a batch. Record what you discard because it is expired. Add up all the transactions and you know what you have on hand. When you Record what you add to a batch, you need to understand how to pull from the oldest, "open" inventory bucket. Otherwise you will waste material if you allow it to expire.

Adding columns to existing tables doesn't break any existing objects. Adding new tables doesn't break any existing objects. Having to split a table into multiples because you didn't recognize repeating groups or understand normalization breaks EVERYTHING.

An experienced programmer can work with a poor schema. It will take longer to do things and it will require significantly more code and new stuff will be ever so much harder than it needs to be. You won't even know that if you had followed some early direction by an expert, you could do "y" with a new query but instead you end up building some complicated process and have to learn enough VBA to become dangerous and two weeks lager, you're very proud of yourself because you think you got it to work but if you didn't test well, maybe it isn't working and one of the experts could have done the same job in 10 minutes.

Spoiler: etc

We don't get paid to help you. We do it out of a desire to encourage people to use our favorite development platform and to help the community in which we circulate. You can take our advice based on our years of experience and considerable pain incurred to acquire it or you can ignore us. I warned you to not create objects until your schema was solid but you ignored that advice and here we are. You can probably make what you have work if you use enough code although if you don't move the code to the correct events, you will be saving bad data (don't blame it on Access. You are not using Access correctly). Then once you have it "working", the change requests will start and rules you thought were cast in concrete will dissolve as the users ask to make a third kind of cheese and your schema won't support it without changes whereas if you looked into your crystal ball and asked yourself, Hmm, what if we need to make different kinds of cheese? What if we needed to run multiple lines making different cheeses at the same time? What if we need to make yogurt?

I once coined a phrase regarding that, which I repeated to myself at regular intervals while working on client projects.

There's nothing admirable about writing wads and wads of code to compensate for poor design decisions.