Help! How Can I Have These Two Types of Herbal Rec - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Help! How Can I Have These Two Types of Herbal Rec

Sarah Bliss

4 months ago

I have Plant Extracts (hydrosols,tinctures..) and Herbal Products (creams, syrups, aftershaves..) which I make myself.
The herbal products (ProductT) have recipes (RecipeT) and details (RecipeDetailT) [aka CustomerT, OrderT, OrderDetailT]. The RecipeDetailT gets its ingredients from the IngredientT.
The ingredients are all things I actually have or have had. All this works. The recipes work.
I use my own recipes or ones I find in books but I often make my own variations. If a variation works well and I want to repeat it, I need to know exactly what I did, not just the base recipe. But it will not be distinguishable from the older one. (For example cough syrup with added elderflower syrup or not.) So up to now I wrote out (by hand) the recipe base and then added the actual quantities I used etc. so I have a written copy of both the original recipe and of my variation. How can these two recipe types be available to eachother but somehow separate too in my project? Some kind of recipe list I can refer to or quote, that stays the same (a base recipe) and yet gets used and adapted to my needs.
My brain is melting!

Thomas Gonder @Reply

4 months ago

I'm not sure I understand 100%, but here is a suggestion, not knowing if you are using a db or not.
Each product and its related recipe are DIFFERENT. So, they both need their own record. For my db, I relate people through a "parent" field in the table. For you, this would mean the child (your variation) has a parent ID in its record. Does that make sense?

My suggestion would be to copy the entire base recipe into the variation, so that if you change the base recipe significantly (in the parent), you aren't destroying the variation.

Sarah Bliss OP @Reply

4 months ago

Thank you for such a quick answer, Thomas. I'm not sure I understand your reply either🤣I do have a database (I call it "Absolutebliss"!!!)
The product table is the name of my product (e.g.lavender body lotion). Then each product can have plenty of recipes which are further identified by the date (it's like wine) as well as the name. So I have a list of recipes which are already children to the product. What I'd like to do is have a kind of parallel set of recipes (still children to the same product if possible) to use as a kind of "copy/paste" source of base recipes that I can then modify.
PS i'm still learning the copy/paste form-plus-subform.

	Thomas Gonder @Reply 4 months ago
I'm sorry Sarah. With sentences like this: "The product table is the name of my product (e.g.lavender body lotion)", I'm totally lost. Maybe you should show a query that simply explains your tables?

Donald Blackwell @Reply

4 months ago

Sarah I think Thomas was on the right trac.

On your recipe form, I would add a "Create Variant" button that when clicked would duplicate the current recipe thus creating your variation recipe. It would also add the ID of the original recipe along with the ID of the new variant to a junction table so that you could show either a link to the variation or a duplicate instance of the recipe form with the variation. And, if you wanted to add a little more to it, you could have it recurse through the junction table so that it would add an entry for the new variant to every other original or variant in the table.

Does that make sense? And, more importantly, does it sound like what you're trying/wanting to do?

Sarah Bliss OP @Reply

4 months ago

Thomas and Donald,
Sorry, I'm 75 years old and I only started learning Access a few years ago after I retired (and LOVING it). My head doesn't work well in the evening so yesterday I left it.
It's morning now (I live in Italy). I'm not sure we are understanding eachother but I think we are - just using terms I'm not familiar with.
Donald, Yes, it sounds like that is what I want, with a 'cherry on top'! Could you maybe tell me what lessons or TechHelp to go to so I can learn to do this, and how to apply it to my database? (I got stuck at Developer 23 but I have the others.) Maybe give me an idea about how to set up that junction tabel? I don't have the right mental setup for approaching this and I get completely bogged down in trial and error messiness. SO frustrating!
My tables are like this:
ProductT > RecipeT > RecipeDetailT < IngredientT (it works like Richard's Invoicing TechHelp Member video.)

	Sarah Bliss OP @Reply 4 months ago

	Sarah Bliss OP @Reply 4 months ago

Donald Blackwell @Reply

4 months ago

To break it down, you'll be using Many-To-Many Relationships, each recipe could be a variant of one or more other recipes which in turn could be a variant themself. Here are some videos that show junction tables:

Many-to-Many
Students & Parents (that's the first in the series of 8)
Students & Grades (that's the first in the series of 3)

Then, you need to be able to copy records, some videos:
Fitness 47
Fitness 60
Access Developer 24 (shows copying a record - recipe, and it's children - ingredients)
Access Developer 48 (shows how to create a function and links to the code vault for duplicating records)

And then, if you wanted to add the extra linking to all other variants, that would just use a recordset loop in the junction table to look for all other variants:

For Next Loop
Nested Loops
VBA Loops
Recordsets

These should give you some ideas. Objectively, I would adjust your database as follows:
1. Create Junction table (i.e. RecipeXVariantT)
2. Work on copying recipe and add original/variant to junction table
3. Copy recipedetailT
4. Adjust new recipe with variations
5. Loop through junction table to find all linked recipes and create a junction record

It seems like a lot, but if you take it one step at a time, I think you'll do fine :)

	Sarah Bliss OP @Reply 4 months ago
Wow, Donald, thank you so much!! That's a SUPER challenge for me. I'll let you know how it goes. Thanks!🤩

	Donald Blackwell @Reply 4 months ago
I have faith in you :) But if you ever get hung up, you know help is just a post away.

	John Davy @Reply 4 months ago
Hi Sarah, Follow what Donald and Thomas said. I did some clip screens on one approach which was recommended. If you follow the screens, I think you can create the one to many that you need. Since I am not a cook, I hope my screen clips are better than my receipe.HTH John

	John Davy @Reply 4 months ago

	John Davy @Reply 4 months ago

	John Davy @Reply 4 months ago

	John Davy @Reply 4 months ago

	John Davy @Reply 4 months ago

	John Davy @Reply 4 months ago

Sarah Bliss OP @Reply

4 months ago

Thank you too, John. I'm studying all the videos Donald suggested and really enjoying them. I get carried away - I wish I had the time to do all the Fitness ones!
Your screenshots have confirmed how I was thinking. I need to know more before I plan more so here I am, studying the videos.
Thanks. I really appreciate all your help. My background is literary and I have no-one to talk this stuff out with, so thanks!!

	John Davy @Reply 4 months ago
Hi Sarah, My screenshots are simply the basics. You can add fields to each table as needed and then to the respective forms. If you need help, just let me know. John

	Sarah Bliss OP @Reply 4 months ago
Hi Donald, I decided to do a many to many self join. I used the lesson from Richard's seminar on relationships to start. This is what I have so far. Now I need to learn how to copy the whole recipe and how to add the "opposite" or "other" side of the relationship. I'll get there.

	Sarah Bliss OP @Reply 4 months ago

	Sarah Bliss OP @Reply 4 months ago

	Donald Blackwell @Reply 4 months ago
Looking good

Sarah Bliss OP @Reply

3 months ago

Donald, Thomas and John - I have sorted the problem of referencing my recipes among themselves quite satisfactorily. I just can't get the copying to work, but it's really an "extra" and I can copy manually (I am the only person who uses this database). For your interest I reviewed the table setup for errors in joins etc (my weak point) and I tried copying in all the 3 ways demonstrated by Richard in Developer 24. The straight copy/paste only works with the recipe form, not the details. The "Messy" version only (sometimes) works with the Recipe form, absolutley not with the details. For the recordset version - I just can't get it to even start. I need to learn recordsets before taking this on. I'm having trouble getting my head around it.
So Let's consider this question closed. And thank you for all the help. I'll be coming back to the links you suggested, Donald, I'd like to go into some of them more.

	Sarah Bliss OP @Reply 3 months ago

	Sarah Bliss OP @Reply 3 months ago

	Sarah Bliss OP @Reply 3 months ago

Sarah Bliss OP @Reply

3 months ago

As you can see, the references are fine: sources above and repeat/variant below. And dbl clicking gets you to the original one.
The trouble is in the details form in the Add button which I use to add my ingredients. But this is necessary as I need to reduce the list to choose from.
For now I'm moving on to other bits that need fixing before the lavender flowers and I have to start the practical part of this project.
THANK YOU!!

	Donald Blackwell @Reply 3 months ago
Fantastic job!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View