Creating a cheese production record from scratch

[Pat Hartman]

Oops. I missed two connections

The relationship seems strange at first glance but relating the tblProduction to tblFormula is done so I can have a way to limit the combo in the tblProductionSteps form to select only steps for the single formula.

 

[Panayiotis]

Wow, thank you all for your help. If anyone is over in Ireland for a visit, please do call in Macroom, County Cork and I'll give you a VIP tour of our cheeseplant!

Mike60smart, you've given me alot to think about and work on!

Plog I'm starting to get it now, i was trying too hard to tweak what I had already done, but the best thing is to start from the basics. I should have devoted more time on learning about tables and their relationships before thinking about user interface and fancy vba stuff.

What can I say about you Pat, I'm indebted to you. I'll study the relationships and database and will be back soon for more

 

[Pat Hartman]

You're welcome As I said, it's a start. You are a very small operation and probably don't need to automate every step at this point. I built what I thought should be automated now and left the inventory part for later. This is an unusual application which I happened to have some specific experience with so I took my suggestions further than I might have if there were other databases which had similar needs I could point you to.

There is code you will need to make this easy for you. For example, each time you start a new batch, you will want to run an append query that copies the data from tblFormulaSteps for the type of cheese you are making now into the tblProductionSteps. It is tblProductionSteps that serves as the "blanks" in the form you use now and tblFormulaSteps which serves as the "words" that describe what goes into the "blanks". Hope that gives you an understanding of how it works.

Don't rush to create forms. Spend some time manually entering data into the table. It's a bit of a pain due to having to use numbers as the FKs but I changed the db layout to overlapping forms which will make it easier for you to have multiple objects open to work with at the same time. Do create queries that join the tables so you can see what the data looks like when it is all pulled together. Don't forget, you will be using append queries in the real app to copy data from FormulaSteps to ProductionSteps so the process will be very simple for the user. They won't have anything to do but to initiate the process and then fill in the blanks as they would have with the paper form.

Make sure you absolutely understand the schema before you commit to forms/code/reports. For now just stick with tables and queries. And don't hesitate to ask questions. There are lots of people here who can help, especially since I've made a basic schema.

 

[Pat Hartman]

Have you made any progress with my suggested schema? Do you have questions?

 

[JMongi]

I want to second that this particular thread can be VERY useful for those extremely dangerous individuals like the OP and myself who are technically inclined and understand the "basics" of Access the program (or even some of the "advanced" stuff) rather easily but trip on the "basics" of database design. I find the regularly used examples (teacher/student, etc) trivially easy to understand and therefore very inadequate for helping me design a "real" database. (Don't get me wrong they are great to use to demonstrate concepts. But, the requirements of a demo are to be very simple and thus limited).

The comments by @plog identifying "entities" and by @Pat Hartman outlining how to organize "attributes" are the intermediate steps that are often glossed over and usually only discussed in real relational database design classes/tutorials. This is why I like this forum so much!

 

[Pat Hartman]

@JMongi Thanks for your support. It is really disappointing to have spent so much time on this model to end up having no feedback from @Panayiotis . The concepts in this application apply to many other situations where we need to define something and then use it. Another situation would be a survey/questionnaire. We define the elements of the survey such as the sections and the questions and then someone takes the survey and provides answers to the questions.

 

[Panayiotis]

Hello all. @JMongi , you're absolutely right. So many tutorials out there show pretty much the same regularly used examples, it's hard to transfer all that knowledge to specific industry-related situations. In my case, I think I would be a much better learner and would adapt that knowledge easier if I didn't have that bit of previous knowledge that has nothing to do with relational databases (my background is in academia, PhD in education, so had only worked with excel for data collection and spss to analyse those data).

@Pat Hartman, hence 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.

So I decided to focus on the stock (goods in) and packing side of things, for anyone interested I'm sharing it here. I used knowledge gained from everyone's advice here to revamp the tables. Two main records come out of this: goods in and daily packing log. I hope in due time, I will be able to revamp the production side of things that I used to include in this project.

Again thanks to everyone, apologies for not being able to live up to expectations.

 

[mike60smart]

Hi

Sorry but where do we start.

Date is a reserved word and should not be used as a field name.
Tables should have a Primary Key using a Long Integer Autonumber
There should be no spaces in field names or any special characters like !@#$%^&*()
You should not use Lookup fields in tables.
Your tblPackingSteps and tblDailyPackingRecord structures are wrong.
You have listed all of the steps but each Step should be a Record in a related table.

 

[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.

 

[Pat Hartman]

In addition to the comments by @mike60smart , here are some more.
1. Using ID as the name for the PK of a table just causes confusion, use a meaningful name such as StaffID and then use the same name when you create the FK. It is ever so much easier to "see" the relationships if your naming standard is helpful.

2. Ditto the naming standards. The only VALID characters in an object name or variable name are the letters a-z and A-Z, the numbers 0-9, and the underscore. Access is way more flexible than VBA and will allow you to create object names that VBA cannot support. Sometimes, Access will give you a warning to not use a particular name for a control. Don't ignore the warning. It is far better in the long run to go with the lowest common denominator and only create names that VBA will be happy with. You won't regret it. Remember, you are now working with a relational database so the names of your objects and variables are NOT necessarily the names your user will see in forms and reports. Don't confuse Access with Excel where the data and presentation layers are combined so if you call something FullName, you are stuck with that for your "reports" also.

3. Also, be careful to avoid using Reserved Words as your Column names. This can be difficult given that you are working with VBA, Access, and SQL so the list is pretty long and you will never memorize it, nor should you bother. The worst mistake is to use "Name" as a column name. The second worse is to use "Date". Every object has a Name property so when you are writing VBA and you write Me.Name, what "name" do you think you will get? So, whenever you want to name an object, if the name you choose is very simple and common, it is almost certainly "owned" by VBA, Access, or SQL So, use compound names. They are more descriptive anyway. PackingDate, ProdName, etc.

4. And then there's the repeating groups. The obvious ones are those with numeric or single letter suffixes. The less obvious ones are the "Labels" set in PackingSteps and the "check" and "scales" in PackingRecord. When you have more than one of something, you have "many" and "many" requires a separate child table so that you don't limit yourself to 10 "w" or 3 "check" or 3 "batch", etc. In a relational database, it is never right to make hard limits.

5. Using Screen Active control is very dangerous because you don't always know what control Access or Windows things is active. There are better methods to use your frmUseByCalandar that will ensure you are always incrementing the intended control.

6. You are not doing much validation but in all the cases I have seen, the validation is being done in the wrong event. The best, event to use for validation is the FORM's BeforeUpdate event. This is the last event that runs before a record gets saved and it CANNOT be bypassed. It runs regardless of what made the form dirty and regardless of what caused Access to save the record. Think of it as the flapper at the end of a funnel. If the flapper is open, the record is saved. If the flapper is closed, the record will not be saved. YOU have COMPLETE CONTROL but ONLY if you use the correct event. If you use other events, they will work under some but not all conditions because all of them can be bypassed in some manner EXCEPT for the FORM's BeforeUpdate event. Even the Control's BeforeUpdate event can be bypassed. If you don't put focus in the field, there is no way to activate its events. If you use VBA to put a value in the field, the control level events won't fire. ONLY the FORM's BeforeUpdate event can never be bypassed.

7. I have no clue why you are using the Change event of multiple controls to set AllowAdditions to False. You've got this in multiple controls in multiple forms and in no case is that the correct event to set this property. The form and control level events are NOT random. They are defined for specific purposes and if you want Access to work to help you, you need to get a grip on what events are used for which purpose. I can't tell the reason you want to set AllowAdditions to False. A more logical event would be the form's Current event or the form's Load event. Or even set the default to False and don't use code at all. It depends on the situation.

I guess you didn't believe me when I told you to not make forms and reports until your schema was solid I've only been doing this for 50 + years so what do I know. If you want to fix your schema, you now have a lot of work to do to fix your other objects. If you don't want to fix your schema, you will have to live with it forever and constantly write code to get around the mistakes in it.

An application with a shaky foundation (schema) is weak from the start. Everything will be harder to accomplish than necessary. Making changes to accommodate a growing business will be way too time consuming.

I've included a couple of links to useful databases I built. One will show you why you should not use certain reserved words as column names and the other will give you a way to manage simple lookup value lists without having to hard-code them in the table definition or create separate tables for each one.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[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.

 

[Pat Hartman]

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.

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?

 

[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!

 

[Pat Hartman]

Scaling back doesn't mean implementing poorly, it means implementing less but making sure you can build on it. I tell you this from years of doing myself and supervising "doers". If your foundation is sound, you can add on to it. This is extremely difficult for novices which is why the experts all harp on it so much. You may think it's ok to have three child names in your employee table because none of your present employees has more than two children but you would be wrong and it is that kind of wrong thinking that needs to be fixed early. First you're going to realize that in addition to first name, you're going to need DOB. And then maybe you need DOD or you could just clear the name and shift the second child to the first set of fields. Now your repeating group is up to three fields for each child and then whoops, you find a new couple and the spouse's child has a different last name. What are you going to do now? That is going to break your reports as well as your table and data entry form.

I have enough experience and so do many of the other experts to anticipate future changes. It's like defensive driving. Defensive programming will always save your bacon.

I don't even take shortcuts when I build applications for my own use. Why? Been there, done that. Always get burned. If I begin to depend on an application, I always find something I want to add to it that I never anticipated. Like my contact database. It started out as friends and family. When I decided to add children, did I add a bunch of fields in a repeating group to the contact table. Not on your life. I knew through experience what a mess this makes. I did it right and added a junction table and made a self referencing relationship so that I could have all the fields for the children as I had for their parents. That meant as they got older, I didn't need to do anything except set my send ChristmansCard flag to true to add them to my card list No change to queries to add the "children" table. The "children" were people and using the junction table, they were related to their parents but they also stood alone

 

[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.