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

I was looking over my table design and noticed in my main Recipe table that I had 3 fields which have a join/joins to their detailed lists in other folders. and was wondering if I need the fields here at all as I think I can get rid of them; rFolder, rCategoryName, rCardNumber . Please confirm so I know I am understanding this more and more, I won't delete them until I hear back from you.

Answer 2

The enforcement of cascade deletions is a matter of judgement depending on the context.  Let's take a common commercial model as an example:

Customers----<Orders----<OrderDetails>----Products

You would probably not want to enforce cascade deletions in the relationship type between Customers and Orders as this would remove all orders relating to that customer from the database.  You might however want to delete a customer for whom there are no related orders.  In this case you would not enforce cascade deletions so that, any attempt to delete a customer with related orders would fail, and only the deletion of customers with nom related orders would be allowed.

On the other hand you might want to delete an order, e.g. if it's cancelled, in which case you would wish to automatically delete all OrderDetails rows which reference that order, so you would enforce cascade deletion sin the relationship type between Orders and OrderDetails.

You would not enforce cascade deletions in the relationship type between Products and OrderDetails of course, as you would not wish to delete any referencing rows from the latter if a product is deleted.  On the contrary, you would want to prevent the deletion of a product if there are any rows in OrderDetails which reference it, which the enforcement of referential integrity would do.

As regards the rFolder, rCategoryName and rCardNumber columns in tblRecipes, the first would appear to be redundant as the key of tblFolders is being referenced via the relationship type with tblRecipeSources, so a recipe can relate to multiple folders.  As regards the other two, there is a ternary many-to-many relationship type between tblCardNumbers, tablCategories and tblFolders, modelled by tblCategoryCardNumbers, so a recipe can relate to multiple categories and card numbers.  If this accurately reflects the reality being modelled then these two columns can also be deleted from tblRecipes.

I'm not sure what you mean by 'locking of form formats', but at this stage it is premature to consider the interface design until the logical model has been firmly established.

Answer 3

As for the Parts which I will have for Related Recipes such as an ingredient of the Pie Recipe is the Crust which is recorded as its own Recipe. I am not sure I have wrapped my head around how to do this correctly. Would you manually enter the RecordID reference for the Crust recipe into the record for the Pie recipe. Can I link it in a way that I can enter a crust description/name as an ingredient for the pie recipe, and it pull all of its recipe details up when needed?

I was also wondering in a split form view can you view results from a query instead of the original table (in datasheet view) the form was based upon?

First, if you are going to include ingredients in a recipe that is, in itself, a recipe you are now dealing with a multi-level Bill of Materials (BOM). I suggest you do some research on BOMs as it gets into a complex situation.

Second, a split form shows a datasheet of whatever the Recordsource is. Be it query or table.

Answer 4

Following on from Scott's comment, modelling a bill of materials is per se very simple.  You merely need a further table which acts as an 'adjacency list', by having two foreign key columns, each of which references the primary key of the recipes table.  So for your pie recipe, one column in a row in the new table would reference the pie recipe, the other would reference the pie crust recipe.

The difficulty is in processing the data to generate the bill of materials, as this requires recursive querying to do so to an arbitrary number of levels.  The flavour of SQL used by Access does not support recursive querying, however.  You could use a client server SQL product which does support recursive querying as the back end, or you can simulate it in Access.  You'll find an example of the latter as BoM.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file recursive querying is simulated by generating tables in a temporary external database file.  The process is not trivial, though, and does require a familiarity with VBA and SQL.  Doing so to a fixed number of levels is simpler as this can be done by joining multiple instances of the table which models the adjacency list.  My demo includes a PartsTree file which does this.  Even this is not trivial, however, and you might find it simpler not to try to generate a bill of materials as such recursively, but to simply use the new table to reference all associated recipes.  So if recipe A includes recipe B  and recipe B includes recipe C, rather than having two rows in the new table like this as you normally would when modelling a BoM:

A    B

B    C

you would have

A    B

A    C

B    C

This would be very easy to process in the interface.

Answer 5

Ken, you say the 'delete related records' cascade is optional, and why. Wouldn't it be safer to run a delete query when you need to instead?

Do I assume that you feel the 'update related fields' cascade is not optional, and is a must?

As for the 'locking of form formats', I had issues with my subform before and I was losing the look that I wanted. I am referring to 'Border Style', 'Record Selectors', 'Navigation Buttons', 'Scroll Bars' and even the 'Fill/Back Color'. I know you guys don't want me to look at forms yet, but I don't want to forget what I want help with either. I hope you understand. Although I will refrain from asking anymore form questions for now.

Scottgem, thanks for telling me what to research for information and solutions. Regarding the split form view. That was what I thought the answer would be, thanks for confirming that.

Ken you state 'two foreign key columns, each of which references the primary key of the recipes table', but it won't let me enforce referential integrity.

So if I have my 'tblRecipeParts' with 3 fields 'rpRecipePartID', 'rpMainRecipeReferenceID', 'rpIncludedRecipeReferenceID', and have joined the 2 ReferenceID fields to the 'rRecipeID' field in 'tblRecipes' is this our simple BOM.

I also have 'tblParts' with 2 fields; 'ptPartsID' and 'ptPart' which has a one-to-many join from 'ptPartsID' to 'rpIncludedRecipeReferenceID' in 'tblRecipeParts'. I think this is right, but I am also thinking that if I somehow reference the Part in the form for the Recipe then this table is irrelevant.??

I have changed the Section table connection so as to group it with the ingredient information (yet another table coming off 'tblRecipeIngredients', if you think this is not right please let me know.