Hi Ben, I always ask myself what information do I want from my application. What do you need to know from the data you input? Are you managing/monitoring a process? What do you need to know in order to achieve your result. As you start this process you will quickly start to identify information that you need to enter into your tables. If you had an ideal report what would it look like? Only ideas to get started  John

Good question. This is exactly the scenario I cover in Access Developer 25. In that course, I show how to take products and relate them to other products using a self-referencing many-to-many relationship (a junction table), which is the correct way to model bundles, kits, assemblies, and manufactured items. That design lets one product be composed of multiple other products, whether they're purchased or manufactured. You're on Developer 4 now. Keep going. There's a lot of stuff between 4 and 25 you're going to need as well.

Thanks John. Richard hit the nail on the head with what I am looking for. Unfortunately, I believe I know what I want in concept just not how to make it all bundle together. Richard....this is the first time I'm upset at the 'get to it later' lol. I don't want to wait and I don't have resources to jump that far very fast going in order (which to date is what I have done and will continue to do). Oh well...will keep playing so that hopefully I can learn something then kick myself when you show the easier way lol.

If you want something that's a little simpler, in my Fitness database series, I teach how to do something like this. It's not exactly proper bundles, but the analogy that I use here is we take food items (like cereal, coffee, that kind of stuff) and you can bundle those together into meals, like "breakfast 1", which are stored in a separate meals table. Then, when you go to add items to your food log, you can pick either individual food items or bundled meals. It's the same kind of concept. These techniques work for different things, so start watching the fitness series of videos, and that's covered in there.

Interestingly enough, that sounds like it will definitely get me through until I get through the developer stuff. Thank you so much!

Richard, I am currently around the fitness 45 video and understand what you are doing and believe this will work. As you mentioned it may not be exactly proper bundles which sort of brings me back to my original question. You have tons of videos with examples and I understand how they can be used in other applications. For lack of a better phrase, I think my original question should be rephrased to "Is there a 'right' way of doing X? What ever X is." You made a comment in one of your videos "there is database theory and then there is 30+ years of experience in doing to know what is right" (rough quote). When there are multiple ways of doing something how would someone know which is the best or right path as opposed to one that works 'just fine'? Sorry for the long post just thought I would try to expand the question to be a bit more clear since originally I had a specific issue I was trying to solve but thought it relevant to ask the same thing in a more generalized way.

Ben Perhaps instead of trying to find the "right way" you might ask about best practice or the pros and cons of doing it one way as opposed to another way.  I had several questions that I was having difficulty getting answered by asking on forums and just searching on the internet.
Finally I broke down and asked the Google AI and it was able to share some pros and cons of the two options I asked about.  Since it isn't really about what is Right but more about what will suite your situation and workflow best.

Aleece, I appreciate your input. I have done that before and gotten various answers depending on where you go (Chat GPT vs Google AI). I have rebuilt a database a few times in different ways because of it and have found pros and cons on my own as well. While google and chat are great tools they are sometimes the worst as well. If the overall agreed upon practice is 'whatever works is the right path' then so be it, I was just looking to see if there is a more rigid approach generally taken as just about everything I have learned has been from here and while it has been extremely helpful it seems to me without some of your own creative ability there isn't always a clear path. Through a bunch of his tech help videos he calls these concepts the lego blocks and 'to go assemble', unfortunately, I am the one who builds the sets by the book not someone who can just grab some blocks and go.

Ben I Agree.  I try to resist the AI because I have occasionally tried to use it to help me develop something and it really required me figuring out how to break it down into the smallest steps so that I can test and trouble shoot each step before moving on.  Problem being that I might not know the correct order to go about things to be most efficient on something I don't have very much experience in.  But asking the AI for some more generalized discussion on the pros and cons lately I was able to find some answers that I just couldn't seem to locate with a more generalized search.

Ben this is a really good question, and honestly, it's one that never fully goes away. The short answer is there isn't a single "right" way to do X. If there were, database design would be a lot easier and a lot less interesting.

One of the real strengths of Microsoft Access is that there are usually three or four perfectly valid ways to solve the same problem. Loops, queries, SQL statements, domain functions, temporary tables. They all exist for a reason. What I usually do, and what I teach, is to get something working first in the most straightforward way my brain understands at the moment. For me, that often means recordset loops, because I grew up programming in BASIC and my brain naturally thinks in terms of stepping through things.

Once it works, I step back and ask, "Can this be done faster, cleaner, or more efficiently?" Sometimes the answer is yes, and it turns into a single SQL statement that runs ten times faster. Sometimes the answer is no, because the logic is too complex or too conditional for SQL alone. Both approaches are correct. One is just better for that particular situation.

Experience also changes how you see problems. What feels like the right solution at five years of experience isn't the same as what feels right at ten years, and it's very different at twenty or thirty. That doesn't mean the earlier solution was wrong. You literally can't reach the higher level without going through those intermediate stages first. I can look back now and understand why I built things a certain way years ago, but I also know I couldn't have jumped straight to today's way of thinking back then.

So instead of asking "What is the one right way?", I think a better question is "What are the tradeoffs?" Does this design scale? Will it still make sense six months from now? Is performance actually a problem yet, or just theoretical? If it works, is understandable, and meets the requirements, then it's a good design for where you are right now. Refinement and optimization come later. I'm still learning new database techniques after 30+ years, and that's honestly one of the reasons I enjoy this so much. It really is a continuous learning process, not a destination.

And for the record, I'm still tearing pieces of my database apart and rebuilding them. So, it's a never-ending process. I still got code in my database from the original build back in 2002. And today I'm like, you know what, we're going to rip this form out and redo it. So that's just something you're going to always do, no matter how good you get.

What Richard said.  Also, coming from a place of much less experience than him, I would say that the "right" way to do "X" is the best way that you actually understand, today.   This allows you to move forward with your work.  For example, for Christmas this year my wife made her list on paper with a pencil and my daughter created a shared google document with hyperlinks.  In my mind, they both did it the "right" way because they used the best tools that they actually understood and they both got it done.

There are rules of normalization that can be followed.  Richard's lessons follow these, but not militantly.  For most of his students, the benefit of absolute adherence to these rules would not be worth the tedium and complexity it would add to their projects.  

I am also rebuilding parts of my database that I did "right" based on what I knew then, but I now know more and have better tools.  Also, don't forget to comment your code.  You will be glad you did.