Pull data for multiple information

Question

Hi, I would like some help. I am using Access 2007 and as I have just started to create some databases for home use.  The above is a screenshot of a Form I have created, in the background (as tabs) I show the 2 Tables I need help with.

I am trying to have it so I can run a query on one or more ingredients to return all recipes with this information.

At the moment I have 1 column of ingredients in the "Ingredient Listing" table and the same for the units of measure in the "Unit of Measure Listing" table. I think this should stay this way.

I have some basic queries set up, to pull information from my "Recipes" table which holds all the information. I have 15 separate columns to hold the unit of measure information and another 15 to hold the ingredient information. I am willing to have 15 identical/duplicate columns in the tables holding this information (instead of one), but there must be a better way.

Also I have tried to use information I have found to solve this problem, but I am unable to get what I think is the best and easiest solution to work. I found this "set Fieldb = fielda" and have tried to put it in an Expression Builder (with my database details) in the tables to duplicate information from 1 column to 15, no success.

If there is a way to query an ingredient without inputting its name under each (15) column, in separate rows I would be very interested. With the number of ingredients there are in a recipe, there is not enough rows in a query for me to do this.

I have attached this particular screenshot so you can see that some recipes show an ingredient more than once (generally with different units of measure). Also not all ingredients such as salt and pepper have a unit of measure to be recorded.

I feel like it is pointless to continue entering hundreds or thousands of recipes into this database until I can search for want I want properly. Any help with this would be greatly appreciated.

Answer 1

Ken, you seem to have a well of information which is great. Although as I have 1 ingredient used more than once in the same recipe I kind of ruled out the multi-list box option. I noticed in the relationship model you had in your reply, there were 2 PK's in 1 table. How do you do that? Is that so you save creating another table to join them up, and instead have 2 one-to-many joins in the same table. The GetToken and InParam functions are way over my head, so I will hold off on this if I can get it functioning without them. Although I will keep it in kind, thanks.

Please see below for more comments, for everyone.

Answer 2

First, the foundation of any well run database is a proper table structure. So before you start designing forms, reports queries, you need the table structure done right.

As the others noted, from your screen shot it looks like your tables have field for 15 ingredients and that is not correct. The structure Scott Mcd or what is in Gina's demo is what you need to achieve. 

You said; "I do have Units of Measure which are text (Bunch, For Dusting, To Garnish and To Serve)". you should have a lookup table like:

tblUOM

UOMID (PK autonumber)

UOM

You then store the value of the autonumber as a foreign key in your RecipeIngredients table (see Scott Mcd's example). 

You can create a compound PK by selecting both fields then hitting the PK icon on the ribbon. I'm not a fan of compound keys, but when used in a junction table as in Ken's illustration they are OK. But if you are going to link the record to other tables (not the case here) then I would prefer to use a surrogate key (like an autonumber) and use a unique index on the combination of the fields.

Finally, you said you might have an ingredient listed multiple times. Why? I can understand that you might add an ingredient at different times during the preparation of a recipe, but when preparing an ingredient list it should only appear once.

Answer 3

You have jumped form Table to Forms way to fast.  As we have all pointed out you MUST get the Tables correct first.  Think of it like building a house... you plan to hang drapes before the foundation is poured?  So, can we please have a screen shot of your tables and how they are related to each as you have them now.

Trust us... you get these Table right and everything else because easier.

Answer 4

Thanks to all for replying to my request for help.

Looking at the impressive road map of a recipe database that Gina attached, just wondering is it best to link all table ID field together. I assume this would mean you need to duplicate the ID field from your first table into your second and join as one-to-many. Does this method eliminate the need for lookup fields?

My logic may not be the same as access', but I assume I need to state somewhere which ingredients are in these recipes. I can't figure out how this works, although obviously it is important to link the information. I don't see how you guys have it all coming together for a list of ingredients for 1 recipe, I assume it all comes together after a form entry.

As I was stuck with my original thoughts of how to carry this out, I have gathered some knowledge off my access superiors (you guys) and came up with this.

With my new form looking like this (thought the Design view would help more).

I thought I might have to do a subform, but as a didn't realy have any knowledge on that I wanted to try bypass this option.  As for Gina suggesting that I do this, I figure it had to be done. I decided to insert a new query as a subform for my main recipe entry form, just so I can modify the Field name for display in the form. Although it appears I can't edit it the fields in my form.

I found while researching for a fix "Me.Dirty = False" (to be placed in a code builder I believe), my thoughts are that Access 2007 doesn't like the"Me."(not that I know much about this language). Even so I can't think of which property sheet field to put this in. If you think it may be something else I look forward to your assistance. I have inserted the 2 separate subforms from their tables as I couldn't get 1 with combined info (from a form/query) to work. (There might be a 'subform holder' which is locked and not allowing entry, but I can't find it.)

Is there a way to get the subform to expand automatically without scrolling when more data is put in, and also can it jump to a new column next to these once it gets to the end of the page (so you don't have to scroll). I thought maybe there is a way to continue the same list of information after 10 entered lines, next to it.

Thank you all very much for your assistance so far, you are all great.

Answer 5

I use the asterisk to highlight data since highlight here. :)

You can **** or take away, my intention is give a better understanding of what they should resemble.  I use *Active* to determine if that item in the Combo Box is still in use.  If not, then once the check mark is removed in the Combo Box it will drop to the bottom and display an *x-* before the name.  I do not delete the entry because in larger files you could leave orphans but going forward you'll know not to use that selection.

Not sure which *Related Recipe* you're talking about, what is the actual table name?