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

As Gina and I have said, you have to pour the foundation before you can start building on it. You are not yet ready to even be thinking about forms and reports. 

You said; "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?" 

You also said; "My logic may not be the same as access',"

It's not necessarily Access' logic, but the rules and principles of designing a relational database and you NEED to learn and understand that before you go beyond the table design. 

Every table should have a primary key. And every child table needs a foreign key to link it to the parent. A child can have multiple parents and a parent can have multiple children.

No this doesn't eliminate the need for lookup tables, it EMPATHIZES the need for lookup tables. A lookup table is generally a table of values used to standardize and speed input. for example a Units of measure lookup would simple be two fields. A code field (usually an autonumber) with a description field. the code field is then stored as a foreign key.

You said; " I need to state somewhere which ingredients are in these recipes. I can't figure out how this works,"

it works by having a list of recipes, a list of ingredients and a junction table that lists the recipeID and the IngredientID. So there would be a record for each combination of recipe and ingredient. 

The Me. keyword (which is just a shortcut for the Active object (i.e. forms!formname) has worked in every version of Access. Me.Dirty = False goes in a code snippet and is used to commit the current data being entered.

Answer 2


I'm afraid that model is badly wrong.  In relational database terms what you have is a ternary (3-way) relationship type between the entity types recipes, ingredients and measurements, each of which should be modelled by a table.  The relationship type is modelled by a further table, let's call it RecipeIngredients, which resolves the many-to-many relationship type intro three one-to-many relationship types.  The RecipeIngredients therefore has three foreign key columns, each of which references the primary key of the recipes, ingredients or measurements table respectively.  It is this table which stores the list of ingredients for each recipe.  In addition to the three foreign key columns there will be other columns which represent the attributes of the relationship type, e.g. the quantity of the ingredient in whatever unit of measurement has been selected.

The question of what is the key of the RecipeIngredients table depends on what you mean when you refer to the same ingredient appearing more than once in the recipe.  If it means that it can be entered more than once with a different unit of measurement each time, then the candidate key is a composite one of RecipeID, IngredientID and MeasurementID.  If an ingredient can be entered more than once, but might have the same unit of measurement in more than one case, but with a different quantity each time, then the candidate key is a composite one of RecipeID, IngredientID, MeasurementID and Quantity.  Even if you add an autonumber column to the table as a surrogate key, the multiple columns are still a key, and must be defined as such by their inclusion in a single unique index.

For data entry you simply need a form in single form view based on the Recipes table and a subform in continuous forms view based on the RecipeIngredients table.  The subform will be linked to the parent form on RecipeID, and include combo boxes bound to the IngredientID and MeasurementID foreign key columns, and text boxes for entering values such as the quantity of the ingredient.

It's theoretically possible to expand a subform or split it over two separate subforms, and I did once do something similar for a hospital in New York, but it's not trivial, and frankly not worth the candle.  The place to show the complete list of ingredients is in a report, where the list will grow automatically to encompass all the ingredients.  In a form, where a subform is likely to contain a large number of rows I often position it to one side of the form so that bit can occupy the full height of the form.

Answer 3

I see you removed some critical tables from my model...  (not you Ken).  You need to put them back and read carefully what Ken is saying.

Answer 4

I am probably playing around with all of this too much, trying to figure things out. I apologise if I am annoying you guys with my issues.

Does the 'Required' field in the tables affect anything connected or just relate to that table view? I had assumed it only relates to the table it is in.

I think I have come up with a good working structure, thought I'd share it to keep you updated. (Also noticed later that Gina asked for it anyway.)

Gina (re: 1st reply) I have used combo boxes, I like them a lot. Understood, I assume you put some code in to do that (with my knowledge, I would do it manually). I guess you can do code for possible 'active' periods as well, for example seasonal ingredients or courses during the day.

You mention orphans, just curious why that would be classed as one. Wouldn't it be connected somewhere? (even if just table to table (as a listing) and not an entry)

When you go into 'Recipes.accdb', there is 'tblRelatedRecipes' which is not in the flowchart. (It holds three fields: 'rrID', 'rrRecipeID', 'rrRelatedRecipeID')

Scottgem (re: 1st reply), I think I realise where I was having trouble. As you stated I have 15 fields for Ingredients, although I just had 1 Ingredient List. I really just needed to delete all my 15 (x2) fields in my table and view the information through a query (or on the form, I haven't looked at reports yet which I assume they are maybe a prettier, printable query). Obviously my structure was a main issue also, which I think I have fixed now.

As for the Unit of Measure table, I have the fields you state. Through my attempts I switched the PK to all my fields with the actual information and had them connected to there counterparts (FK) in the Recipe table. I don't think that was an issue at this point as 1 recipe have 1 folder, category, etc. The problem was with the 2 fields which appeared multiple times in 1 recipe.

I tried Scottgems' compound key (2 x PK), thought that was cool. I noticed you can do it for quite a few in the same table, is there a limit and whats the point. Linking a couple tables like in Ken's demo I understand but beyond that is ridiculous right, unless maybe you use 1 table to link multiple couple tables.

When you state "prefer to use a surrogate key (like an autonumber) and use a unique index on the combination of the fields", I assume you mean like Gina showed. Meaning having 1 field like Recipe ID referred to in many tables but each has a unique name so you can distinguish from which table it relates or has come from. I have adopted this system as I find it a very easy and helpful way to keep track of your fields at a glance.

As for an ingredient appearing multiple times for one recipe, I want it that way so I can record it true to the original recipe. Gina has a table for 'parts' which I am now thinking is something I can look at which will give me probably 1 listing of a multiple ingredient in separate parts. Example, my recipe for Potato Salad has Salt&Pepper with its main ingredients as well as ingredients in its 'For The Vinaigrette' section. Maybe I can have it with the UOM & Ingredient of my entry form.

Gina (re: 2nd reply), I am not jumping to the form in anyway I am just trying to fix what I already have, although I understand why you say that. I thought it all okay until I had no idea how to search beyond the query rows for my multiple ingredients, which was why I did my first post.

Scottgem (re: 2nd reply), what I meant by the combination of recipe and ingredient was that my logic said that I need to see all my information on a recipe in one table on one record line (this is why I ended up with 15 fields of ingredients), with lookup fields to the related tables. But now if I understand Access' structure principles a bit better, you connect all tables with information relative to that specific table listing only and get a record of all the information at once through the form/query and probably report options.

re: 'Me.', I stated what I did because I tried something out previously with all of my research (can't remember what it was), it did not work with the 'Me.' but it did work without it.

You state "in a code snippet", I guess you are referring to a 'Code Builder'. So I guess this 'Me.Dirty=False' wont help my situation as it wont allow me to type information to be committed to a record? (I hope I have fixed this issue, but I feel that it may happen again.)

Ken, from reading your reply it made me wonder what you thought of Ginas relationship flowchart?

Ken, I changed my Index fields for my subforms and I think I fixed that problem (and am currently happy with it, just so you all know I have inserted combo boxes for my subform fields).

I believe your reply helped me solve a few issues, so thanks for that.

Gina (re: 3rd reply), you state 'I see you removed some critical tables from my model'. I don't know what you are talking about. Do you mean I didn't show a table connecting the recipe with the ingredient and measurement tables in my database, I was still working it all out. If so can I get some constructive comments of what may hopefully be my final relationship structure attempt (at this point), hopefully the one I have attached above works and everyone is happy with what I have done so far.

I am having frustrating issues with access continually changing my design format of my subform, why does it keep changing back to a datasheet view while it remains a 'Default view' of 'Continuous Forms'. I have got it ok for now but I'm concerned it will change again when it feels like it. It has generally been happening as I have been jumping from tables to forms, and modifying/testing from form/layout/design views of my form&subform.

I thought my structure was originally incorrect in some way, although I didn't think it would look like this at the end. Also thought I would need to look at subforms, this is all done now and I look forward to all your feedback regarding my efforts. (I will cross my fingers they are positive.)

Answer 5

Ken, from reading your reply it made me wonder what you thought of Ginas relationship flowchart?

I haven't seen Gina's model, but knowing her, I'm sure it's right on the button.  As regards that in your last post the first thing which strikes me is that none of the relationships are enforced.  An unenforced relationship type is as much use as a chocolate poker.  You must enforce referential integrity in every case.

I'd like to take you up on your comment 'Linking a couple tables like in Ken's demo I understand but beyond that is ridiculous right'

Firstly the tables are not linked, that means something completely different; they are related, as you'd expect in a relational database.  The main point I want to make, though, is that a many-to-many relationship type can be between any number of tables.  It is entirely dictated by the reality which the database is modelling.  A table modelling such a relationship type might perfectly well be referencing six or seven tables if that relationship type exists in the real world.  It should always be remembered that a relational database is a model of the real world in terms of its entity types, their attributes and the relationship types between the entity types.  It should also be remembered that a relationship type is really just a special kind of entity type, so there can, and frequently are, relationship types between relationship types.  If the reality being modelled is complex, then the database's logical model will be equally complex.

On the other hand you should guard against unnecessary over-complexity of the model.  Always take note of something Einstein said, which should be pinned in big letters above the desk of every relational database developer, at least metaphorically:

'It can scarcely be denied that the supreme goal of all theory is to make the irreducible basic elements as simple and as few as possible without having to surrender the adequate representation of a single datum of experience.'

Albert Einstein "On the Method of Theoretical Physics" The Herbert Spencer Lecture, delivered at Oxford (10 June 1933)

You'll sometimes see this very loosely misquoted as 'As simple as possible, but no more so.' Or you could think of it as the KISS principle.