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

First re my stmt; "prefer to use a surrogate key (like an autonumber) and use a unique index on the combination of the fields". If we use terms you are unfamiliar with, look them up and if you still don't understand, ask for clarification.

A Surrogate key is an arbitrary value assigned as a unique identifier. There is a whole debate that has raged for years about whether to use natural or surrogate keys, so there is plenty to find on that subject. A natural key is a value or values in the record that make it unique. For example a person's name is not unique, so a natural key would be the name fields combined with some other value(s) that make it unique. For example birth date. But even then you may need to add other fields to ensure uniqueness. And you do NEED to be able to establish uniqueness in a record. When you use multiple fields as a compound PK, it becomes cumbersome to relate compound keys to other tables. This is where a surrogate key comes in. But then you still need ensure uniqueness. That's where a unique index on a combination of keys comes into play. 

I generally name my foreign keys the same as the corresponding PK. So if I have RecipeID as a PK, then all my foreign keys are named RecipeID. If it helps you to use a prefix that shows where the FK came from, then tat's fine for you. I have never found it necessary. 

The Dirty property of a form indicates whether there are pending changes to the record. If Dirty = True, then there are rending edits. If its False then the record displayed in the form is the same as in the table. So setting Dirty=False, commits the changes to the table.

Answer 2

Your statement...

Gina you state 'I'm sorry but in order to fix what you have you need to redo the tables.  A band aid will not work in this case.' but above that you had stated 'Your Data Model...Looks good with a couple of suggestions' (which I was real happy to hear & I have changed). What other issues should I be aware of.

Yep, that was me getting confused between what you posted earlier and the updated model you posted now.  (Thanks for getting rid of those special characters!

Hmm, another question... why tblFolders?  What purpose does that serve?  Just curious and it would seem Categories would be good enough, not sure why you broke it down that far.

Answer 3

We are here to help, if we didn't want to then we would not be here (no pay for this just volunteers).  So you ask as many questions as you like... that said, let's try and break it up a bit.  Trying to answer many parts by many people gets confusing.  Okay, here we go with my replies...

You asked...

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

Tricky question as the answer could be both or just the one it's in depends on the Table and which Field is tagged as Required.  The question would need to be more specific to be answered properly.

Your Data Model...

Looks good with a couple of suggestions:

• Get rid of all the *special characters* like the forward slash, parenthesis, etc. See... http://www.access-diva.com/d1.html

Combo Boxes...

No code just and If() Statement and then a sort so the ones unchecked will fall to the bottom.  Orphans would not show anywhere except in the Table if you cause them to drop off the list, i.e. Joe worked at the Company and was assigned many Tasks.  Joe's name appears in the Combo Box.  Joe leaves the Company.  Now, if we remove his name off the list those records are there but they are assigned to no one and if you have a Form that finds Tasks by Employee's and Joe's name is not on the list, well... those Tasks become orphans and can only be seen in the Table.  My way they will show up because Joe's name just at the bottom with a little x by his name.  Make better sense now?

tblRelatedRecipes - Rouge table (actually part of another database that should not be there)...  thanks I will fix that!

Re: my second reply...

I'm sorry but in order to fix what you have you need to redo the tables.  A band aid will not work in this case.

Re: my third reply...

I see now you only posed your old Data Model and, my mistake, I thought you had taken the one from the download and removed some tables.

Hmm, and again you are going between Table design and Form design when you need to stay focused on Table design.  When I'm building a database whether for the first time or I can just reuse one I have already built I spend much time reviewing the Tables.  I sometimes believe the Table design is 60% of the job.  If those are not right 150% of your time will be getting your Forms to do *anything* correctly.  So, please heed our advice, get those Tables *fixed* and then we can move on the Forms.

Answer 4

Ken, I put the referential integrity on my relationship joins. I can't currently figure out though why it wouldn't let me do it on the Servings and RecipeIngredients table joins to the Recipes table. From Ginas' suggestion of removing my "/" & "()", I have modified it a little and removed the Servings table altogether. Although that still leaves 1 join without referential integrity. I deleted the Ingredient and Measurement ID fields from the Recipe table also. I don't know why it is not working as (to me) it looks like it is set up the same as for RecipeFolders, which is working.

This message comes up, 'Microsoft Office Access can't create this relationship and enforce referential integrity'.

Thought I would ask you if you feel that these relationships MUST have their 'Cascade Update Related Fields' amd 'Cascade Delete Related Records' boxes ticked also, as I haven't felt like that was something I wanted to do.

I find when things get hard, the 'KISS principle' is one to live by in most cases.

Scottgem, I had done a little research on the terms regarding surrogate vs natural key and did find it to be kind of an endless debate.  I didn't research to long as I didn't want to get confused about things that I didn't fully understand.  I find your reply helpful enough to put my mind at ease with this.

So I think I understand 'keys' a little better as I have done a little research. I understand the compound key (2 or PK's in 1 table) is a way to make a unique key combining the information of more than 1 field at once.

I could be totally wrong here, but would you describe a record with field data or data combinations which there is no duplicate of in the table/database as a natural key.

I saw in one part of my researching that  a 'SurrogateKey' was displayed as a field name is that how you get a surrogate key?

Also if you think I need to have this key, can you please explain something I found 'Surrogate keys avoid choosing a natural key that might be incorrect.'

Where do these keys come into play anyway and do they all show keys next to there records/fields? Are these keys instead of a single PK in a table like I have now? Do I need to worry about any other key apart from what I already have (PK & FK)?.

The way you explain the 'Dirty property' is like you use it to updates the edit straight away. That was not the issue I had, so happy not to worry about that anymore.

Gina it does get confusing with all the info, but regarding the 'Required field' question.  I think I asked this as I thought it may have been causing issues with my subform. That should be alright now though.

Your Orphan reasoning and explanation was good thanks, I understood 'orphans' earlier in my research but as I have had issues and this was not one I kind of forgot the details. Thanks for the refresher, not sure I will need this with what I am doing (I do have another database which worked okay, and another project after this one to look at), I will try to keep your way in mind for later.

Gina you state 'I'm sorry but in order to fix what you have you need to redo the tables.  A band aid will not work in this case.' but above that you had stated 'Your Data Model...Looks good with a couple of suggestions' (which I was real happy to hear & I have changed). What other issues should I be aware of.

Gina you are obviously far more knowledgeable then me on access and I would never temper with anything from any of my expert advisers. Apology accepted.

I was pretty happy with my table setup, so I just thought I would fix the form a little. The only reason I was going 'between Table design and Form design' was because I was trying to fix my subform issue (of misbehaving). Is there a way to lock my formatting on my form, I am still paranoid that I will need to fix it again at some point. I am guessing one workaround is, only have one tab open at a time (form/table/etc.).

You will be happy to know I am not going to go further than that without all your approvals to do so.

I just decided to add a couple more tables this is the current setup


Answer 5

This message comes up, 'Microsoft Office Access can't create this relationship and enforce referential integrity'.

It would normally imply that there is a row in the referencing table (tblRecipeIngredients) which does not have a matching row in the referenced table (tblRecipes).  You can examine the table for this with the following query:

SELECT tblRecipeIngredients.*

FROM tblRecipeIngredients LEFT JOIN tblRecipes

ON tblRecipeIngredients.riRecipeID = tblRecipes.rRecipeID

WHERE tblRecipes.rRecipeID IS NULL;